PIVOT Question

Question

Post reply

PIVOT Question

Kwisatz78

SSChampion

Points: 12478
More actions
December 5, 2011 at 5:50 am

#391030

Hi there - I currently have the following select
SELECT TM.DESCRION AS Team,
CASE TMM.MBR_SEQ_NUM
WHEN 1 THEN 'Primary'
WHEN 2 THEN 'Secondary'
WHEN 3 THEN 'Tertiary'
ELSE 'Other'
END AS Rotation,
RE.TARGET_NAME AS Person
FROM ALARMPOINT.RECIPIENTS RE JOIN ALARMPOINT.TM_MBRSHIPS TMM
ON RE.RECIPIENT_ID = TMM.RECIPIENT_ID
JOIN ALARMPOINT.TM TM ON TMM.TM_ID = TM.TM_ID
WHERE TMM.TM_ID IN (200003,200005)
Which returns the data below:
Systems Team ApplicationsPrimarymathurp
Systems Team ApplicationsSecondaryjulie
Systems Team ApplicationsTertiaryrheat
Systems Team InfrastructurePrimarybamforthc
Systems Team InfrastructureTertiarymoorhousej
Systems Team InfrastructureSecondarywoolleyr
I am wanting to pivot this so that the headings are the values Primary, Secondary , Tertiary and the table values are the peoples name. With the Team name as a row heading.
I have been trying with something like the following but I haven't been able to get it to work, any help s appreciated.
SELECT TM.DESCRION AS Team, [1] AS [Primary], [2] AS Secondary, [3] AS Tertiary
FROM (
SELECT TM.DESCRION, TMM.MBR_SEQ_NUM AS Rotation, RE.TARGET_NAME AS Person
FROM ALARMPOINT.RECIPIENTS RE JOIN ALARMPOINT.TM_MBRSHIPS TMM
ON RE.RECIPIENT_ID = TMM.RECIPIENT_ID
JOIN ALARMPOINT.TM TM ON TMM.TM_ID = TM.TM_ID
WHERE TMM.TM_ID IN (200003,200005)
) AS SourceTable
PIVOT
(
count(Person)
FOR Rotation IN ([1], [2], [3])
) AS PivotTable

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

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Can you please post some sample data and the expected output?

That would make things a lot easier for us.

-- Gianluca Sartori

Kwisatz78 SSChampion Points: 12478 More actions · Answer 2

I did include the data, but perhaps not very clear - try this with the headers.

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

Wit sample data I mean something readily consumable, like this:

SELECT *

FROM (

SELECT 'Systems Team Applications', 'Primary', 'mathurp'

UNION ALL SELECT 'Systems Team Applications', 'Secondary', 'julie'

UNION ALL SELECT 'Systems Team Applications', 'Tertiary', 'rheat'

UNION ALL SELECT 'Systems Team Infrastructure', 'Primary', 'bamforthc'

UNION ALL SELECT 'Systems Team Infrastructure', 'Tertiary', 'moorhousej'

UNION ALL SELECT 'Systems Team Infrastructure', 'Secondary', 'woolleyr'

) AS SampleData (Team, Rotation, Person)

What's the expected output?

-- Gianluca Sartori

Kwisatz78 SSChampion Points: 12478 More actions · Answer 4

Apologies - The output should look something like this

SELECT *

FROM(

SELECT 'Systems Team Applications', 'mathurp', 'grayj', 'rheat'

UNION ALL SELECT 'Systems Team Applications', 'bamforthc', 'woolleyr', 'morrhousej'

)AS ExpectedOutput ([Team], [Primary], [Secondary], [Tertiary])

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

Guess based on Gianluca's interpretation of your data: -

SELECT pvtData.DESCRION AS Team, pvtData.[Primary], pvtData.[Secondary],

pvtData.[Tertiary], pvtData.[Other]

FROM (SELECT DESCRION, CASE WHEN MBR_SEQ_NUM = 1

THEN 'Primary'

WHEN MBR_SEQ_NUM = 2

THEN 'Secondary'

WHEN MBR_SEQ_NUM = 3

THEN 'Tertiary' ELSE 'Other' END AS Rotation,

TARGET_NAME

FROM (SELECT 'Systems Team Applications', 1, 'mathurp'

UNION ALL SELECT 'Systems Team Applications', 2, 'julie'

UNION ALL SELECT 'Systems Team Applications', 3, 'rheat'

UNION ALL SELECT 'Systems Team Infrastructure', 1, 'bamforthc'

UNION ALL SELECT 'Systems Team Infrastructure', 3, 'moorhousej'

UNION ALL SELECT 'Systems Team Infrastructure', 2, 'woolleyr') sd (DESCRION, MBR_SEQ_NUM, TARGET_NAME) ) srcData

PIVOT(MAX(TARGET_NAME) FOR Rotation IN ([Primary], [Secondary], [Tertiary], [Other])) pvtData

--EDIT--

So you'd be looking at something like this for your actual query (untested)

SELECT pvtData.DESCRION AS Team, pvtData.[Primary], pvtData.[Secondary],

pvtData.[Tertiary], pvtData.[Other]

FROM (SELECT TM.DESCRION, CASE TMM.MBR_SEQ_NUM WHEN 1

THEN 'Primary'

WHEN 2

THEN 'Secondary'

WHEN 3

THEN 'Tertiary'

ELSE 'Other' END AS Rotation,

RE.TARGET_NAME

FROM ALARMPOINT.RECIPIENTS RE

JOIN ALARMPOINT.TM_MBRSHIPS TMM ON RE.RECIPIENT_ID = TMM.RECIPIENT_ID

JOIN ALARMPOINT.TM TM ON TMM.TM_ID = TM.TM_ID

WHERE TMM.TM_ID IN (200003,200005) ) srcData

PIVOT(MAX(TARGET_NAME) FOR Rotation IN ([Primary], [Secondary], [Tertiary], [Other])) pvtData

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 6

A simple crosstab should work. I'm typing this freehand, so apologies in advance for any errors.

select team

,max(case when rotation = 'Primary' then person else null end) as Primary

,max(case when rotation = 'Secondary' then person else null end) as Secondary

,max(case when rotation = 'Tertiary' then person else null end) as Tertiary

from SampleData

group by team

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Kwisatz78 SSChampion Points: 12478 More actions · Answer 7

Sorry for the slow response with this, thanks for the help I will work through the code.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 8

You're welcome. Let us know if you have any questions.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills