Not sure why PIVOT returns a single row?

Question

Post reply

Not sure why PIVOT returns a single row?

jabberpunch

Valued Member

Points: 54
More actions
November 27, 2012 at 8:33 am

#265257

I'm new to using the PIVOT operator and I want to PIVOT the following CTE result set:
RowIDRegionFactor
=================
1CapitolText1
2CapitolText2
3CapitolText3
1CentralText4
2CentralText5
3CentralText6
1North Text7
2North Text8
3North Text9
To look like this:
RowID Capitol Central North
===== ===== ===== ====
1 Text1 Text4 Text7
2 Text2 Text5 Text8
3 Text3 Text6 Text9
I tried using the following:
SELECT * FROM
(SELECT [Factor], [Region]
FROM CTE
WHERE RowID <= 3)
PIVOT
(
MAX([Factor]) FOR [Region] IN ([Capitol], [Central], [North])
)AS pvt
but only the first row gets returned:
Capitol Central North
===== ===== ====
Text1 Text4 Text7
Where am I missing the mark here? Thanks

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

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

s_osborne2 SSCertifiable Points: 6726 More actions · Answer 1

Hi,

It's because you've not selected the RowID... See below.

DECLARE @cte TABLE

(

RowID INT,

Region VARCHAR(50),

Factor VARCHAR(50)

)

INSERT INTO @cte

SELECT 1, 'Capitol', 'Text1'

UNION ALL SELECT 2,'Capitol', 'Text2'

UNION ALL SELECT 3,'Capitol', 'Text3'

UNION ALL SELECT 1,'Central', 'Text4'

UNION ALL SELECT 2,'Central', 'Text5'

UNION ALL SELECT 3,'Central', 'Text6'

UNION ALL SELECT 1,'North','Text7'

UNION ALL SELECT 2,'North' ,'Text8'

UNION ALL SELECT 3,'North' ,'Text9'

SELECT

*

FROM

(

SELECT [RowID], [Factor], [Region]

FROM @cte

WHERE RowID <= 3

) as X PIVOT

(

MAX([Factor]) FOR [Region] IN ([Capitol], [Central], [North])

)AS pvt

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne

Lowell SSC Guru Points: 323504 More actions · Answer 2

fixed a syntax issue, and create the DDL for the CTE;

i got far enough to duplicate the one row issue before i ran out of gas...building the CTE took the wind out of my sails.

With CTE ([RowID],[Region], [Factor])

AS

(

SELECT '1','Capitol',' Text1' UNION ALL

SELECT '2','Capitol',' Text2' UNION ALL

SELECT '3','Capitol',' Text3' UNION ALL

SELECT '1','Central',' Text4' UNION ALL

SELECT '2','Central',' Text5' UNION ALL

SELECT '3','Central',' Text6' UNION ALL

SELECT '1','North' ,'Text7' UNION ALL

SELECT '2','North' ,'Text8' UNION ALL

SELECT '3','North' ,'Text9'

)

SELECT * FROM

(SELECT [Factor], [Region]

FROM CTE

WHERE RowID <= 3)

AS TheSource

PIVOT

(

MAX([Factor]) FOR [Region] IN ([Capitol], [Central], [North])

)AS pvt

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jabberpunch Valued Member Points: 54 More actions · Answer 3

That's exactly what was wrong, once I add in the [RowID] column I get exactly the result set I want. Thanks for the quick catch on that!

s_osborne2 SSCertifiable Points: 6726 More actions · Answer 4

No problem. If you want to make our lives easier to help you in the future you should create a script something like Lowell and I did. That way more people will attempt to solve your problem rather than spending 5 - 10 mins recreating it first.

Thanks,

Simon

MCSE: Data Platform
MCSE: Business Intelligence
Follow me on Twitter: @WazzTheBadger
LinkedIn Profile: Simon Osborne

jabberpunch Valued Member Points: 54 More actions · Answer 5

I absoutely will in the future and I apologize for not doing that (sorry Lowell). I work with sensitive data so tried to keep it minimal, but I will just have to do a bit of vetting and provide you guys with some actual DDL - the least I can do for the people that are always so willing to help out the less knowledgeable folk like myself!

Thanks again you guys!

Alan Burstein SSC Guru Points: 61136 More actions · Answer 6

You can also do this (I'm just showing another way to pivot):

;WITH sourceData ([RowID],[Region], [Factor])

AS

(

SELECT '1','Capitol',' Text1' UNION ALL

SELECT '2','Capitol',' Text2' UNION ALL

SELECT '3','Capitol',' Text3' UNION ALL

SELECT '1','Central',' Text4' UNION ALL

SELECT '2','Central',' Text5' UNION ALL

SELECT '3','Central',' Text6' UNION ALL

SELECT '1','North' ,'Text7' UNION ALL

SELECT '2','North' ,'Text8' UNION ALL

SELECT '3','North' ,'Text9'

)

SELECTRowID,

MAX(CASE WHEN Region = 'Capitol' THEN Factor END) AS Capitol,

MAX(CASE WHEN Region = 'Central' THEN Factor END) AS Central,

MAX(CASE WHEN Region = 'North' THEN Factor END) AS North

FROM sourceData

GROUP BY RowID

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001