我有這張表：
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
我需要這個查詢結果格式：
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
條形碼應該是動態的，因為每個 SKU BrandId 行都有未知的條形碼
uj5u.com熱心網友回復：
這個概念是構建一個動態資料透視查詢如下
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/369107.html
標籤：sql sql-server 查询语句