How to transpose the ROWs

Question

Post reply

How to transpose the ROWs

aks_pra

Mr or Mrs. 500

Points: 591
More actions
December 26, 2008 at 4:22 am

#73165

Hi ,
I want to How to transpose the ROWs from table.
eg.
Exiting format
ID Status Count
0001 XX 10
0004 YY 30
0005 YX 40
i want in below format
ID XX YY YX
0001 10 NULL NULL
0004 NULL 30 NULL
0005 NULL NULL 40
and also i want the MAX value of the ID
(ROW wise MAX value)
Thanks and Regards,
Pravin V. Kadam

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

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

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 1

Read about Cross-tab reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

Dugi SSCoach Points: 17998 More actions · Answer 2

try this:

CREATE TABLE #PAPI

(

ID CHAR(4),

STATUS CHAR(2),

CNT INT

)

INSERT INTO #PAPI

SELECT '0001', 'XX', 10

UNION ALL

SELECT '0004', 'YY', 30

UNION ALL

SELECT '0005', 'XY', 40

SELECT * FROM #PAPI

SELECT ID,

MAX(CASE [STATUS] WHEN 'XX' THEN cnt ELSE null END) AS XX,

MAX(CASE [STATUS] WHEN 'YY' THEN cnt ELSE null END) AS YY,

MAX(CASE [STATUS] WHEN 'XY' THEN cnt ELSE null END) AS XY

FROM #PAPI

GROUP BY ID

Hope it works to you ...if you have any problem be more specific and post the structure of the table then some sample data again!

Dugi

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 3

Praveen,

If you want to learn how to make cross tabs, then what Madhivanan suggested is a good idea. Also, I wrote a kind-of tutorial on how to make a crosstab at the following URL...

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

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

aks_pra Mr or Mrs. 500 Points: 591 More actions · Answer 4

Thanks you very much Jeff !!

It's really helpful article

Thanks and Regards,

Pravin V. Kadam

Jeff Moden SSC Guru Points: 1004412 More actions · Answer 5

Thanks Pravin... I always appreciate the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)