How to select data in a particular format

  • Greetings all.

    I have data in a view that when selected from looks like this:

    CertYear CertCode Quantity

    -------- --------- --------

    2008 HI-Master 2

    2008 HI-Instr 1

    2008 HI-Train 1

    2008 HI-Master 3

    2008 HI-Train 1

    2009 HI-Master 3

    2009 HI-Instr 4

    There are a few other fields that are in this view, but I left them out because they're not really relevant to this question (apart from simply knowing that there are more fields in this view, which is why the data that I have above looks redundant in nature, but isn't). I've also left out more rows (such as year 2010 and later).

    What I need: Using a single select statement (I am not allowed to do this using a stored procedure), I would like to return a record set from this view that looks like the following:

    CertYear QtyMaster QtyInstr QtyTrain

    -------- ---------- -------- --------

    2008 7 2 4

    2009 3 4 3

    I have a query that I've constructed which gets me part way there, but not quite. What I have is this:

    select v.CertYear, v.CertCode

    , case when v.CertCode = 'HI-Master' then sum(v.Quantity)

    else 0

    end as QtyMaster

    , case when v.CertCode = 'HI-Instr' then sum(v.Quantity)

    else 0

    end as QtyInstr

    , case when v.CertCode = 'HI-Train' then sum(v.Quantity)

    else 0

    end as QtyTrain

    from dbo.vCerts v

    group by v.CertYear, v.CertCode

    order by v.CertYear asc, v.CertCode asc

    But what this select yields (like I said, it's not quite what I want) is this:

    CertYear CertCode QtyMaster QtyInstr QtyTrain

    -------- --------- ---------- -------- --------

    2008 HI-Master 7 0 0

    2008 HI-Instr 0 2 0

    2008 HI-Train 0 0 4

    2009 HI-Master 3 0 0

    2009 HI-Instr 0 4 0

    2009 HI-Train 0 0 3

    I tried remove the CertCode from the GroupBy and the select (except not from the case statement itself), because I thought that might get me to the record set that I want, but instead I get an error that says "Column 'dbo.vCerts.CertCode' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

    Thanks in advance for any help on this, and sorry for the complexity involved!

    edit: Sorry for the data that is hard to read. I'm not sure how to get whitespacing to persist on this site (it appears to be mostly ripped out).

  • Hi and welcome to SSC. It is really difficult to offer any assistance without something to work with. By that I mean it would be far better if you could post ddl (create table statements), sample data (insert statements) and desired output based on your sample data in addition to an explanation of what you are trying to do. Please see the first link in my signature for best practices when posting questions.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I think this is what you are after

    SELECTv.CertYear,

    SUM( CASE WHEN v.CertCode = 'HI-Master' THEN v.Quantity ELSE 0 END ) AS QtyMaster,

    SUM( CASE WHEN v.CertCode = 'HI-Instr' THEN v.Quantity ELSE 0 END ) AS QtyInstr,

    SUM( CASE WHEN v.CertCode = 'HI-Train' THEN v.Quantity ELSE 0 END ) AS QtyTrain

    FROMdbo.vCerts v

    GROUP BY v.CertYear

    ORDER BY v.CertYear ASC

    Check the below mentioned links for more information on this technique called CROSS-TABS

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]

    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]

    Edit: Added links to articles on CROSS-TABS


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • SELECT DISTINCT V.CERTYEAR,

    (SELECT SUM(QUATITY) FROM VCERTS WHERE CERTYEAR=V.CERTYEAR AND CERTCODE='HI-MASTER')QTYMASTER,

    (SELECT SUM(QUATITY) FROM VCERTS WHERE CERTYEAR=V.CERTYEAR AND CERTCODE='HI-INSTR')QTYINSTR,

    (SELECT SUM(QUATITY) FROM VCERTS WHERE CERTYEAR=V.CERTYEAR AND CERTCODE='HI-TRAIN')QTYTRAIN

    FROM DBO.VCERTS V

    ORDER BY 1

  • umarrizwan (4/1/2013)


    SELECT DISTINCT V.CERTYEAR,

    (SELECT SUM(QUATITY) FROM VCERTS WHERE CERTYEAR=V.CERTYEAR AND CERTCODE='HI-MASTER')QTYMASTER,

    (SELECT SUM(QUATITY) FROM VCERTS WHERE CERTYEAR=V.CERTYEAR AND CERTCODE='HI-INSTR')QTYINSTR,

    (SELECT SUM(QUATITY) FROM VCERTS WHERE CERTYEAR=V.CERTYEAR AND CERTCODE='HI-TRAIN')QTYTRAIN

    FROM DBO.VCERTS V

    ORDER BY 1

    4 Hits on the same table will get quite expensive. Take a look at the simple cross tab that Kingston wrote above.

    --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)

  • Hi,

    You can achieve this with Pivot as well..

    SELECT Certyear, [HI-Master] , [HI-Instr] , [HI-Train]

    FROM

    (SELECT Certyear,CertCode,Quantity FROM Certification) C

    PIVOT ( SUM (Quantity) FOR CertCode IN ( [HI-Master], [HI-Instr], [HI-Train]) ) AS pvt

    ORDER BY pvt.Certyear;

    [font="Verdana"]Regards,
    Rals
    [/font].
  • rals (4/2/2013)


    Hi,

    You can achieve this with Pivot as well..

    SELECT Certyear, [HI-Master] , [HI-Instr] , [HI-Train]

    FROM

    (SELECT Certyear,CertCode,Quantity FROM Certification) C

    PIVOT ( SUM (Quantity) FOR CertCode IN ( [HI-Master], [HI-Instr], [HI-Train]) ) AS pvt

    ORDER BY pvt.Certyear;

    Yes. Definitely.

    But I prefer CROSS TABS over PIVOT as they are better in terms of readability, maintenance and performance in most cases

    You can check the below mentioned link which has a comparison of these 2 methods based on these parameters

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • select CertYear, sum(QtyMaster) as QtyMaster, sum(QtyInstr) as QtyInstr

    , sum(QtyTrain) as QtyTrain

    from(

    select v.CertYear

    , case when v.CertCode = 'HI-Master' then sum(v.Quantity)

    else 0

    end as QtyMaster

    , case when v.CertCode = 'HI-Instr' then sum(v.Quantity)

    else 0

    end as QtyInstr

    , case when v.CertCode = 'HI-Train' then sum(v.Quantity)

    else 0

    end as QtyTrain

    from dbo.Cert v

    group by v.CertYear , v.CertCode

    ) as m

    group by CertYear

  • Thank you all for your help. I was in a bit of a time-crunch and managed to figure out how to do it, but what I came up with is identical to Kingston's select. Thanks again.

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

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