For help, the query-CodePudding

A table is as follows:

How language query results are as follows: I think every month when the column name, receiving number below in

CodePudding user response:

 
The CREATE TABLE # A (
ID VARCHAR (20), 
DDATE VARCHAR (20), 
NUM INT 
) 
# INSERT INTO A VALUES (' CA01 ', 'in January 2020, 1) 
# INSERT INTO A VALUES (' CA01 ', 'in February 2020, 2) 
# INSERT INTO A VALUES (' CA01 ', 'in March 2020, 3) 
# INSERT INTO A VALUES (' CA02 ', 'in January 2020, 4) 
# INSERT INTO A VALUES (' CA03 ', 'in January 2020, 5) 
# INSERT INTO A VALUES (' CA04 ', 'in January 2020, 6) 
# INSERT INTO A VALUES (' CA04 ', 'in February 2020, 7) 
# INSERT INTO A VALUES (' CA04 ', 'in March 2020, 8) 
# INSERT INTO A VALUES (' CA05 ', 'in January 2020, 9) 

SELECT * FROM # A 

DECLARE @ SQL VARCHAR (MAX)=', @ SQL1 VARCHAR (MAX) 
SELECT SQL + SQL=@ @ ', '+ QUOTENAME (a. d. DATE) FROM # aa GROUP BY a. d. DATE 

The SET @ SQL1='
SELECT * FROM # A 
The PIVOT (
MAX (NUM) FOR DDATE IN (' + STUFF (@ SQL, 1, 1, ' ') + ') 
) A '

The EXEC (@ SQL1) 

DROP TABLE # A

CodePudding user response:

reference 1/f, kun jins kao kun jins kao response:

 
The CREATE TABLE # A (
ID VARCHAR (20), 
DDATE VARCHAR (20), 
NUM INT 
) 
# INSERT INTO A VALUES (' CA01 ', 'in January 2020, 1) 
# INSERT INTO A VALUES (' CA01 ', 'in February 2020, 2) 
# INSERT INTO A VALUES (' CA01 ', 'in March 2020, 3) 
# INSERT INTO A VALUES (' CA02 ', 'in January 2020, 4) 
# INSERT INTO A VALUES (' CA03 ', 'in January 2020, 5) 
# INSERT INTO A VALUES (' CA04 ', 'in January 2020, 6) 
# INSERT INTO A VALUES (' CA04 ', 'in February 2020, 7) 
# INSERT INTO A VALUES (' CA04 ', 'in March 2020, 8) 
# INSERT INTO A VALUES (' CA05 ', 'in January 2020, 9) 

SELECT * FROM # A 

DECLARE @ SQL VARCHAR (MAX)=', @ SQL1 VARCHAR (MAX) 
SELECT SQL + SQL=@ @ ', '+ QUOTENAME (a. d. DATE) FROM # aa GROUP BY a. d. DATE 

The SET @ SQL1='
SELECT * FROM # A 
The PIVOT (
MAX (NUM) FOR DDATE IN (' + STUFF (@ SQL, 1, 1, ' ') + ') 
) A '

The EXEC (@ SQL1) 

DROP TABLE # A

When is what the PIVOT is MAX

CodePudding user response:

refer to the second floor RINK_1 response:

Quote: refer to 1st floor kun jins kao kun jins kao response:

 
The CREATE TABLE # A (
ID VARCHAR (20), 
DDATE VARCHAR (20), 
NUM INT 
) 
# INSERT INTO A VALUES (' CA01 ', 'in January 2020, 1) 
# INSERT INTO A VALUES (' CA01 ', 'in February 2020, 2) 
# INSERT INTO A VALUES (' CA01 ', 'in March 2020, 3) 
# INSERT INTO A VALUES (' CA02 ', 'in January 2020, 4) 
# INSERT INTO A VALUES (' CA03 ', 'in January 2020, 5) 
# INSERT INTO A VALUES (' CA04 ', 'in January 2020, 6) 
# INSERT INTO A VALUES (' CA04 ', 'in February 2020, 7) 
# INSERT INTO A VALUES (' CA04 ', 'in March 2020, 8) 
# INSERT INTO A VALUES (' CA05 ', 'in January 2020, 9) 

SELECT * FROM # A 

DECLARE @ SQL VARCHAR (MAX)=', @ SQL1 VARCHAR (MAX) 
SELECT SQL + SQL=@ @ ', '+ QUOTENAME (a. d. DATE) FROM # aa GROUP BY a. d. DATE 

The SET @ SQL1='
SELECT * FROM # A 
The PIVOT (
MAX (NUM) FOR DDATE IN (' + STUFF (@ SQL, 1, 1, ' ') + ') 
) A '

The EXEC (@ SQL1) 

DROP TABLE # A

When is what the PIVOT is MAX

to use aggregation function, can't help it

CodePudding user response:

refer to the second floor RINK_1 response:

Quote: refer to 1st floor kun jins kao kun jins kao response:

 
The CREATE TABLE # A (
ID VARCHAR (20), 
DDATE VARCHAR (20), 
NUM INT 
) 
# INSERT INTO A VALUES (' CA01 ', 'in January 2020, 1) 
# INSERT INTO A VALUES (' CA01 ', 'in February 2020, 2) 
# INSERT INTO A VALUES (' CA01 ', 'in March 2020, 3) 
# INSERT INTO A VALUES (' CA02 ', 'in January 2020, 4) 
# INSERT INTO A VALUES (' CA03 ', 'in January 2020, 5) 
# INSERT INTO A VALUES (' CA04 ', 'in January 2020, 6) 
# INSERT INTO A VALUES (' CA04 ', 'in February 2020, 7) 
# INSERT INTO A VALUES (' CA04 ', 'in March 2020, 8) 
# INSERT INTO A VALUES (' CA05 ', 'in January 2020, 9) 

SELECT * FROM # A 

DECLARE @ SQL VARCHAR (MAX)=', @ SQL1 VARCHAR (MAX) 
SELECT SQL + SQL=@ @ ', '+ QUOTENAME (a. d. DATE) FROM # aa GROUP BY a. d. DATE 

The SET @ SQL1='
SELECT * FROM # A 
The PIVOT (
MAX (NUM) FOR DDATE IN (' + STUFF (@ SQL, 1, 1, ' ') + ') 
) A '

The EXEC (@ SQL1) 

DROP TABLE # A

When is what the PIVOT is MAX

See the meaning of the original poster date should be a corresponding to a number, if a date corresponding to the number two, such as
nullnullnull