資料表結構如下：
需要實作如下表格：
uj5u.com熱心網友回復：
select a.產品編號,b.數量,b.重量,c.數量,c.重量 from(select distinct 產品編號 from 表) as a
left join (select 產品編號,sum(數量) as 數量,sum(重量) as 重量 from 表 where 合同號='cn001' group by 產品編號) as b on a.產品編號=b.產品編號
left join (select 產品編號,sum(數量) as 數量,sum(重量) as 重量 from 表 where 合同號='cn002' group by 產品編號) as b on a.產品編號=b.產品編號
以此類推
uj5u.com熱心網友回復：
合同號也是不固定的uj5u.com熱心網友回復：
CREATE TABLE #T
(CONTRACT_NO VARCHAR(10),
ITEM_NO VARCHAR(10),
QTY INT,
WEIGHT INT)
INSERT INTO #T
SELECT 'CN001','P001',100,50 UNION ALL
SELECT 'CN001','P002',200,100 UNION ALL
SELECT 'CN002','P001',100,50 UNION ALL
SELECT 'CN003','P002',100,50 UNION ALL
SELECT 'CN003','P003',100,50
DECLARE @SQL VARCHAR(8000)
WITH CTE
AS
(SELECT '['+CONTRACT_NO+'_'+TYPE+']' AS TYPE_NEW
FROM (SELECT DISTINCT CONTRACT_NO FROM #T) AS A
JOIN (SELECT 'QTY' AS TYPE UNION SELECT 'WEIGHT') AS B ON 1=1)
SELECT @SQL=STUFF((SELECT ','+TYPE_NEW FROM CTE ORDER BY TYPE_NEW FOR XML PATH('')),1,1,'')
SET @SQL='WITH CTE
AS
(SELECT ITEM_NO,CONTRACT_NO+''_''+TYPE AS TYPE_NEW,QTY
FROM
(SELECT CONTRACT_NO,ITEM_NO,QTY,''QTY'' AS TYPE FROM #T
UNION ALL
SELECT CONTRACT_NO,ITEM_NO,WEIGHT,''WEIGHT'' FROM #T) AS A)
SELECT *
FROM CTE A
PIVOT (SUM(QTY) FOR TYPE_NEW IN('+@SQL+'))B'
EXEC(@SQL)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/269128.html
標籤：基礎類