Transpose data/Dynamic pivot based on ID (no calculation)

Question

I need a bit of help transposing data based on a numeric column. ID is dynamic (it can go as high as 35).

Format:

ID  SKU Q       A
1   123 Q1      A1
2   123 Q100    A2
3   123 Q200    A3
1   456 Q2      A1
2   456 Q200    A4
3   456 Q201    A5
4   456 Q203    A6
1   789 Q1      A7
2   789 Q2      A2
3   789 Q3      A3

Would need to be transposed as per:

SKU  Q+A (ID1)      Q+A (ID2)   Q+A (ID3)       Q+A (ID4)
123  Q1:A1          Q100:A2     Q200A3          NULL
456  Q100:A2        Q200:A4     Q201:A5         Q203:A6
789  Q1:A7          Q2:A2       Q3:A3           NULL

Can you guys help please?

Thank you in advance.

KeithL · Accepted Answer · 2018-10-30T13:25:39.040

Here is a solution using CASE for your pivot:

declare @t table (ID int,SKU int,Q varchar(100),A varchar(100))

insert into @t
values
    (   1   ,   123 ,   'Q1'    ,   'A1'    )
,   (   2   ,   123 ,   'Q100'  ,   'A2'    )
,   (   3   ,   123 ,   'Q200'  ,   'A3'    )
,   (   1   ,   456 ,   'Q2'    ,   'A1'    )
,   (   2   ,   456 ,   'Q200'  ,   'A4'    )
,   (   3   ,   456 ,   'Q201'  ,   'A5'    )
,   (   4   ,   456 ,   'Q203'  ,   'A6'    )
,   (   1   ,   789 ,   'Q1'    ,   'A7'    )
,   (   2   ,   789 ,   'Q2'    ,   'A2'    )
,   (   3   ,   789 ,   'Q3'    ,   'A3'    )


;with setup as 
(
select distinct SKU
    ,Q+':'+A [Q+A]
    ,'Q+A (ID'+cast(ID as varchar(2)) + ')' PivotOn
from @t
)

select SKU
    , max(case when PivotOn = 'Q+A (ID1)' then [Q+A] end) as [Q+A (ID1)]
    , max(case when PivotOn = 'Q+A (ID2)' then [Q+A] end) as [Q+A (ID2)]
    , max(case when PivotOn = 'Q+A (ID3)' then [Q+A] end) as [Q+A (ID3)]
    , max(case when PivotOn = 'Q+A (ID4)' then [Q+A] end) as [Q+A (ID4)]

from setup
group by SKU

Results:

SKU Q+A (ID1)   Q+A (ID2)   Q+A (ID3)   Q+A (ID4)
123 Q1:A1       Q100:A2     Q200:A3     NULL
456 Q2:A1       Q200:A4     Q201:A5     Q203:A6
789 Q1:A7       Q2:A2       Q3:A3       NULL

You will need to take the case statements up to the MAX ID which in your stated case is 35.

sorry, I may not have been very clear, this data already exists currently and all I need is to write a select statement that pivots the data. there are over 15,000 rows in my current data so I will need this to be dynamic. — TheTallBloke, Oct 30 '18 at 13:48
I would add your 35 case statements so that you can use this data set in the future as you will know the column names that are being returned. If you use dynamic SQL, you will not know what columns are being returned (i.e. is column [Q+A ID21] in the data set?) — KeithL, Oct 30 '18 at 13:54

Sreenu131 · Answer 2 · 2018-10-30T13:32:03.880

Sample Data

IF OBJECT_ID('tempdb..#TempData')IS NOT NULL
DROP TABLE #TempData

DECLARE @t table (ID int,SKU int,Q varchar(100),A varchar(100))
Insert into @t
values
    (   1   ,   123 ,   'Q1'    ,   'A1'   )
,   (   2   ,   123 ,   'Q100'  ,   'A2'   )
,   (   3   ,   123 ,   'Q200'  ,   'A3'   )
,   (   1   ,   456 ,   'Q2'    ,   'A1'   )
,   (   2   ,   456 ,   'Q200'  ,   'A4'   )
,   (   3   ,   456 ,   'Q201'  ,   'A5'   )
,   (   4   ,   456 ,   'Q203'  ,   'A6'   )
,   (   1   ,   789 ,   'Q1'    ,   'A7'   )
,   (   2   ,   789 ,   'Q2'    ,   'A2'   )
,   (   3   ,   789 ,   'Q3'    ,   'A3'   )
,   (   1   ,   478 ,   'Q10'    ,  'A47'  )
,   (   2   ,   478 ,   'Q20'    ,  'A12'  )
,   (   3   ,   478 ,   'Q34'    ,  'A03'  )
,   (   4   ,   478 ,   'Q34'    ,  'A03'  )
,   (   5   ,   478 ,   'Q34'    ,  'A03'  )
,   (   6   ,   478 ,   'Q34'    ,  'A03'  )
,   (   7   ,   478 ,   'Q34'    ,  'A03'  )
,   (   8   ,   478 ,   'Q34'    ,  'A03'  )


SELECT *,(Q+':'+A) AS Qdata, 'Q+A('+CAST(DENSE_RANK()OVER(ORDER BY ID) AS VARCHAR(10))+')'AS RID
INTO #TempData
FROM @t

Dynamic Sql using Pivot

DECLARE @Columns VARCHAR(MAX) ='',
        @Columns2 VARCHAR(MAX) ='',
        @Sql nvarchar (max)=''

SELECT @Columns=STUFF((SELECT DISTINCT ',',+ QUOTENAME(RID )
                FROM  #TempData FOR XML PATH ('')),1,1,'')
SELECT @Columns2=STUFF((SELECT DISTINCT ',',+ 'ISNULL(MAX('+QUOTENAME(RID ) +') ,''NA'') AS '+QUOTENAME(RID )
                FROM  #TempData FOR XML PATH ('')),1,1,'')

SET @Sql='SELECT SKU,'+@Columns2+' FROM
(
SELECT * FROM #TempData
) AS SRC
PIVOT
(
MAX(Qdata) FOR RID IN('+@Columns+') 
) AS PVT 
GROUP BY SKU'
PRINT @Sql
EXEC (@Sql)

Result

SKU     Q+A(1)      Q+A(2)      Q+A(3)      Q+A(4)      Q+A(5)      Q+A(6)      Q+A(7)      Q+A(8)
--------------------------------------------------------------------------------------------------
123     Q1:A1       Q100:A2     Q200:A3      NA           NA         NA           NA        NA
456     Q2:A1       Q200:A4     Q201:A5     Q203:A6       NA         NA           NA        NA
478     Q10:A47     Q20:A12     Q34:A03     Q34:A03     Q34:A03     Q34:A03     Q34:A03     Q34:A03
789     Q1:A7       Q2:A2       Q3:A3        NA           NA         NA           NA        NA

sorry, I may not have been very clear, this data already exists currently and all I need is to write a select statement that pivots the data. there are over 15,000 rows in my current data so I will need this to be dynamic. — TheTallBloke, Oct 30 '18 at 13:48
sorry, I'm not quite sure how to use these in the context of a single table that holds all the data in the 'sample' format — TheTallBloke, Oct 30 '18 at 13:54

Transpose data/Dynamic pivot based on ID (no calculation)

2 Answers2