i've created my query string but am getting another issue.
when i use
-- create the dynamic pivot
set @query = N'SELECT AccountId, AccountName, StockGroup, StockGroupDescription, StockId, StockCode, StockDescription, ' + @cols + '
FROM (
SELECTAccountId, AccountName, StockId, StockCode, StockDescription, StockGroup, StockGroupDescription, Quantity, convert(varchar(10), Date, 120) PivotDate
FROM#t1) x
PIVOT
(
sum(Quantity)
for PivotDate in (' + @cols + ')
) p
order by AccountId, StockGroup, StockDescription, StockCode'
.. i get this error :
Msg 203, Level 16, State 2, Line 85
The name 'SELECT AccountId, AccountName, StockGroup, StockGroupDescription, StockId, StockCode, StockDescription, [2013-01-06],[2013-01-13],[2013-01-20],[2013-01-27],[2013-02-03],[2013-02-10],[2013-02-17],[2013-02-24],[2013-03-03],[2013-03-10],[2013-03-17],[2013-03-24],[2013-03-31],[2013-04-07],[2013-04-14],[2013-04-21],[2013-04-28],[2013-05-05],[2013-05-12],[2013-05-19],[2013-05-26],[2013-06-02],[2013-06-09],[2013-06-16],[2013-06-23],[2013-06-30],[2013-07-07],[2013-07-14],[2013-07-21],[2013-07-28],[2013-08-04],[2013-08-11],[2013-08-18],[2013-08-25],[2013-09-01],[2013-09-08],[2013-09-15],[2013-09-22],[2013-09-29],[2013-10-06],[2013-10-13],[2013-1' is not a valid identifier.
but when i use PRINT @query and use the displayed @query string..
SELECT AccountId, AccountName, StockGroup, StockGroupDescription, StockId, StockCode, StockDescription, [2013-01-06],[2013-01-13],[2013-01-20],[2013-01-27],[2013-02-03],[2013-02-10],[2013-02-17],[2013-02-24],[2013-03-03],[2013-03-10],[2013-03-17],[2013-03-24],[2013-03-31],[2013-04-07],[2013-04-14],[2013-04-21],[2013-04-28],[2013-05-05],[2013-05-12],[2013-05-19],[2013-05-26],[2013-06-02],[2013-06-09],[2013-06-16],[2013-06-23],[2013-06-30],[2013-07-07],[2013-07-14],[2013-07-21],[2013-07-28],[2013-08-04],[2013-08-11],[2013-08-18],[2013-08-25],[2013-09-01],[2013-09-08],[2013-09-15],[2013-09-22],[2013-09-29],[2013-10-06],[2013-10-13],[2013-10-20],[2013-10-27],[2013-11-03],[2013-11-10],[2013-11-17],[2013-11-24],[2013-12-01],[2013-12-08],[2013-12-15],[2013-12-22],[2013-12-29]
FROM (
SELECTAccountId, AccountName, StockId, StockCode, StockDescription, StockGroup, StockGroupDescription, Quantity, convert(varchar(10), Date, 120) PivotDate
FROM#t1) x
PIVOT
(
sum(Quantity)
for PivotDate in ([2013-01-06],[2013-01-13],[2013-01-20],[2013-01-27],[2013-02-03],[2013-02-10],[2013-02-17],[2013-02-24],[2013-03-03],[2013-03-10],[2013-03-17],[2013-03-24],[2013-03-31],[2013-04-07],[2013-04-14],[2013-04-21],[2013-04-28],[2013-05-05],[2013-05-12],[2013-05-19],[2013-05-26],[2013-06-02],[2013-06-09],[2013-06-16],[2013-06-23],[2013-06-30],[2013-07-07],[2013-07-14],[2013-07-21],[2013-07-28],[2013-08-04],[2013-08-11],[2013-08-18],[2013-08-25],[2013-09-01],[2013-09-08],[2013-09-15],[2013-09-22],[2013-09-29],[2013-10-06],[2013-10-13],[2013-10-20],[2013-10-27],[2013-11-03],[2013-11-10],[2013-11-17],[2013-11-24],[2013-12-01],[2013-12-08],[2013-12-15],[2013-12-22],[2013-12-29])
) p
ORDER BY AccountId, StockGroup, StockDescription, StockCode
..it works fine. @query variable is declare @query AS NVARCHAR(MAX). The string len is 1798.
am i missing something? thanks