Pivot a table with SQL

Tools
Enthusiast

Pivot a table with SQL

Does anyone have a good way to pivot a table when there is a variable amount of columns? I know that it can be done with a CASE statement, but that is tedious.
4 REPLIES
Enthusiast

Re: Pivot a table with SQL

It seems if I need to know the number of columns that I will need in these solutions. I would potentially need 100 columns convert from rows. Do you have any other thoughts?
Enthusiast

Re: Pivot a table with SQL

The only way to do what you are asking about is with dynamic SQL. If you wanted a pivot showing sales by department by year, you might not know how many departments or years there are. You would need a query to return all of your departments, and the iterate through that query to build sql strings for each line. Then you add each line to your query.

The real issue is how to handle the data once you build the query. If you are using something like .NET you can programatically output your data by looping through the columns or using AutoGenerateColumns = True. If you are using something like PL/SQL and Cursors, it is almost impossible.
Fan

Re: Pivot a table with SQL

I am looking for a way to pivot as well. Several threads point to this thread as having an answer. The forum also shows 8 replies to this thread but I only see 3?
Fan

Re: Pivot a table with SQL


i have similar question,
how you put rows to column

1 a
1 b
1 c
2 a
2 b
2 c

as
1 a b c
2 a b c

thansk you