Doubt in Pivot operator - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Doubt in Pivot operator

Author

Topic

JackForever
Starting Member

1 Post

Posted - 2006-11-28 : 22:05:34

Hi all,

I have doubt in Pivot operator,please share your knowledge.

using AdventuresWork DB in (SQL Server 2003):


Select *
from 
(
select subtotal,customerid
from Sales.SalesOrderHeader
)
sOrderHeader
pivot
(
sum(subtotal) 
for customerid   in 
(
[1],
[2],
[3],
[4],
[5],
[6],
[7],
[9],
[10],
[11]
 )
) 
as pvt

If u observer this part


pivot
(
sum(subtotal) 
for customerid   in 
(
[1],
[2],
[3],
[4],
[5],
[6],
[7],
[9],
[10],
[11]
 )
)

Iam getting data for specific customer, sound like static to me.
Is there way to query for all the customer Id's.

Expecting somthing like this


Select *
from 
(
select subtotal,customerid
from Sales.SalesOrderHeader
)
sOrderHeader
pivot
(
sum(subtotal) 
for customerid   in 
(
select distinct customerid from Sales.SalesOrderHeader 
 )
) 
as pvt

Thanks in Advance,
Jack.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-28 : 23:02:02

It is static, just because its letting you create a crosstab doesn't mean it has to be dynamic. You have to create dynamic SQL and then execute it, the PIVOT statement does not support dynamic column lists itself.

You can get the list of columns by creating a variable and populating it like this

DECLARE @pivotColumns nvarchar(2000), @sql nvarchar(4000)
SET @pivotColumns = ''
SELECT @pivotColumns = @pivotColumns + '[' + cast(CustomerID AS nvarchar(10)) + '],'
FROM (SELECT distinct CustomerID FROM Sales.SalesOrderHeader) p
SET @pivotColumns = LEFT(@pivotColumns, LEN(@pivotColumns) - 1)
SET @sql = 'SELECT *
FROM
(SELECT SubTotal, CustomerID
FROM Sales.SalesOrderHeader) sOrderHeader
PIVOT
(
SUM(SubTotal)
FOR CustomerID IN
( ' + @pivotColumns + ' )
) AS pvt'
EXEC (@sql)

There is a very nice article describing this here

http://www.theabstractionpoint.com/dynamiccolumns.asp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources