Apple juice apple 30
Apple juice water 70
Sweet orange juice orange 20
Sweet orange juice sugar 5
Sweet orange juice water 75
Lemon lemon water 10
Lemon water water 90
Convert
The product formula rate formula formula
Apple juice apple 30 water 70
Sweet orange juice orange crystal sugar water 75 20
Lemon lemon water 10 water 90
Is there a big, seek counsel
CodePudding user response:
Turn the grammar of the column,If used with piovt
Example:
https://www.cnblogs.com/itsone/p/10232433.html
CodePudding user response:
The CREATE TABLE # T
(ITEM NVARCHAR (20),
ELEMENT NVARCHAR (20),
Thewire INT)
INSERT INTO # T
SELECT 'apple juice', 'apple', 30 UNION ALL
SELECT 'apple juice', 'water', 70 UNION ALL
SELECT 'sweet orange juice', 'orange' 20 UNION ALL
SELECT 'sweet orange juice', 'sugar', 5 UNION ALL
SELECT 'sweet orange juice', 'water', 75 UNION ALL
SELECT 'lemonade', 'lemon', 10 UNION ALL
SELECT 'lemonade', 'water', 90
DECLARE @ SQL NVARCHAR (MAX)
; WITH CTE
AS
(SELECT *, ROW_NUMBER () OVER (PARTITION BY ITEM ORDER BY 1) (SELECT) AS SEQ FROM # T)
SELECT @ SQL=ISNULL (@ SQL + ', ', ') + 'MAX (CASE WHEN SEQ=' + CAST (SEQ AS VARCHAR) + 'THEN ELEMENT ELSE "' "' END) AS ELEMENT '+ + CAST (SEQ AS VARCHAR)
+ ', the SUM (CASE WHEN SEQ='+ CAST (SEQ AS VARCHAR) +' THEN thewire ELSE 0 END) AS thewire '+ + CAST (SEQ AS VARCHAR)
The FROM
(SELECT DISTINCT SEQ FROM CTE) AS A
The SET @ SQL='WITH CTE
AS
(SELECT *, ROW_NUMBER () OVER (PARTITION BY ITEM ORDER BY 1) (SELECT) AS SEQ FROM # T)
SELECT the ITEM, 'SQL + + @' FROM CTE GROUP BY ITEM '
The EXEC (@ SQL)