Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

transpose SQL results

Posted on 2011-09-29
4
Medium Priority
?
225 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 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

609 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