Query help on Pivoting on two columns

  • s-sql

    SSCommitted

    Points: 1970

    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

     

    I need the out put as

    [13latam] , [14latam], [13NOAM], [14NOAM] as column headers with corresponding values under it.

    • This topic was modified 3 months, 3 weeks ago by  s-sql.
  • s-sql

    SSCommitted

    Points: 1970

    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
  • Phil Parkin

    SSC Guru

    Points: 243596

    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;

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Jeff Moden

    SSC Guru

    Points: 994519

    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 @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.

    https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-1-%E2%80%93-converting-rows-to-columns-1

    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.
    "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. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Phil Parkin

    SSC Guru

    Points: 243596

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Jeff Moden

    SSC Guru

    Points: 994519

    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.
    "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. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Phil Parkin

    SSC Guru

    Points: 243596

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Jeff Moden

    SSC Guru

    Points: 994519

    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.
    "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. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 8 (of 8 total)

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