Newbie: PIVOT

Hi,
SELECT * FROM SalesOrderTotalsYearly
PIVOT (SUM(SubTotal) FOR CustomerID IN ([1], [2], [4], [6])) as a
GO
The above is taken from the SQL Server 2005 T-SQL Enhancments document
used in the Microsoft Hands-On LAB.
My question is whether the IN ([1]...[6]) is always necessary?
For example if one has a large database and one wants to plot all
CustomerID's how would one go about it (I removed the IN part but got
an error!)?
Thank You
Regards,
ACB
Yes, you have to list the values which are to become columns. So you have to resort to dynamic SQL
if you don't know these values in advance. See http://aspfaq.com/show.asp?id=2462 for elaboration on
the topic.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"acb" <chribonn@.gmail.com> wrote in message
news:1137778700.663859.83660@.g44g2000cwa.googlegro ups.com...
> Hi,
> SELECT * FROM SalesOrderTotalsYearly
> PIVOT (SUM(SubTotal) FOR CustomerID IN ([1], [2], [4], [6])) as a
> GO
> The above is taken from the SQL Server 2005 T-SQL Enhancments document
> used in the Microsoft Hands-On LAB.
> My question is whether the IN ([1]...[6]) is always necessary?
> For example if one has a large database and one wants to plot all
> CustomerID's how would one go about it (I removed the IN part but got
> an error!)?
> Thank You
> Regards,
> ACB
>
|||2. Tibor Karaszi wrote:
> Yes, you have to list the values which are to become columns. So you have to resort to dynamic SQL
> if you don't know these values in advance. See http://aspfaq.com/show.asp?id=2462 for elaboration on
> the topic.
Thank You