All Forums Tools
c9jad 6 posts Joined 08/04
12 Apr 2005
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.

c9jad 6 posts Joined 08/04
27 Apr 2006

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?

Nytewynd 13 posts Joined 12/05
28 Apr 2006

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.

Siinis 3 posts Joined 05/08
19 Jun 2008

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?

Kunny 2 posts Joined 08/08
07 Aug 2008

i have similar question, how you put rows to column 1 a 1 b 1 c2 a 2 b 2 cas 1 a b c 2 a b cthansk you

You must sign in to leave a comment.