如何生成這樣的格式
uj5u.com熱心網友回復：
原始資料里, 企業名稱里沒有等于"沃爾瑪"的記錄喔?uj5u.com熱心網友回復：
CREATE TABLE #T
(ITEM_NAME VARCHAR(20),
QTY INT,
ITEM_NO VARCHAR(10),
BUYER VARCHAR(10),
PRICE INT,
DELIVERY_DAY INT)
INSERT INTO #T
SELECT 'MEMORY',11,'001','AAAAAA',999,9 UNION ALL
SELECT 'POWER',12,'002','AAAAAA',888,8 UNION ALL
SELECT 'MEMORY',11,'001','BBBBB',777,7 UNION ALL
SELECT 'POWER',12,'002','BBBBB',666,6
WITH CTE_1
AS
(SELECT *,DENSE_RANK() OVER (ORDER BY ITEM_NAME) AS SEQ_1 FROM #T),
CTE_2
AS
(SELECT ITEM_NAME,'' AS VALUE,BUYER,SEQ_1,1 AS SEQ_2 FROM CTE_1
UNION ALL
SELECT 'PRICE',CAST(PRICE AS VARCHAR(10)),BUYER,SEQ_1,2 AS SEQ_2 FROM CTE_1
UNION ALL
SELECT 'DELIVERY_DAY',CAST(DELIVERY_DAY AS VARCHAR(10)),BUYER,SEQ_1,3 AS SEQ_2 FROM CTE_1)
SELECT ITEM_NAME AS BUYER,[AAAAAA],[BBBBB] FROM CTE_2
PIVOT (MAX(VALUE) FOR BUYER IN ([AAAAAA],[BBBBB])) B
ORDER BY SEQ_1,SEQ_2
如果企業名稱不固定，那就得是動態的了。
uj5u.com熱心網友回復：
對的企業名字不固定uj5u.com熱心網友回復：
CREATE TABLE #T
(ITEM_NAME VARCHAR(20),
QTY INT,
ITEM_NO VARCHAR(10),
BUYER VARCHAR(10),
PRICE INT,
DELIVERY_DAY INT)
INSERT INTO #T
SELECT 'MEMORY',11,'001','AAAAAA',999,9 UNION ALL
SELECT 'POWER',12,'002','AAAAAA',888,8 UNION ALL
SELECT 'MEMORY',11,'001','BBBBB',777,7 UNION ALL
SELECT 'POWER',12,'002','BBBBB',666,6
DECLARE @SQL VARCHAR(1000)
SELECT @SQL=ISNULL(@SQL+',','')+'['+BUYER+']'
FROM (SELECT DISTINCT BUYER FROM #T) AS A
SET @SQL='WITH CTE_1
AS
(SELECT *,DENSE_RANK() OVER (ORDER BY ITEM_NAME) AS SEQ_1 FROM #T),
CTE_2
AS
(SELECT ITEM_NAME,'''' AS VALUE,BUYER,SEQ_1,1 AS SEQ_2 FROM CTE_1
UNION ALL
SELECT ''PRICE'',CAST(PRICE AS VARCHAR(10)),BUYER,SEQ_1,2 AS SEQ_2 FROM CTE_1
UNION ALL
SELECT ''DELIVERY_DAY'',CAST(DELIVERY_DAY AS VARCHAR(10)),BUYER,SEQ_1,3 AS SEQ_2 FROM CTE_1)
SELECT ITEM_NAME AS BUYER,'+@SQL+'FROM CTE_2
PIVOT (MAX(VALUE) FOR BUYER IN ('+@SQL+')) B
ORDER BY SEQ_1,SEQ_2'
EXEC(@SQL)
uj5u.com熱心網友回復：
大佬，你技術太牛了，有啥教程可以推薦嗎？把sql可以玩的這么溜uj5u.com熱心網友回復：
沒啥牛的，平時作業和這些沒啥關系，就是論壇泡出來的
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/11416.html
標籤：基礎類
上一篇：sql 行列轉化
下一篇：求大佬