?
Solved

transpose SQL results

Posted on 2011-09-29
4
Medium Priority
?
227 Views
Last Modified: 2012-05-12
The attached scripting works fine and produces a table format as follows:
DATE           TOTAL         CFW             NAT
01.09.2011      3906      555      1417
02.09.2011      3853      657      1336
03.09.2011      4154      775      1229
04.09.2011      3234      564      970
05.09.2011      3932      575      1414
06.09.2011      4110      671      1439
07.09.2011      4014      611      1380
08.09.2011      4319      838      1485
09.09.2011      4537      794      1664

I would like to produce a different output format as follows:

01.09.2011 TOTAL       3906
01.09.2011 CFW       555
01.09.2011 NAT        1417
02.09.2011 TOTAL       3853
02.09.2011 CFW       657
02.09.2011 NAT       1336

etc..

[LocationZoneID] is the Category seperator (i.e. TOTAL / CFW / NAST etc).
USE [db1]

DECLARE @endDate   datetime
DECLARE @startDate datetime

SET @startDate = '2011-09-01 00:00:00.000'
SET @endDate   = '2011-09-30 23:59:59.997'

SELECT

convert(CHAR(10), SetupTime,104) as 'DATE / CAT'

, COUNT(CASE WHEN ((NetworkCause = 0) OR (PrepaidCause = 0)) THEN 0 END) AS 'TOTAL'
       
, COUNT(CASE WHEN ((NetworkCause = 0) OR (PrepaidCause = 0)) AND (LocationZoneID = 167) THEN 0 END) AS 'CFW'
	 
, COUNT(CASE WHEN ((NetworkCause = 0) OR (PrepaidCause = 0)) AND (LocationZoneID = 168) THEN 0 END) AS 'NAT'

			  
FROM [CDRData] 

WHERE (Direction = 1) 
    AND (CallClass = 1)
	AND (SetupTime BETWEEN @startDate AND @endDate)
      
GROUP BY convert(CHAR(10), SetupTime, 104)
ORDER BY convert(DATETIME, convert(CHAR(10), SetupTime, 104), 104)

Open in new window

0
Comment
Question by:amillyard
  • 2
4 Comments
 
LVL 15

Accepted Solution

by:
Eyal earned 2000 total points
ID: 36813961
0
 
LVL 9

Expert Comment

by:sachinpatil10d
ID: 36814740
Try this
SELECT Date , ColHead, ColValue FROM    
(
     SELECT Date, Total,CFW, NAT   
     FROM CDRData) p
UNPIVOT   
(ColValue FOR 
     ColHead IN       (Total,CFW, NAT)
)AS unpvt;

Open in new window

0
 

Author Comment

by:amillyard
ID: 36890483
sachinpatil10d:  where do I insert that code supplied.  Have tried end of existing code - not compiling.
0
 

Author Comment

by:amillyard
ID: 36890490
Msg 207, Level 16, State 1, Line 52
Invalid column name 'Date'.
Msg 207, Level 16, State 1, Line 52
Invalid column name 'Total'.
Msg 207, Level 16, State 1, Line 52
Invalid column name 'CFW'.
Msg 207, Level 16, State 1, Line 52
Invalid column name 'NAT'.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question