Transpose cloumns into rows and rows into columns

Question

Post reply

Transpose cloumns into rows and rows into columns

malleswarareddy_m

SSCertifiable

Points: 5847
More actions
February 15, 2010 at 8:19 am

#138100

my result set is like this.But i want to pivot the result.
I used both the cobination of pivot and unpivot but i got the result but is not worked for me.
stateOctoberNovemberDecemberJanuaryFebuary
state17211631 1035 821752
state213212015 1997 101 515
state313091360 908 736665
state42721716 1847 1804897
state5130366 394 452240
state62312992 2468 25531693
state76121637 1419 941339
state81023941 1772 1641902
for the above result set Actually i want the result set like this where there is transpose function in sql server.
state state1state2state3state4state5state6state7state8
October 721132113092721302316121023
November 163120151360171636629921637941
December 103519979081847394246814191772
January 8211013736180445225539411641
Febuary 7525156658972401693339902
Malleswarareddy
I.T.Analyst
MCITP(70-451)

Viewing 8 posts - 1 through 7 (of 7 total)

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

WayneS SSC Guru Points: 95460 More actions · Answer 1

You might want to read these articles:

http://www.sqlservercentral.com/articles/T-SQL/63681/

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

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

LutzM SSC Guru Points: 107049 More actions · Answer 2

I'm not aware of any transpose function in SQL Server.

So you'd need to UNPIVOT your data to get three columns (state, month and value). Then use one of the links Wayne pointed you at to get the data pivoted again in a transposed for.

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]

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 3

Hi i got my result .but i did not got my result directly with combination of pivot and unpivot result.first i unpivot the result into temp

then pivot the temp table.

finally got succeeded.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

Paul White SSC Guru Points: 150467 More actions · Answer 4

USE tempdb;

DECLARE @data

TABLE (

state CHAR(6) NOT NULL,

october INT NOT NULL,

november INT NOT NULL,

december INT NOT NULL,

january INT NOT NULL,

february INT NOT NULL

);

INSERT @data

VALUES ('state1', 0721, 1631, 1035, 0821, 0752),

('state2', 1321, 2015, 1997, 0101, 0515),

('state3', 1309, 1360, 0908, 0736, 0665),

('state4', 0272, 1716, 1847, 1804, 0897),

('state5', 0130, 0366, 0394, 0452, 0240),

('state6', 0231, 2992, 2468, 2553, 1693),

('state7', 0612, 1637, 1419, 0941, 0339),

('state8', 1023, 0941, 1772, 1641, 0902);

SELECT P.month AS state,

P.state1,

P.state2,

P.state3,

P.state4,

P.state5,

P.state6,

P.state7,

P.state8

FROM @data D

UNPIVOT (value FOR month IN (october, november, december, january, february)) U

PIVOT (MAX(U.value) FOR U.state IN (state1, state2, state3, state4, state5, state6, state7, state8)) P

ORDER BY

CASE P.month

WHEN 'october' THEN 1

WHEN 'november' THEN 2

WHEN 'december' THEN 3

WHEN 'january' THEN 4

WHEN 'february' THEN 5

ELSE NULL

END;

For a general Transpose function (implemented as a CLR procedure) see http://www.sqlmag.com/articles/index.cfm?articleid=102631. A subscription is required.

Paul

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

malleswarareddy_m SSCertifiable Points: 5847 More actions · Answer 5

Hi pual ,

i got suceeded with pivot and unpivot.But i First transpose this to unpivot then pivot the table.I take two steps for this.

But you have done this in single steps.thanks for reply.

your code is pretty ,simple and excellent.

Malleswarareddy
I.T.Analyst
MCITP(70-451)

Paul White SSC Guru Points: 150467 More actions · Answer 6

Thanks very much!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Stun-Dexim SSC Journeyman Points: 77 More actions · Answer 7

http://sqlmag.com/t-sql/how-sql-server-clr-and-c-can-solve-common-problem