HOW TO: PIVOT for unknown number of column headings?

15 Sep 2006 3:28 PM

Edmund

Hello,

In SQL Server 2005 there is the PIVOT command that you can apply to your
SELECT statement to pivot data as you do in Excel. However, unlike Excel,
in SQL you need to know the number and names of the column headings in order
to pivot. Is it possible to dynamically pivot a result set in SQL? Is
there a syntax or short-cut where you can select the column to pivot on and
the column to group on (like a 2 column CUBE statement) and get the pivot
table out; instead of naming each pivot column value in the SELECT
statement?

Thanks,

Ed

15 Sep 2006 3:46 PM

Mike C#

Dynamic SQL. See http://www.sqlteam.com/item.asp?ItemID=2955

Show quoteHide quote

"Edmund" <die_spambot@hatespam.com> wrote in message
news:OZGpmuN2GHA.2176@TK2MSFTNGP04.phx.gbl...
> Hello,
>
> In SQL Server 2005 there is the PIVOT command that you can apply to your
> SELECT statement to pivot data as you do in Excel. However, unlike Excel,
> in SQL you need to know the number and names of the column headings in
> order to pivot. Is it possible to dynamically pivot a result set in SQL?
> Is there a syntax or short-cut where you can select the column to pivot on
> and the column to group on (like a 2 column CUBE statement) and get the
> pivot table out; instead of naming each pivot column value in the SELECT
> statement?
>
> Thanks,
>
> Ed
>

15 Sep 2006 3:46 PM

Anith Sen

You will have to use a client side application or a support tool like RAC to
do this reliably.

Within the server, the available options are kludgy: Either hardcode all the
possible column values as names and use a series of CASE expressions or use
Dynamic SQL to build the SQL string ( mostly involving convoluted procedural
code ) and use EXEC() or sp_ExecuteSQL to generate the cross tabulation.

--
Anith