Trying to learn (2008) 1

[MichaelRed]

Declare @cols as nvarChar(MAX)

With YearsCTE as
(SELECT Distinct YEAR(OrderDate) As [YEAR] From Sales.SalesOrderHeader)

Select @cols = IsNull(@cols + ',[', '[') + Cast([Year] as nvarChar(10)) + ']'
	From YearsCTE
	Order By [YEAR]

	/*
		Construct the full T-SQL statement and execute it dynamically
	*/
Declare @Sql as NVarChar(MAX)
Set @Sql = 'Select *
			From
			(Select CustomerId, Year(OrderDate) as OrderYear, TotalDue
				From Sales.SalesOrderHeader)
				As a
			Pivot
				(
					Sum {TotalDue) For OrderYear in(' + @cols + N')
				) As b'

Print @SQL /* for debugging */
exec sp_ExecuteSql @Sql

Msg 102, Level 15, State 1, Line 8

the double left bracket in the isnull function,seems horribly wrong, but then thats just me. The err appears to move a bit depending on randomized nothings, but always references the left paren.

I have struggled with this one far to long ...

please ...




MichaelRed

 

[markros]

1. There is a typo in your SQL { instead of [.

I would write it a bit differently:

Declare @cols as nvarChar(MAX)

With YearsCTE as
(SELECT Distinct YEAR(OrderDate) As [YEAR] From Sales.SalesOrderHeader)

Select @cols = stuff((select ', ' + quotename([Year])
From YearsCTE
Order By [YEAR] FOR XML PATH('')),1,2,'')

/*
Construct the full T-SQL statement and execute it dynamically
*/
Declare @Sql as NVarChar(MAX)
Set @Sql = 'Select *
From
(Select CustomerId, Year(OrderDate) as OrderYear, TotalDue
From Sales.SalesOrderHeader)
As a
Pivot
(
Sum (TotalDue) For OrderYear in (' + @cols + ')) As b'

Print @SQL /* for debugging */
exec sp_ExecuteSql @Sql

PluralSight Learning Library

 

[MichaelRed]

Thanks. Tired old eyes and a small font get the blame, you get the star of fame!!!

MichaelRed