Transpose Columns to Rows - SQL 2000

Question

Post reply

Transpose Columns to Rows - SQL 2000

Mitesh Chauhan

Old Hand

Points: 371
More actions
October 6, 2009 at 4:19 am

#100681

Hi
Here is my table
Field1--|--Field2-----|--Field3---|-Field4
A---------06-09-09-----100.00------5
B---------05-09-09-----200.00------6
I would like to display the data as follows
--------------A----------------B
Field2------06-09-09--------05-06-09
Field3------100.00-----------200.00
Field4--------5--------------6
Any ideas?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Take a look at the article in my signature by Jeff Moden on Cross Tabs. Post back if you have questions.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Mitesh Chauhan Old Hand Points: 371 More actions · Answer 2

I have read the original article about Cross Tabs on BOL.

My requirement is different. In the article it uses static statments to insert records. In my case no. of rows will be dynamic.

Garadin One Orange Chip Points: 29613 More actions · Answer 3

Well, in that case, you need part 2 of the article ;-).

http://www.sqlservercentral.com/articles/Crosstab/65048/

That Jeff, always thinkin ahead.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Mitesh Chauhan Old Hand Points: 371 More actions · Answer 4

Well thanks everyone.

I have managed to solve it mysef.

Mitesh Chauhan Old Hand Points: 371 More actions · Answer 5

Here is how I managed to get it working. If it helps anyone, it would be great.

My problem was I needed the values of Field1 to become the Column Headings and rest of the columns as row headings and numbers of rows will vary every time but will never be more then 10 rows.

1) Created a temp table

Create Table #TempTab

(Col_Heading varchar(50) NULL,

Col_Description varchar(50) NULL)

1) I dumped all the data that i needed into another temp table(#NewTempTab).

2) Looped through #TempTab and added the value of field 1 as Column in #TempTab

set @sql = 'Alter Table #TempTab Add '

set @sql = @sql + rtrim(VALUE of FIELD1) + ' varchar(30) NULL'

exec(@SQL)

So Now my #TempTab will look as follows

Col_Heading , Col_Description , VALUE1 of FIELD1, VALUE1 of FIELD1..

3) Looped through #NewTempTab inserted the records

set @sql = 'Insert Into #TempTab(Col_Heading,Col_Description, Order_id, ' + rtrim(VALUE1 of FIELD1) + ')'

set @NewSQL = @sql + 'values(''Field2'', ''Field 2 Description''' + ',' + ',''' + rtrim(VALUE1 of FIELD1) + ''')'

exec(@NewSQL)

So now my #TempTab will look like

Col_Heading , Col_Description , A, -----------B

Field2, Field 2 Description-----06-09-09--------05-06-09

Field3, Field 3 Description-----100.00-----------200.00

Field4, Field 4 Description-------5--------------6

I hope it makes sense as the stored procedure has lots of code. But this just the jist of the solution.