SQL Server Pivot without column names-CodePudding

I have this table:

SKU    BrandId   Barcode
---------------------------------
1        1       123
1        1       987
1        1       852
1        2       951
1        2       753
2        1       926
2        1       364
2        2       854
2        2       256
2        2       351
2        2       157
2        3       976

I need this query result format:

SKU   BrandId   Barcode1   Barcode2    Barcode3    Barcode4
-------------------------------------------------------------
1      1         123       987          852
1      2         951       753
2      1         926       364
2      2         854       256          351         157
2      3         976

The barcode should be dynamic because there is unkown Barcodes for every SKU BrandId row

CodePudding user response：

the concept is to build a dynamic pivot query as follow

 CREATE TABLE #t (sku nvarchar(5), brandid nvarchar(5), barcode nvarchar(50) )
truncate table #t
INSERT INTO #t VALUES 
    (1,1,150),
    (1,1,140),
    (1,1,111),
    (1,2,1234),
    (1,2,145),
    (2,1,345),
    (2,1,532),
    (2,2,875),
    (2,2,1237),
    (3,1,566)

select *,'Barcode' CONVERT (nvarchar(50),ROW_NUMBER()over(partition by sku, brandid order by sku)) rn from #t   


DECLARE @colName AS NVARCHAR(MAX), @pivotQ AS NVARCHAR(max)

SELECT @colName = ISNULL(@colName   ',', '')   QUOTENAME(rn) 
FROM (select DISTINCT 'Barcode' CONVERT (nvarchar(50),ROW_NUMBER()over(partition by sku, brandid order by sku)) rn from #t  
) AS Labels ORDER BY rn


SET @pivotQ = N'
SELECT sku,   brandid, ' @colName '
FROM  
(
  SELECT sku,   brandid , barcode , ''Barcode'' CONVERT (nvarchar(50),ROW_NUMBER()over(partition by sku, brandid order by sku)) rn
  FROM #t
) AS SourceTable  
PIVOT  
(  
  MAX(barcode)  
  FOR rn IN (' @colName ')  
) AS PivotTable; 
'

EXECUTE sp_executesql @pivotQ