Solved

transpose SQL results

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

732 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