Sql Server- Pivot Table To One Column Per Date

I'm currently moving an MS Access db to SQL Server. I have a cross-tab query I'm needing to convert to a Pivot table. The Pivot table will be in a sproc that gives parameters for t

Solution 1:

You can try to use dynamic SQL for BalDate.

due to your query has parameter so you can try to use sp_executesql and append parameters in the execute syntax.

DECLARE@colsAS NVARCHAR(MAX),
        @queryAS NVARCHAR(MAX);

SET@cols= STUFF((SELECTdistinct','+ QUOTENAME(a1.BalDate) 
            FROM IDTable a1
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')



set@query='SELECT *
FROM (
    SELECT a1.IDNbr , a2.[CustName] as [Name] , a1.BalDate, a1.Balance 
    FROM IDTable a1 INNER JOIN CustTable a2 ON (a1.IDNbr = a2.IDNbr)
    WHERE MONTH(a1.BalDate) = @month AND YEAR(a1.BalDate) = @year
) as d1
PIVOT (
    SUM(Balance)
    For Balance in ('+@cols+') --Error: see below
) piv'EXECUTE sp_executesql @query, N'@year INT,@month INT', 
                    @year=@year,
                    @month=@month

Solution 2:

Sounds like you need a dynamic pivot,

First you have to select all the dates to be used in pivot, The dates have to be in QuoteName format which is [Date], otherwise the pivot won't understand.

DECLARE@Days NVarchar(MAX)
SEt@Days= STUFF((SELECT','+ QUOTENAME(a1.BalDate) 
                    FROM IDTable a1
                    groupby a1.BalDate
                    orderby a1.BalDate
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
Select@Days

Then you have to build a dynamic pivot, something like this.

DECLARE @FinalQuery NVARCHAR (MAX)
SET @FinalQuery =   'SELECT *INTO #ToReportOn
                     FROM
                         (SELECT a1.IDNbr , a2.[CustName] as [Name] , a1.BalDate, a1.Balance 
                          FROM IDTable a1 INNER JOIN CustTable a2 ON (a1.IDNbr = a2.IDNbr)
                          WHERE MONTH(a1.BalDate) = @month AND YEAR(a1.BalDate) = @year) as d1
                         ) A
            PIVOT
                  (
                    SUM(Balance)
                   For BalDate in ('+@Days +')
                  ) B
ORDERBY document_group

SELECT * FROM #ToReportOn 
'
--EXECUTE(@FinalQuery)
PRINT @FinalQuery

I didn't test this. But I think the dynamic pivot is the approach to solve your problem.

Solution 3:

In MS Sql Server you'll need a dynamic sql for this.

First calculate a variable with the dates. Then use it to in the string for the dynamic query.

You can test it here on rextester

DECLARE@Dates NVARCHAR(max);

SELECT@Dates= CONCAT(@Dates+', ', QUOTENAME(BalDate)) 
FROM IDTable
WHEREYEAR(BalDate) =@yearANDMONTH(BalDate) =@monthGROUPBY BalDate
ORDERBY BalDate;

DECLARE@DynSql NVARCHAR(max); 
SET@DynSql='SELECT *
FROM
(
  SELECT a1.IDNbr, a2.[CustName] as [Name], a1.BalDate, a1.Balance 
  FROM IDTable a1 
  INNER JOIN CustTable a2 ON (a1.IDNbr = a2.IDNbr)
  WHERE MONTH(a1.BalDate) = @month 
  AND YEAR(a1.BalDate) = @year
) as src
PIVOT (
  SUM(Balance) 
  FOR BalDate IN ('+@Dates+') 
) pvt';

DECLARE@Params NVARCHAR(500) = N'@year INT, @month INT';  

-- SELECT @DynSql AS DynSql;EXECUTE sp_executesql @DynSql, @Params, @year=@year, @month=@month;