SQL如何将数据转置并分组到静态列？ [重复]

This question already has an answer here:

这个问题在这里已有答案:

• TSQL Pivot without aggregate function 9 answers

没有聚合函数的TSQL Pivot 9个答案

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的FUND,AMOUNT和STATUS值转换为每个UID的单行。生成的表格将为FUND_1,AMT_1,STATUS_1,FUND_2,AMT_2,STATUS_2,FUND_3,AMT_3,STATUS_3添加列。每个UID可能有也可能没有总共3个基金。如果他们不这样做,剩余的资金,amt和状态列将留空。结果表将显示为:

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:

为了澄清,这是数据如何从现有表移动到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.

我似乎无法使用PIVOT,因为FUND_1,FUND_2,FUND_3将是每个人不同的基金类别。问题,没有聚合函数的TSQL Pivot有帮助但不回答我的问题,因为我在该问题中的DBColumnName中有多行。

1 个解决方案

#1

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