T SQL statement issue

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » T SQL statement issue

T SQL statement issue

Rate Topic

Display Mode

Topic Options

Author

Message

chandrakant_gaurav

Posted Thursday, February 21, 2013 6:10 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:41 AM
Points: 26, Visits: 101

Guys,
I have a requirement as follows:
CREATE TABLE #temp
(CallDate DAtetime,
Talktime INT,
ID INT,
others Varchar(10))
INSERT INTO #temp
SELECT '2013-01-01', 10,1,'ABC'
UNION
SELECT '2013-01-01', 10,1,'---'
UNION
SELECT '2013-01-01', 20,2,'XYZ'
UNION
SELECT '2013-01-01', 20,2,'--'
UNION
SELECT '2013-01-01', 30,3,'asd'
UNION
SELECT '2013-01-01', 30,3,'---'
UNION
SELECT '2013-01-02', 40,1,'ABCD'
UNION
SELECT '2013-01-02', 40,1,'---_'
UNION
SELECT '2013-01-02', 30,2,'XYZQ'
UNION
SELECT '2013-01-02', 30,2,'--1'
UNION
SELECT '2013-01-02', 20,3,'asdf'
UNION
SELECT '2013-01-02', 20,3,'---'

Using a query like this:
--------------
select calldate,
SUM(talktime) as Overall,
case when ID = 1 then SUM(talktime) END AS A_Talktime,
case when ID = 2 then SUM(talktime) END AS B_Talktime,
case when ID = 3 then SUM(talktime) END AS C_Talktime
FROM #temp
GROUP BY calldate, ID
--------------------
I need an output like below:

Calldate------------Overall------A_Talktime-----B_Talktime------C_Talktime
_________________________________________________________________
2013-01-01----------120-----------20-------------40--------------60
2013-01-02----------180-----------80-------------60--------------40

Let me know if any other info is required.

Thanks

Post #1422524

ChrisM@Work

Posted Thursday, February 21, 2013 6:25 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 5,613, Visits: 10,981

So nearly there...

SELECT 
	calldate,
	Overall		= SUM(talktime),
	A_Talktime	= SUM(case when ID = 1 then talktime END),
	B_Talktime	= SUM(case when ID = 2 then talktime END),
	C_Talktime	= SUM(case when ID = 3 then talktime END) 
FROM #temp
GROUP BY calldate

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps

Post #1422534

chandrakant_gaurav

Posted Thursday, February 21, 2013 6:31 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, May 16, 2013 6:41 AM
Points: 26, Visits: 101

Its embarassing Blush

. Thank you!!

Post #1422538

ChrisM@Work

Posted Thursday, February 21, 2013 7:06 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:32 AM
Points: 5,613, Visits: 10,981

chandrakant_gaurav (2/21/2013)

Its embarassing Blush

. Thank you!!

Heh - don't be embarrassed, you almost got there. Thanks loads for posting a sample script.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

Post #1422565

Jeff Moden

Posted Tuesday, February 26, 2013 9:43 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:26 PM
Points: 32,906, Visits: 26,791

chandrakant_gaurav (2/21/2013)

Its embarassing Blush

. Thank you!!

If you'd like to learn more about this "CROSSTAB" technique (which is also known as a PIVOT), please see the following articles...

http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1424324

« Prev Topic | Next Topic »

Permissions