SQL How do I transpose and group data into static columns?

Question

I have a table with the following data:

UID  LAST    FIRST  FUND  AMOUNT  STATUS
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

I would like to transpose the FUND, AMOUNT and STATUS values for each UID into a single row for each UID. The resulting table would have columns added for FUND_1, AMT_1, STATUS_1, FUND_2, AMT_2, STATUS_2, FUND_3, AMT_3, STATUS_3. Each UID may or may not have a total of 3 funds. If they do not, the remaining fund, amt, and status columns are left blank. The resulting table would appear as:

UID  LAST   FIRST  FUND_1  AMT_1  STATUS_1  FUND_2  AMT_2  STATUS_2  FUND_3  AMT_3  STATUS_3
1    Smith  John   C       100    1         B       250    1         E       150    1
2    Jones  Meg    B       275    1         F       150    1
3    Carter Bill   A       100    1

For clarification, this is how the data would move from the existing table to the resulting table for UID 1:

It seems I am unable to use PIVOT because FUND_1, FUND_2, FUND_3 will be different fund categories for each person. The question, TSQL Pivot without aggregate function helps but doesn't answer my question since I have multiple rows in what would be the the DBColumnName in that question.

related: https://stackoverflow.com/questions/1343145/tsql-pivot-without-aggregate-function?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa — SCFi, May 07 '18 at 16:02
https://meta.stackoverflow.com/questions/285551/why-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557 — Sean Lange, May 07 '18 at 16:04
How do you know the order of funds for a given person? And is the number of funds per person dynamic? — Sean Lange, May 07 '18 at 16:05
@SeanLange - The order of the funds does not matter. It should just take them in the order it receives them and add them to the next available FUND_#, AMT_# & STATUS_# columns. The number of funds per person is dynamic and will not be know beforehand, but nobody has more than 3. — David Baker, May 07 '18 at 16:06
Then this is pretty simple. You either need to use PIVOT or conditional aggregation (my preference). Can you post sample data so we can use it instead of as images? — Sean Lange, May 07 '18 at 16:08
You are going to lose the ability to easily do math on your results. You just turned the total value of Fund B into a more difficult calculation. — KeithL, May 07 '18 at 16:31
@keithl thanks for noting that. This particular set of data is being fed into a program to run stats on, and was requested in this format -- so I will not need to perform any further math on it. — David Baker, May 07 '18 at 17:00

score 2 · Accepted Answer · answered May 07 '18 at 16:27

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

SQL How do I transpose and group data into static columns?

1 Answers1

Linked