Rotate table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Rotate table

Author

Topic

lane0618
Posting Yak Master

134 Posts

Posted - 2002-09-12 : 18:46:08

I have done a lot of research regarding pivot tables but I can't seem to figure out a simple way to just rotate the table switching rows for columns. I don't need to sum or anyrhing, just rotate. Is there a way to do this?

Thanks,
Lane

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-09-12 : 19:09:57

not any easy way that's for sure ... you can do it through ASP though or if you can get your data into an two-dimensional array... loop through rows for each columns rather than columns for each row... so instead of (assuming DATA[x,y] where x is the column and y is the row


for (int y=0; y<DATA.GetLength(1); y++) {
 for (int x=0; x<DATA.GetLength(0); x++) {
  // Do something...
 }
}

/* Assuming output would be like:
 y=0:x=1,x=2,x=n,...
 y=1:x=1,x=2,x=n,...

      | Column1 | ColumnN |
 -----+---------+---------+
 Row1 | Value1  | ValueN  |
 RowN | Value1  | ValueN  |
*/

try this instead:


for (int x=0; x<DATA.GetLength(0); x++) {
 for (int y=0; y<DATA.GetLength(1); y++) {
  // Do something...
 }
}

/* Assuming output would be like:
 x=0:y=1,y=2,y=n,...
 x=2:y=1,y=2,y=n,...

           Row1   | Row2   | RowN   |
 --------+--------+--------+--------+
 Column1 | Value1 | Value2 | ValueN |
 Column2 | Value1 | Value2 | ValueN |
*/

That's C# code by the way and should allow you to pivot a table completely... um wow I must be bored...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-12 : 19:15:50

You're looking for a cross-tab:

http://www.sqlteam.com/SearchResults.asp?SearchTerms=cross+tab

It's another term for pivot table. If you only have one value for each row and column combination, you can use Min or Max as the summary function. The CASE statement is well suited for generating cross-tabs (that's what my procedure uses, it does it dynamically) You can find more info on cross-tabs in Books Online too. If you don't have a lot of pivot columns, or have a fixed number, you can write it with fixed CASE statements and get better performance than using the dynamic procedure.

lane0618
Posting Yak Master

134 Posts

Posted - 2002-09-12 : 19:39:43

Thanks. What would I fill in for the four parameters for the cross tab sp? My table looks like this:

id | abc | def |
----------------
1 | 0 | 1 |
2 | 1 | 0 |
3 | 0 | 1 |

I want it to look like:

____| 1 | 2 | 3 |
----------------
abc | 0 | 1 | 0 |
def | 1 | 0 | 1 |

Thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-09-12 : 19:43:22

If that is what I think it is, you're talking about transposition, not a true cross-tab, and depending on how many ROWS of data you have, you won't be able to do it.

Is transposition what you're looking for? If so, then Onamuji has the right idea, and you need to do this in a 2D array in a programming language outside of SQL.

lane0618
Posting Yak Master

134 Posts

Posted - 2002-09-13 : 14:22:00

Yes, I'm definately trying to do transpositions so that I can search the columns names as if they were rows. Any idea how this can be done in ASP?

thanks,
Lane

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-09-13 : 15:47:28

use my solution but use the GetRows function ...


Dim data: data = RecordSet.GetRows()
Dim colLength: colLength = UBound(data, 1)
Dim rowLength: rowLength = UBound(data, 2)
Dim col, row

For col = 0 To colLength
 Response.Write RecordSet.Fields(col).Name & " | "
 For row = 0 To rowLength
  Response.Write data(col, row) & " | "
 Next
Next

try that

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources