Re: a Non-Aggregate Cross-Tab?

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: A Traveler (hitchhikersguideto-news_at_yahoo.com)
Date: 09/17/04 

Date: Fri, 17 Sep 2004 11:11:25 -0400

Thanks much for your help. That worked perfectly.

CheerZ!

"Anith Sen" <anith@bizdatasolutions.com> wrote in message
news:O4wtZT1mEHA.3264@TK2MSFTNGP12.phx.gbl...
> You have to make sure you get a proper derived sequence value which can be
> used in the aggregate in the outer query. For instance, you can do:
>
> SELECT RowFld,
> MAX( CASE WHEN ColFld = 1 THEN DataFld END ),
> MAX( CASE WHEN ColFld = 2 THEN DataFld END ),
> MAX( CASE WHEN ColFld = 3 THEN DataFld END ),
> MAX( CASE WHEN ColFld = 4 THEN DataFld END )
> FROM ( SELECT RowFld, ColFld, DataFld,
> ( SELECT COUNT( * )
> FROM tbl t2
> WHERE t2.RowFld = t1.RowFld
> AND t2.ColFld = t1.ColFld
> AND t2.DataFld <= t1.DataFld )
> FROM tbl t1 ) D ( RowFld, ColFld, DataFld, seq )
> GROUP BY RowFld, seq
> ORDER BY RowFld;
>
> --
> Anith
>

Relevant Pages

  • Re: a Non-Aggregate Cross-Tab?
    ... One thing i did forget to mention is that for any given RowFld, one ColFld ... may have 1 DataFld while another ColFld has 2 such as follows. ... >> (Actually aggregate would work if only there were a CONCAT aggregate or i ...
    (microsoft.public.sqlserver.programming)
  • Re: a Non-Aggregate Cross-Tab?
    ... You have to make sure you get a proper derived sequence value which can be ... used in the aggregate in the outer query. ... FROM (SELECT RowFld, ColFld, DataFld, ...
    (microsoft.public.sqlserver.programming)