sql-server - 使用行号或排名在 SQL Server 中将值从行转换为列值而不使用数据透视和循环/游标

Question

我想转换以下数据，如输出所示

DPPY_PFX    DPDP_ID DPRL_DP_ID  ROW_NUMBER   
-------------------------------------------
1315        D8703   D8692       1   
0219        D6010   D6012       1  
0219        D6010   D6013       2  
0219        D6010   D7993       3  
0219        D6010   D7994       4   

输出

1315    D8703   D8692  
0219    D6010   D6012,D6013,D7993,D7994  

我编码如下，但没有取值，而是单独取值。

DPPY_PFX    DPDP_ID 1   2   3   4  
0219    D6010   1   1   1   1  
1315    D8703   1   0   0   0  

询问

SELECT * 
FROM
    (SELECT 
         DPPY_PFX,
         DPDP_ID,
         DPRL_DP_ID,
         ROW_NUMBER   
     FROM 
         ##pivot) AS StudentTable
PIVOT
    (COUNT (DPRL_DP_ID)
         FOR ROW_NUMBER IN ([1],[2],[3],[4])
    ) AS SchoolPivot

标签： sql-servertsqlpivotrankdata-interchange

Answer 1

您似乎想要字符串聚合：

select dppy_pfx, dpdp_id,
    string_agg(dprl_dp_id, ',') within group(order by row_number) dprl_dp_ids
from ##pivot
group by dppy_pfx, dpdp_id