Solved

transpose SQL results

Posted on 2011-09-29
4
215 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 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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

776 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