Solved

transpose SQL results

Posted on 2011-09-29
4
219 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 15

Accepted Solution

by:
Eyal earned 500 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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 this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

718 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