SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Not sure why PIVOT returns a single row?


Not sure why PIVOT returns a single row?

Author
Message
jabberpunch
jabberpunch
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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
s_osborne2
s_osborne2
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1019 Visits: 2292
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
Lowell
One Orange Chip
One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)One Orange Chip (29K reputation)

Group: General Forum Members
Points: 29072 Visits: 39984
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
jabberpunch
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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!
s_osborne2
s_osborne2
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1019 Visits: 2292
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
jabberpunch
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
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!
Alan.B
Alan.B
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5358 Visits: 7736
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



Best practices for getting help on SQLServerCentral
Need to split a string? Try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL 2012+)
Need a pattern-based splitter? Try PatternSplitCM
Need to remove or replace those unwanted characters? Try PatExclude8K and PatReplace8K.

"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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search