CASE Group by - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

CASE Group by

Author

Topic

skillilea
Starting Member

15 Posts

Posted - 2012-03-22 : 17:53:18

what is the best way to get the date in columns.

Data is in rows by date
The values are in dentervalue
They have a QID (question ID)
There is one entry for each question for each month so there are 24 records total that I want to show in 12 rows of data return.

I am looking to return the values this way

Date QID(1) QID(2)
Jan
Feb
Mar
...

Here is what I have

SELECT
dEnterDate
, CASE
WHEN qID = 39 THEN dEnterValue END

, CASE
WHEN qID = 74 THEN dEnterValue END
FROM tblQuestion_Data

WHERE
qid IN(39,74)
AND entityID = 23
AND dEnterDate >= '3-1-2011'
AND dEnterDate <= '3-1-2012'

GROUP BY denterdate, dentervalue, qid

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-03-22 : 19:39:57

I can't quite follow what you want. Perhaps if you suppied the table definitions, some data and desired output it would clarify the issue.

=================================================
It is not so much our friends' help that helps us as the confident knowledge that they will help us. -Epicurus, philosopher (c. 341-270 BCE)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-22 : 21:21:04

[code]
SELECT
DATENAME(mm,denterdate)
, COUNT(QID)
FROM tblQuestion_Data

WHERE
qid IN(39,74)
AND entityID = 23
AND dEnterDate >= '3-1-2011'
AND dEnterDate <= '3-1-2012'
GROUP BY DATENAME(mm,denterdate)
[/code]
??

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skillilea
Starting Member

15 Posts

Posted - 2012-03-23 : 05:48:10

Thanks for the response.

Data looks like this:

DATE QID VALUE

3/1/2011 39 10
4/1/2011 39 11
5/1/2011 39 8
6/1/2011 39 66
3/1/2011 74 45
4/1/2011 74 54
5/1/2011 74 33
6/1/2011 74 55

I am trying to graph the data by month and need the return this way.

DATE QID(39) VALUES QID(74) VALUES
3/1/2011 10 45
4/1/2011 11 54
5/1/2011 8 33
6/1/2011 66 55

thanks for the help

skillilea
Starting Member

15 Posts

Posted - 2012-03-25 : 11:30:51

Any thoughts on best approach?

tnx

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-03-25 : 12:26:06

[code]
SELECT
*
FROM
YourTable
PIVOT
(MAX(Value) FOR QID IN ([39],[74]))P[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-26 : 13:31:23

are QID values static (always 39,74)?

if not extend suggestion like below

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

skillilea
Starting Member

15 Posts

Posted - 2012-03-26 : 20:58:13

Yep they are static in this case.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-27 : 12:13:15

then you're good to go with Sunita's suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources