Re: a Non-Aggregate Cross-Tab?

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

Date: Wed, 15 Sep 2004 14:32:43 -0400

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. Thanks again.

 RowFld | ColFld | DataFld
 A | 1 | abc
 A | 2 | cde
 A | 2 | xyz
 A | 4 | fgh
 B | 2 | a12
 B | 3 | b34

 i would get a table looking like this:
     | 1 | 2 | 3 | 4 |
 A | abc | cde | | fgh |
 A | | xyz | | |
 B | | a12 | b34 | |

"Adam Machanic" <amachanic@hotmail._removetoemail_.com> wrote in message
news:%23GTg7F1mEHA.3868@TK2MSFTNGP11.phx.gbl...
> MAX() aggregate will work fine for this:
>
> SELECT RowFld,
> MAX(CASE ColFld WHEN 1 THEN DataFld ELSE NULL END) AS Col1
> MAX(CASE ColFld WHEN 2 THEN DataFld ELSE NULL END) AS Col2
> MAX(CASE ColFld WHEN 3 THEN DataFld ELSE NULL END) AS Col3
> MAX(CASE ColFld WHEN 4 THEN DataFld ELSE NULL END) AS Col4
> GROUP BY RowFld
>
>
> "A Traveler" <hitchhikersguideto-news@yahoo.com> wrote in message
> news:OfPT7B1mEHA.2948@TK2MSFTNGP11.phx.gbl...
>> Hello,
>>
>> I am wondering if maybe someone has written a sproc which can, given a
> table
>> with individual units of data, can return a non-aggregate crosstab table.
>> (Actually aggregate would work if only there were a CONCAT aggregate or i
>> could write one).
>>
>> By this, here is what i mean...
>> Given data:
>> RowFld | ColFld | DataFld
>> A | 1 | abc
>> A | 2 | cde
>> A | 4 | fgh
>> B | 2 | a12
>> B | 3 | b34
>>
>> i would get a table looking like this:
>> | 1 | 2 | 3 | 4 |
>> A | abc | cde | | fgh |
>> B | | a12 | b34 | |
>>
>> Thanks in advance.
>>
>>
>
>

Relevant Pages

  • Re: a Non-Aggregate Cross-Tab?
    ... MAXaggregate will work fine for this: ... MAX(CASE ColFld WHEN 1 THEN DataFld ELSE NULL END) AS Col1 ... GROUP BY RowFld ...
    (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)
  • 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)