rsstech
asked on
Convert Columns to Rows
I am using MS SqlServer 2000. I have a table that has data like this...
Size #STMT UNQ REV COMP TAX
----------- ----------- ----------- ------------ ----------- ----------
1 2 NULL NULL 1 1
2 22 1 2 4 1
3 45 13 7 5 3
4 65 23 2 2 NULL
5 19 11 NULL 1 NULL
6 35 25 NULL NULL NULL
7 88 71 1 NULL NULL
8 276 144 12 13 5
I need this data to come out like this....
1 2 3 4 5 6 7 8
-------- -------- -------- ------- ------ ------ ------ ------
#STMT 2 22 45 65 19 35 88 276
UNQ null 1 13 23 11 25 71 144
REV null 2 7 2 null null 1 12
COMP 1 4 5 2 1 null null 13
TAX 1 1 3 null null null null 5
This table actually has about 25 columns in it that need converting. One across the top and the rest down the left side. There are 16 tables like this in our database. I do not need any aggregation functions in the end result.
Could someone help me convert this data.
Thanks
Jim
Size #STMT UNQ REV COMP TAX
----------- ----------- ----------- ------------ ----------- ----------
1 2 NULL NULL 1 1
2 22 1 2 4 1
3 45 13 7 5 3
4 65 23 2 2 NULL
5 19 11 NULL 1 NULL
6 35 25 NULL NULL NULL
7 88 71 1 NULL NULL
8 276 144 12 13 5
I need this data to come out like this....
1 2 3 4 5 6 7 8
-------- -------- -------- ------- ------ ------ ------ ------
#STMT 2 22 45 65 19 35 88 276
UNQ null 1 13 23 11 25 71 144
REV null 2 7 2 null null 1 12
COMP 1 4 5 2 1 null null 13
TAX 1 1 3 null null null null 5
This table actually has about 25 columns in it that need converting. One across the top and the rest down the left side. There are 16 tables like this in our database. I do not need any aggregation functions in the end result.
Could someone help me convert this data.
Thanks
Jim
What would you do if a particular size row appeared more than once?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Geeez what a scrub...good one scott.....
Give him the points (not that he needs them)
Give him the points (not that he needs them)
ASKER
Scott adn x002548
Thanks for the solutions. However, I may not have been clear on what I need to do. Let me try to explain and see if that makes any differences in your answers.
I have one row of data
<column names> a b c d
<data in row> jim 14 22 34
<data in next row? bob 32 18 15
Thanks for the solutions. However, I may not have been clear on what I need to do. Let me try to explain and see if that makes any differences in your answers.
I have one row of data
<column names> a b c d
<data in row> jim 14 22 34
<data in next row? bob 32 18 15
ASKER
Sorry - the enter key moved me to finish before I was through....
What I need to do is that the data in column A and make that data the column headings.
Then I need to take the column headings b,c,& d and make them the row headings.
Then I need to put the data in the columns and rows as follows....
Jim Bob
b 14 32
c 22 18
d 34 15
Basically this changes on column of data into column headings and then pivots the results set to report vertically instead of horizontially.
Thanks
jim
What I need to do is that the data in column A and make that data the column headings.
Then I need to take the column headings b,c,& d and make them the row headings.
Then I need to put the data in the columns and rows as follows....
Jim Bob
b 14 32
c 22 18
d 34 15
Basically this changes on column of data into column headings and then pivots the results set to report vertically instead of horizontially.
Thanks
jim
If you cut and paste my query that's what you get
RowDetail 1 2 3 4 5 6 7 8
--------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
[#STMT] 2 22 45 65 19 35 88 276
UNQ NULL 1 13 23 11 25 71 144
REV NULL NULL NULL NULL NULL NULL NULL NULL
COMP NULL NULL NULL NULL NULL NULL NULL NULL
TAX NULL NULL NULL NULL NULL NULL NULL NULL
(5 row(s) affected)
I just didn't finish
RowDetail 1 2 3 4 5 6 7 8
--------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
[#STMT] 2 22 45 65 19 35 88 276
UNQ NULL 1 13 23 11 25 71 144
REV NULL NULL NULL NULL NULL NULL NULL NULL
COMP NULL NULL NULL NULL NULL NULL NULL NULL
TAX NULL NULL NULL NULL NULL NULL NULL NULL
(5 row(s) affected)
I just didn't finish
I thought that's what I gave you based on the sample data in your initial post.
Did you end up with the result set you needed? Was there still something not right?