0

I want to convert the below data as shown in the output

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

Output

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

I coded as below, but the value is not taken instead the count is alone taken.

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

Query

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
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
John Christopher
  • 37
  • 1
  • 8

1 Answers1

1

You seem to want string aggregation:

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
GMB
  • 216,147
  • 25
  • 84
  • 135