DECLARE @RegionCounts Table
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
I 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...
is pronounced "ree-bar
" and is a "Modenism
" for R
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.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8
is also a glyph that describes the nature of a DBAs job. 😉
How to post code problems
Create a Tally Function (fnTally)