How to transpose rows to columns

Question

How to transpose rows to columns

sushil.mchaudhari

Grasshopper

Points: 14
More actions
July 3, 2010 at 6:50 am

#226416

I have created table . I do monitoring of server everyday.
so I have to store all data in table.
But............. I have to create report. How I transpose rows to columns while fetching out record from table???
My table is ....
Parameter Instuction Unit_of_report Expected_value
1 2 e 5
2 4 g h
I want this table in following format...
Parameter 1 2
Instruction 2 4
Unit_of_report e g
Expectted_value 5 h
Please helpp meeeeee..........

Viewing 3 posts - 1 through 2 (of 2 total)

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

sushil.mchaudhari Grasshopper Points: 14 More actions · Answer 1

sushil.mchaudhari (7/3/2010)
I have created table . I do monitoring of server everyday.
so I have to store all data in table.
But............. I have to create report. How I transpose rows to columns while fetching out record from table???
My table is ....
Parameter | Instuction | Unit_of_report | Expected_value
1 | 2 | e | 5
2 | 4 | g | h
I want this table in following format...
Parameter 1 2
Instruction 2 4
Unit_of_report e g
Expectted_value 5 h
Please helpp meeeeee..........

LutzM SSC Guru Points: 107049 More actions · Answer 2

Step one: Define a column to be used as a row/column identifer (either column Parameter or a new column using ROW_NUMBER())

Step two: UNPIVOT the data to get a table with your ID, description_name (e.g. "Instruction") and related description_value (e.g. 2).

Step three: build the final table (including column names, other than shown in your expected output) by either using PIVOT or CrossTab.

A description of ROW_NUMBER(), UNPIVOT and PIVOT can be found in BooksOnLine, the SQL Server help system usually installed together with SQL Server. The CrossTab concept is described in the related article referenced in my signature.

See how far the hints will get you and post back if you get stuck.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]