Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Not sure why PIVOT returns a single row? Expand / Collapse
Author
Message
Posted Tuesday, November 27, 2012 8:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 10:31 AM
Points: 6, Visits: 25
I'm new to using the PIVOT operator and I want to PIVOT the following CTE result set:

RowID Region Factor
===== ====== ======
1 Capitol Text1
2 Capitol Text2
3 Capitol Text3
1 Central Text4
2 Central Text5
3 Central Text6
1 North Text7
2 North Text8
3 North 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
Post #1389234
Posted Tuesday, November 27, 2012 8:46 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:22 AM
Points: 711, Visits: 2,209
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
Post #1389251
Posted Tuesday, November 27, 2012 8:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:16 AM
Points: 12,923, Visits: 32,282
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1389253
Posted Tuesday, November 27, 2012 9:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 10:31 AM
Points: 6, Visits: 25
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!
Post #1389288
Posted Tuesday, November 27, 2012 9:51 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 5:22 AM
Points: 711, Visits: 2,209
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
Post #1389292
Posted Tuesday, November 27, 2012 10:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 3, 2013 10:31 AM
Points: 6, Visits: 25
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!
Post #1389303
Posted Wednesday, November 28, 2012 9:46 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 5:04 AM
Points: 632, Visits: 2,949
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'
)

SELECT RowID,
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



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't 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. " -- Itzek Ben-Gan 2001

My blog
Post #1389926
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse