SQL Server- PIVOT Table to one column per date

Question

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 the month and year. Those parameters are put into the WHERE clause of a subquery.

SQL so far:

SELECT IDNbr, [Name]
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 ([BalDate]) --Error: see below
 ) piv;

The error I get in the Pivot section is:

The column name "BalDate" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.

And I'm also getting the error:

The column 'BalDate' was specified multiple times for 'piv'.

Current data:

Customer  |   BalDate   | Balance
----------+-------------+--------
Customer1 |  1/01/2017  | 0.00
Customer1 |  1/02/2017  | 0.00
Customer1 |  1/03/2017  | 0.00
Customer1 |  1/04/2017  | 0.00
....      |  ....       | ....

Desired data:

|   Customer   |   01/01/2017  |   01/02/2017  |    01/03/2017  |   01/04/2017  |    01/05/2017  |   01/06/2017  |    01/07/2017  |   01/08/2017  |    01/09/2017  |    01/10/2017  | ....
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|    Customer1 |     0.00      |      0.00     |      0.00      |     0.00      |      0.00      |     0.00      |      0.00      |      0.00     |      0.00      |      0.00      | ....
|    Customer2 |     0.00      |      0.00     |      0.00      |     0.00      |      0.00      |     0.00      |      0.00      |      0.00     |      0.00      |      0.00      | ....
|    Customer3 |     0.00      |      0.00     |      0.00      |     0.00      |      0.00      |     0.00      |      0.00      |      0.00     |      0.00      |      0.00      | ....

Primary Question: How would I go about correcting the errors in my Pivot Table?

Secondary Question: How would I be able to get the Current data to Desired data?

I'm still very much a beginner in SQL and SQL Server so I apologize if the answer is obvious.

Thank you in advance for any help and advice! And I will gladly try to clarify on anything that seems confusing or unclear!

---

Posts I've referenced, and attempted to apply to my situation:

SQL Pivot Multiple Columns

Column name specified in the pivot operator conflicts

Column was specified multiple times

How to Create A CrossTab query

SQL Server Pivot Column Data

Answer 1

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
WHERE YEAR(BalDate) = @year
  AND MONTH(BalDate) = @month 
GROUP BY BalDate
ORDER BY 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;

Answer 2

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 @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + 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

Answer 3

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
                    group by a1.BalDate
                    order by 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
ORDER BY 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.