A table has a few years of sales data, date of dt, amount of amt, I would like to export a table as follows, year increased automatically, not qualified

CodePudding user response:

 


The CREATE TABLE # t 
(
DATE DATE, 
The amount of MONEY 
) 

INSERT INTO # t VALUES (' 2018-1-1 ', 100) 
INSERT INTO # t VALUES (' 2019-2-1 ', 100) 
INSERT INTO # t VALUES (' 2020-3-1 ', 100) 


DECLARE @ filed VARCHAR (Max)='
DECLARE @ SQL VARCHAR (Max)='

SELECT @ filed=@ filed + ', '+ QUOTENAME (years) FROM 
(
SELECT YEAR (date) AS years, ROW_NUMBER () OVER (ORDER BY YEAR (date)) AS id FROM # t 
) a ORDER BY id 


The SET @ SQL='
SELECT * FROM 
(
SELECT YEAR (date) AS YEAR, MONTH, date) AS MONTH, amount FROM # t 
) a 
The PIVOT 
(
The SUM (amount) 
FOR years IN (' + stuff (@ filed, 1, 1, ' ') + ') 
) b '

The EXEC (@ SQL) 

DROP TABLE # t 

CodePudding user response:

I get my data to this view, and then execute

DECLARE @ filed VARCHAR (Max)='
DECLARE @ SQL VARCHAR (Max)='
SELECT @ filed=@ filed + ', '+ QUOTENAME (years) FROM (SELECT Yar AS years, ROW_NUMBER () OVER (ORDER BY Yar) AS id FROM SalesMth) a ORDER BY id
SQL=SET @ 'SELECT * FROM (SELECT Yar AS years, Mth AS month, Amt FROM SalesMth) a PIVOT (SUM (Amt) FOR years IN (' + stuff (@ filed, 1, 1,' ') + ')) b '
The EXEC (@ SQL)

To quote the following error: again under the help, thank you
message 8156, level 16, state 1, line 1
Many times for the 'b' specifies the column 'Y2017',