0

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.

halfer
  • 19,824
  • 17
  • 99
  • 186
SP1
  • 1,182
  • 3
  • 22
  • 47
  • Is that your research??? https://stackoverflow.com/q/54134488/6426692 – Ilyes Jan 10 '19 at 18:57
  • Sami I am not a database developer per se but just had to finish this for my Front End code..I tried to look but a lot of examples were doing AVG or just transposing. I just saw your answer there..Really appreciate your help..I was actually just gonna do some looping and insert update tricks to achieve the result which was not cool like the Pivot stuff you suggested but just to get the work done. Short on time so asked for help. – SP1 Jan 10 '19 at 19:05

3 Answers3

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
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

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];  
')
Brad
  • 3,454
  • 3
  • 27
  • 50