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,
ACBYes, 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 /url] for elaboration on
the topic.
Tibor Karaszi, SQL Server MVP
[url]http://www.karaszi.com/sqlserver/default.asp" target="_blank">http://aspfaq.com/show.asp?id=2462[...ver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"acb" <chribonn@.gmail.com> wrote in message
news:1137778700.663859.83660@.g44g2000cwa.googlegroups.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 2" target="_blank">http://aspfaq.com/show.asp?id=246
2 for elaboration on
> the topic.
Thank You