Flatten table rows into columns in SQL Server [dup

问题:

This question already has an answer here:

• Insert and update in a cursor in SQL Server 1 answer

I have the below SQl Table which has the data generated randomly

  Code          Data
    SL Payroll    22
    SL Payroll    33
    SL Payroll    43
    ..            .....

I want to transfer the data so the format becomes as shown below

Code         Data1   Data2   Data3  ..
SL Payroll   22       33      43    ....  

Someone suggested Pivot table to transform the data as below

SELECT Code,
       [22] Data1,
       [33] Data2,
       [43] Data3
FROM
    (
      SELECT *
      FROM T
    ) TBL
    PIVOT
    (
      MAX(Data) FOR Data IN([22],[33],[43])
    ) PVT

but this assumes the data points are static like 22,33 but they are dynamically generated. Can someone please help me out.

Thanks

回答1:

I would use conditional aggregate along with row_number():

select code,
       max(case when seqnum = 1 then code end) as code_1,
       max(case when seqnum = 2 then code end) as code_2,
       max(case when seqnum = 3 then code end) as code_3
from (select t.*,
             row_number() over (partition by code order by data) as seqnum
      from t
     ) t
group by code;

回答2:

If you have a know or maximum number of desired columns, you can do a simple PIVOT, otherwise, you would need to go DYNAMIC

Example

 Select *
  From (
        Select [Code]
              ,[Data]
              ,[Col] = concat('Data',Row_Number() over (Partition By [Code] Order by 1/0))
         From  YourTable
       ) src
 Pivot (max([Data]) for [Col] in ([Data1],[Data2],[Data3],[Data4],[Data5])) pvt

Returns

Code        Data1   Data2   Data3   Data4   Data5
SL Payroll  22      33      43      NULL    NULL

回答3:

to do dynamic pivot I did it a long way back.

UPDATED: Made more close to your code and talble names.

This will work for however many columns you want for the PIVOT, doesnt matter if 1 or 20

DECLARE @SelectFieldNameList as varchar(8000)
DECLARE @SelectFieldNameListCount as varchar(8000)
Set @SelectFieldNameList = ''
Set @SelectFieldNameListCount = ''

-- this section selects the list of firm location names and puts them in a string to be used in the pivot
-- it does it for the field select list and the count select using ISNULL so it puts a 0 if no counts returned
SELECT top (999999) @SelectFieldNameList = @SelectFieldNameList + case when @SelectFieldNameList = '' then '' else ', ' end 
+ '[' + Data + ']', 
@SelectFieldNameListCount = @SelectFieldNameListCount + case when @SelectFieldNameListCount = '' then '' else ', ' end 
+ 'ISNULL([' + Data + '], ''0'')' + Data 
From TableName
Where Data IS NOT NULL AND Ltrim(Data) <> ''
Group by Data

-- just for testing
select @SelectFieldNameList, @SelectFieldNameListCount


-- NOW USE THE ABOVE TO get the data pivoted with your dyanic fields
EXEC('
SELECT [Code], ' + @SelectFieldNameListCount + '
FROM (  
    SELECT [Code], Data, Sum(CountTotal) as CountTotal
    From TableName
    Group by [Code], Data
) AS TableToBePivoted
PIVOT (  
    SUM(CountTotal) 
    FOR Data IN (' + @SelectFieldNameList + ')  
) AS PivotedTable
order by [Code];  
')