How to insert Query Result in PIVOT

Question

I am using SQL SERVER 2012.

Request: 1

SELECT *
FROM (
    SELECT Insurance, ChargeValue, CreatedDate
    FROM dailychargesummary
    WHERE MonthName='June 2017'
) m
PIVOT (
    SUM(ChargeValue)
    FOR CreatedDate IN ([06/22/2017], [06/23/2017],[06/30/2017])
) n

The output of the above query is as follows:

Now I hardcode all the dates of the month inside Pivot Query, such as 01/06/2017, 02/06/2017, etc. After searching on Google, I received the following query to display all the dates of this month.

Request 2:

DECLARE @month AS INT = 5
DECLARE @Year AS INT = 2016

;WITH N(N)AS 
(SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1))M(N)),
tally(N)AS(SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a)
SELECT datefromparts(@year,@month,N) date FROM tally
WHERE N <= day(EOMONTH(datefromparts(@year,@month,1)))

The result is as follows:

Can someone please guide me how to use Query2 inside a rotation in Query1 to automate dates.

Answer 1

You need to use a dynamic query to make a dynamic list of speakers

first assign a list of dates to a variable.

Declare @pivot_list varchar(8000)= ''
DECLARE @month AS INT = 5
DECLARE @Year AS INT = 2016

;WITH N(N)     AS (SELECT 1 FROM(VALUES(1),(1),(1),(1),(1),(1))M(N)),
      tally(N) AS (SELECT ROW_NUMBER()OVER(ORDER BY N.N)FROM N,N a)
select @pivot_list = stuff((SELECT ','+quotename(convert(varchar(15),datefromparts(@year,@month,N),101)) 
                            FROM tally
                            WHERE N <= day(EOMONTH(datefromparts(@year,@month,1))) for xml path('')),1,1,'')


--Print @pivot_list

@pivot_list

Declare @sql varchar(8000)
set @sql = '
SELECT *
FROM (
    SELECT Insurance, ChargeValue, CreatedDate
    FROM dailychargesummary
    WHERE MonthName='June 2017'
) m
PIVOT (
    SUM(ChargeValue)
    FOR CreatedDate IN ('+@pivot_list+')
) n'

--Print @sql

Exec @sql