How to control number of columns after pivoting date as column

Posted on

Question :

Here is the sample query:

DECLARE @cols NVARCHAR(MAX) DECLARE @query NVARCHAR(MAX)

Select @cols = COALESCE(@cols +',[' +  CONVERT(VARCHAR(10), ReportedDate,120)+']',      '[' + CONVERT(VARCHAR(10), reportedDate, 120)
+']')       FROM (SELECT DISTINCT CONVERT(VARCHAR(10),reportedDate,120)         AS reportedDate FROM Activity       ) PV ORDER BY reportedDate desc


SELECT @query = 'SELECT * FROM (    SELECT  b.Description, CONVERT(VARCHAR(10), reportedDate, 120) as reportedDate,Status FROM Activity    left join ActivityType b     on b.activityTypeId = Activity.ActivityTypeId ) AS t PIVOT  (   COUNT(reportedDate)    FOR reportedDate IN( ' + @cols + ' )' + ') AS p ;'

EXEC SP_EXECUTESQL @query

This is my sample image:
I want to control the date columns limit into only 8 columns.
as reference of what I mean please see the second table and its different from the first table.

So in my first table date column from ‘2020-01-07’ up to ‘2019-11-12’
However my second table only up date ‘2020-01-07’ up to ‘2019-11-19’


I want to get the result as shown in this second Table
assuming that this date ‘2020-01-07’ is newly added.
so the ‘2019-11-12’ will not be included.

I hope I did will explaining my point.

Answer :

change the inner query to following using TOP () GROUP BY and ORDER BY to limit to 8 distinct date

SELECT  TOP (8) CONVERT(VARCHAR(10), ReportedDate, 120)  AS ReportedDate 
FROM    Activity       
GROUP BY CONVERT(VARCHAR(10), ReportedDate, 120)
ORDER BY ReportedDate

Leave a Reply

Your email address will not be published.