CODEWITHSUNDEEP
sqlsql-server
Gie
Gie

Reputation: 43

Display date values as column

Below is my code to display date values as column name. But it won't get the new data of the next month unless encoded in Pivot values. How should I do it dynamically?

SELECT *
FROM
(
SELECT REGION, STATUS, CAST((MONTH(TRANS_DATE))AS VARCHAR)+'-'+CAST(YEAR(TRANS_DATE)AS VARCHAR) AS TMONTH
FROM SOM_SAMPLE_MOVEMENT SM WHERE REGION IS NOT NULL AND TRANS_DATE IS NOT NULL AND STATUS='RECEIVED'

) T
PIVOT
(
COUNT(STATUS)
FOR TMONTH
IN ([11-2019],[12-2019],[1-2020],[2-2020],[3-2020])
)AS PVT

Below is the sample output.

REGION  11-2019 12-2019 1-2020  2-2020  3-2020
CEBU       3      10      15      0       0

Upvotes: 2

Views: 57

Answers (1)

Tyron78
Tyron78

Reputation: 4187

You could use "real" dynamic SQL: First get your list of months and store it into a nvarchar variable using XML path. Then store your pivot query into a new variable, using the built month string and execute it. See Fiddle for details:

SQL Fiddle

MS SQL Server 2017 Schema Setup:

CREATE TABLE t1(
  REGION nvarchar(10), STATUS nvarchar(10), TRANS_DATE datetime
 );

Query 1:

INSERT INTO T1 VALUES
('CEBU', 'Received', '2019-12-01'),
('CEBU', 'Received', '2019-12-01'),
('CEBU', 'Received', '2019-12-01'),
('CEBU', 'Received', '2020-01-01'),
('CEBU', 'Received', '2020-01-01'),
('CEBU', 'Received', '2020-01-02'),
('CEBU', 'Received', '2020-02-01'),
('CEBU', 'Received', '2020-03-01'),
('CEBU', 'Received', '2020-03-01'),
('CEBU', 'Received', '2020-05-01'),
('CEBU', 'Received', '2020-06-01')

Results:

Query 2:

DECLARE @MonthList NVARCHAR(max) =(
SELECT  STUFF(( SELECT  ', ' + '['+TMONTH+']'
                FROM    ( SELECT DISTINCT CAST((MONTH(TRANS_DATE))AS VARCHAR)+'-'+CAST(YEAR(TRANS_DATE)AS VARCHAR) AS TMONTH
  FROM T1
                        ) x
              FOR
                XML PATH('')
              ), 1, 2, '') AllMonth
)

DECLARE @Stmt NVARCHAR(max) = 'SELECT *
FROM
(
SELECT REGION, STATUS, CAST((MONTH(TRANS_DATE))AS VARCHAR)+'+char(39)+'-'+char(39)+'+CAST(YEAR(TRANS_DATE)AS VARCHAR) AS TMONTH
FROM T1 SM WHERE REGION IS NOT NULL AND TRANS_DATE IS NOT NULL AND STATUS='+char(39)+'RECEIVED'+char(39)+'

) T
PIVOT
(
COUNT(STATUS)
FOR TMONTH
IN ('+@MonthList+')
)AS PVT'

EXEC(@stmt)

Results:

| REGION | 1-2020 | 12-2019 | 2-2020 | 3-2020 | 5-2020 | 6-2020 |
|--------|--------|---------|--------|--------|--------|--------|
|   CEBU |      3 |       3 |      1 |      2 |      1 |      1 |

Upvotes: 2

Related Questions