[Solved] SQL How do I transpose and group data into static columns? [duplicate]

by


This is a pretty common conditional aggregation. Notice how I posted consumable data as a table and insert statements. To be honest it took longer to do that portion than the actual code to select the data. You should do this in the future. Also you should avoid using keywords as column names. 

declare @Something table
(
    UID int
    , LAST varchar(10)
    , FIRST varchar(10)
    , FUND char(1)
    , AMOUNT int
    , STATUS int
)

insert @Something values
(1, 'Smith', 'John', 'C', 100, 1)
, (1, 'Smith', 'John', 'B', 250, 1)
, (1, 'Smith', 'John', 'E', 150, 1)
, (2, 'Jones', 'Meg', 'B', 275, 1)
, (2, 'Jones', 'Meg', 'F', 150, 1)
, (3, 'Carter', 'Bill', 'A', 100, 1)
;

with SortedValues as
(
    select *
        , RowNum = ROW_NUMBER() over(partition by UID order by (select null))
    from @Something
)

select UID
    , Last
    , First
    , Fund_1 = max(case when RowNum = 1 then Fund end)
    , Amt_1 = max(case when RowNum = 1 then Amount end)
    , Status_1 = max(case when RowNum = 1 then Status end)

    , Fund_2 = max(case when RowNum = 2 then Fund end)
    , Amt_2 = max(case when RowNum = 2 then Amount end)
    , Status_2 = max(case when RowNum = 2 then Status end)

    , Fund_3 = max(case when RowNum = 3 then Fund end)
    , Amt_3 = max(case when RowNum = 3 then Amount end)
    , Status_3 = max(case when RowNum = 3 then Status end)
from SortedValues
group by UID
    , Last
    , First
order by UID
    , Last
    , First

solved SQL How do I transpose and group data into static columns? [duplicate]