Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

T SQL statement issue Expand / Collapse
Author
Message
Posted Thursday, February 21, 2013 6:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 3:49 AM
Points: 32, Visits: 135
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
Posted Thursday, February 21, 2013 6:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:59 AM
Points: 7,129, Visits: 13,512
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
Posted Thursday, February 21, 2013 6:31 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 3:49 AM
Points: 32, Visits: 135
Its embarassing . Thank you!!
Post #1422538
Posted Thursday, February 21, 2013 7:06 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:59 AM
Points: 7,129, Visits: 13,512
chandrakant_gaurav (2/21/2013)
Its embarassing . 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

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 #1422565
Posted Tuesday, February 26, 2013 9:43 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 36,786, Visits: 31,243
chandrakant_gaurav (2/21/2013)
Its embarassing . 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1424324
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse