Pivot table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Pivot table

Author

Topic

Zifter
Yak Posting Veteran

51 Posts

Posted - 2012-01-23 : 11:12:39

I have the following query

select distinct [e].Name as [EventName], 
       STUFF
       (
        (
         select distinct ', ' + [p].EN 
         from Params as [p]
         inner join defEventSeriousnesses  as [es]
           on [p].FK_APrmID = 4
           and [p].AIdxID = [es].FK_AIdx_ID
         where [es].FK_Event_ID = [e].ID
         for xml path('')
        ),1,2,''
       ) as [Seriousnesses]
from defEvent as [e]
inner join defICSRDrugEvents as [ide]
  on [ide].FK_Event_ID = e.ID
inner join defICSRDrugs as [id]
  on [id].ID = [ide].FK_ICSRDrug_ID
inner join defEventSeriousnesses as [es]
  on [es].FK_Event_ID = [e].ID
where [id].FK_ICSR_ID = 14

which gives me the following result

EventName                                          Seriousnesses
-------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Event 1                                            Death, Life Threatening
Event 2                                            (Prolonged) Hospitalization, Life Threatening, Persistent or significant disability / incapacity

I would like to have the following output

Column 1                                           Column2
-------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Event 1                                            Event 2
Death, Life Threatening                            (Prolonged) Hospitalization, Life Threatening, Persistent or significant disability / incapacity

I know I have to use a pivot table, but whatever I try, it's never what I need. Can someone please point me to the right direction?
Thanks in advance!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-23 : 14:03:25

Because I don't have your table definitions and data, I can't test it, but would something like this work for you?

;WITH cte AS
(
--------- YOUR ORIGINAL QUERY------------
select distinct [e].Name as [EventName], 
       STUFF
       (
        (
         select distinct ', ' + [p].EN 
         from Params as [p]
         inner join defEventSeriousnesses  as [es]
           on [p].FK_APrmID = 4
           and [p].AIdxID = [es].FK_AIdx_ID
         where [es].FK_Event_ID = [e].ID
         for xml path('')
        ),1,2,''
       ) as [Seriousnesses]
from defEvent as [e]
inner join defICSRDrugEvents as [ide]
  on [ide].FK_Event_ID = e.ID
inner join defICSRDrugs as [id]
  on [id].ID = [ide].FK_ICSRDrug_ID
inner join defEventSeriousnesses as [es]
  on [es].FK_Event_ID = [e].ID
where [id].FK_ICSR_ID = 14
--------------------------------------------
)	
SELECT
	[1],[2]
FROM
(	SELECT [EventName],ROW_NUMBER() OVER (ORDER BY [EventName]) AS RN FROM cte ) S
PIVOT
(MAX([EventName]) FOR rn IN ([1],[2]))P

UNION ALL

SELECT
	[1],[2]
FROM
(	SELECT [Seriousnesses],ROW_NUMBER() OVER (ORDER BY [Seriousnesses]) AS RN FROM cte ) S
PIVOT
(MAX([Seriousnesses]) FOR rn IN ([1],[2]))P

Zifter
Yak Posting Veteran

51 Posts

Posted - 2012-01-24 : 03:08:39

Thank you sunitabeck.
I know I didn't provide the table definitions. Didn't think they would be of much help for this question. If needed I'll post them.

The solution you gave me is very close to what I would like, but the data got mixed. The "seriousness" of "Event 1" is under "Event 2" and vice versa...

I still don't really understand how this pivotting works.

Zifter
Yak Posting Veteran

51 Posts

Posted - 2012-01-24 : 03:41:30

If I change the order by of the last select, I have what I needed.
Thanks again!

;WITH cte AS
(
--------- YOUR ORIGINAL QUERY------------
select distinct [e].Name as [EventName], 
       STUFF
       (
        (
         select distinct ', ' + [p].EN 
         from Params as [p]
         inner join defEventSeriousnesses  as [es]
           on [p].FK_APrmID = 4
           and [p].AIdxID = [es].FK_AIdx_ID
         where [es].FK_Event_ID = [e].ID
         for xml path('')
        ),1,2,''
       ) as [Seriousnesses]
from defEvent as [e]
inner join defICSRDrugEvents as [ide]
  on [ide].FK_Event_ID = e.ID
inner join defICSRDrugs as [id]
  on [id].ID = [ide].FK_ICSRDrug_ID
inner join defEventSeriousnesses as [es]
  on [es].FK_Event_ID = [e].ID
where [id].FK_ICSR_ID = 14
--------------------------------------------
)	
SELECT
	[1],[2]
FROM
(	SELECT [EventName],ROW_NUMBER() OVER (ORDER BY [EventName]) AS RN FROM cte ) S
PIVOT
(MAX([EventName]) FOR rn IN ([1],[2]))P

UNION ALL

SELECT
	[1],[2]
FROM
(	SELECT [Seriousnesses],ROW_NUMBER() OVER (ORDER BY [EventName]) AS RN FROM cte ) S
PIVOT
(MAX([Seriousnesses]) FOR rn IN ([1],[2]))P

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-24 : 08:19:24

My mistake - copy-paste error on my part. Glad you got it working.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources