The data table structure is as follows:


Need to implement the following form:

CodePudding user response:

Select a. product number, quantity, weight, c. b. b. number, c. weight from
(select distinct product code from table) as a
Left the join (select product number, the sum (quantity) as the number, the sum weight (weight) as the from table where contract number='cn001' group by product number) as b on a. product number=b. product number
Left the join (select product number, the sum (quantity) as the number, the sum weight (weight) as the from table where contract number='cn002' group by product number) as b on a. product number=b. product number
And so on

CodePudding user response:

The contract is not fixed

CodePudding user response:

 
The 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, 200100 UNION ALL 
SELECT 'CN002', 'P001', 100, 50 UNION ALL 
SELECT 'CN003', 'P002, 50, 100 UNION ALL 
SELECT 'CN003', 'P003', 100, 50, 

DECLARE @ SQL VARCHAR (8000) 

WITH CTE 
AS 
(SELECT the '[' + CONTRACT_NO +' _ '+ TYPE +'] 'AS TYPE_NEW 
The FROM (SELECT DISTINCT CONTRACT_NO FROM # T) AS A 
The 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, ' ') 

The SET @ SQL='WITH CTE 
AS 
(SELECT ITEM_NO, CONTRACT_NO + '_' + TYPE AS TYPE_NEW, QTY 
The 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 * 
The FROM CTE A 
The PIVOT (SUM (QTY) FOR TYPE_NEW (+ SQL + '@')) IN B '

The EXEC (@ SQL)