March 29, 2013 at 11:40 am
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).
March 29, 2013 at 12:14 pm
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/
March 31, 2013 at 11:34 pm
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
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 1, 2013 at 6:17 am
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
April 1, 2013 at 4:15 pm
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
Change is inevitable... Change for the better is not.
April 2, 2013 at 3:12 am
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;
April 2, 2013 at 3:27 am
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]
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 2, 2013 at 6:47 am
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
April 2, 2013 at 10:51 am
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