Pivot Query Problem

Question

Post reply

Pivot Query Problem

akshay.tankhiwale

Grasshopper

Points: 20
More actions
October 4, 2010 at 11:16 pm

#239275

hi everybody,
i have a table with following data and i want to generate the output as follow usning pivot or any other way please help me out..
Antibiotics Organism value ReportId Opd_Ipd_Id SortOrder
A1 O1 INTERMEDIATE 66942 50424 4
A2 O2 INTERMEDIATE 66942 50424 7
A3 O1 RESISTANT 66942 50424 5
A4 O2 RESISTANT 66942 50424 8
A5 O1 SENSITIVE 66942 50424 3
A6 O2 SENSITIVE 66942 50424 6
A7 O1 INTERMEDIATE 66942 50424 5
A8 O2 RESISTANT 66942 50424 3
A9 O2 SENSITIVE 66942 50424 4
Output Reqired are as follow:
Group by
Oraganism- O1
INTERMEDIATE RESISTANT SENSITIVE
A1 A4 A7
A7 - -
Oraganism- O2
INTERMEDIATE RESISTANT SENSITIVE
A2 A3 A6
- - A9
How can achive this output using pivot or any other way in sql server.

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

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

removed (duplicate /unfinished post)

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]

LutzM SSC Guru Points: 107049 More actions · Answer 2

Please provide ready to use sample data as described in the first link in my signature.

Side note: Your required output is not a table anymore... Formatting like you require should be done at the front end layer (maybe even including the pivoting).

What you could get out of SQL Server without any major work is a table structure like the following:

Oraganism Block INTERMEDIATE RESISTANT SENSITIVE

O1 1 A1 A4 A7

O1 2 A7 - -

O2 1 A2 A3 A6

O2 1 - - A9

If you want to give it a try by yourself, look into ROW_NUMBER() to get the Block number and also have a look at the CrossTab article referenced in my signature.

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]

Mark Cowne One Orange Chip Points: 26984 More actions · Answer 3

WITH CTE AS (

SELECT Antibiotics,Organism, value,

ROW_NUMBER() OVER(PARTITION BY Organism,value ORDER BY SortOrder) AS rn

FROM MyTable)

SELECT Organism,

MAX(CASE WHEN value='INTERMEDIATE' THEN Antibiotics END) AS INTERMEDIATE,

MAX(CASE WHEN value='RESISTANT' THEN Antibiotics END) AS RESISTANT,

MAX(CASE WHEN value='SENSITIVE' THEN Antibiotics END) AS SENSITIVE

FROM CTE

--WHERE Organism='O1'

GROUP BY Organism,rn

ORDER BY Organism,rn;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

akshay.tankhiwale Grasshopper Points: 20 More actions · Answer 4

Thanks LutzM and Mark-101232 for your help.

LutzM here onwards I will take care all the Forum Etiquette to post the problem.

thanks once again...........