Query help on Pivoting on two columns
-
DECLARE @RegionCounts Table
(
TYear char(2),
Region varchar(30),
RowCnt int
)
Insert into @RegionCounts values ('13','Latam',100)
Insert into @RegionCounts values ('13','NOAM',200)
Insert into @RegionCounts values ('14','Latam',300)
Insert into @RegionCounts values ('14','NOAM',400)
select * from @RegionCountsI need the out put as
[13latam] , [14latam], [13NOAM], [14NOAM] as column headers with corresponding values under it.
- This topic was modified 5 years, 6 months ago by s-sql.
-
DECLARE @RegionCounts Table
(
TYear char(2),
Region varchar(30),
RowCnt int
)
Insert into @RegionCounts values ('13','Latam',100)
Insert into @RegionCounts values ('13','NOAM',200)
Insert into @RegionCounts values ('14','Latam',300)
Insert into @RegionCounts values ('14','NOAM',400)
select * from @RegionCounts -
SELECT [13latam] = IIF(rc.TYear = 13 AND rc.Region = 'Latam', rc.RowCnt, NULL)
,[14latam] = IIF(rc.TYear = 14 AND rc.Region = 'Latam', rc.RowCnt, NULL)
,[13NOAM] = IIF(rc.TYear = 13 AND rc.Region = 'NOAM', rc.RowCnt, NULL)
,[14NOAM] = IIF(rc.TYear = 14 AND rc.Region = 'NOAM', rc.RowCnt, NULL)
FROM @RegionCounts rc;The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - s-sql wrote:
DECLARE @RegionCounts Table
(
TYear char(2),
Region varchar(30),
RowCnt int
)
Insert into @RegionCounts values ('13','Latam',100)
Insert into @RegionCounts values ('13','NOAM',200)
Insert into @RegionCounts values ('14','Latam',300)
Insert into @RegionCounts values ('14','NOAM',400)
select * from @RegionCountsI need the out put as [13latam] , [14latam], [13NOAM], [14NOAM] as column headers with corresponding values under it.
Phil's answer results in 4 rows and each row only has one value in it. The other 3 values in each row are NULL. I get the feeling that's not what you want (see below)
It's not Phil's fault here. In order to have these all appear on one row, you need some form of grouping to identify all values that should appear on a given row.
That brings us to our next question... will you only ever have 4 rows or can you have more? If more than 4 rows, do you want them all grouped on a single row or do you want to form row groups?
In either case, I recommend you study the following article, which explains the nature of what I believe you're trying to do.
In the mean time, if it's only 4 rows always and forever, then a slight modification of Sean's good code will do. It's called a CROSSTAB. We don't need a GROUP BY for this particular example because there's nothing to group by.
SELECT [13latam] = SUM(IIF(rc.TYear = 13 AND rc.Region = 'Latam', rc.RowCnt, 0))
,[14latam] = SUM(IIF(rc.TYear = 14 AND rc.Region = 'Latam', rc.RowCnt, 0))
,[13NOAM] = SUM(IIF(rc.TYear = 13 AND rc.Region = 'NOAM' , rc.RowCnt, 0))
,[14NOAM] = SUM(IIF(rc.TYear = 14 AND rc.Region = 'NOAM' , rc.RowCnt, 0))
FROM @RegionCounts rc;The result looks like this...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Heh, Phil's name is not, nor ever has been, Sean!
And the query was deliberately written in that way, as the OP did not specify the need to see aggregated results in a single row.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin - Phil Parkin wrote:
Heh, Phil's name is not, nor ever has been, Sean! And the query was deliberately written in that way, as the OP did not specify the need to see aggregated results in a single row.
Oh my... I'm truly embarrassed. :blush: I was working on a couple of posts at the same time and got messed up. Not a good excuse though. My apologies, Phil.
I've repaired the post.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:Phil Parkin wrote:
Heh, Phil's name is not, nor ever has been, Sean! And the query was deliberately written in that way, as the OP did not specify the need to see aggregated results in a single row.
Oh my... I'm truly embarrassed. :blush: I was working on a couple of posts at the same time and got messed up. Not a good excuse though. My apologies, Phil. I've repaired the post.
Jeff, I had a smile on my face as I wrote that post and I am not offended in the least – it is a pretty good excuse. In fact, if you confused me with the great Sean Lange, I am flattered.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Heh... I could tell that you weren't offended and were probably having a pretty good guffaw while writing it and, yes, absolutely, you and Sean are outstanding heavy hitters on this forum. But to make such a simple mistake on my part is still embarrassing.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply