• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 517
  • Last Modified:

How to PIVOT Data

I am running the following query on a sql 2005 database

DECLARE @start DATETIME,
    @end DATETIME


        SET @start = DATEADD(day, -1, CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 120), 120))
        SET @end = DATEADD(day, 1, @start)


SELECT  CASE WHEN cd.DigitsDialed = '11783' THEN 'CCU01'
             WHEN cd.DigitsDialed = '11767' THEN 'CCU02'
             WHEN cd.DigitsDialed IN ( '11581', '11683' ) THEN 'CCU03'
             ELSE 'CCU04'
        END Line,
        SUM(CASE WHEN cd.CallDispositionFlag = 1
                      AND cd.PeripheralCallType IN ( 2, 4, 13 )
                      AND cd.CallDisposition IN ( 29, 13, 34, 28, 6 ) THEN 1
            END) Ans,
        SUM(CASE WHEN ( cd.CallDispositionFlag = 4
                        OR cd.CallDisposition IN ( 3, 1, 2 )
                      ) THEN 1
            END) Abn,
        SUM(CASE WHEN cd.CallDispositionFlag = 1
                      AND cd.PeripheralCallType IN ( 2, 4, 13 )
                      AND cd.CallDisposition IN ( 29, 13, 34, 28, 6 )
                      AND ( cd.NetQTime + cd.RingTime + cd.DelayTime ) <= 20
                 THEN 1
            END) Ans20,
        SUM(CASE WHEN cd.CallDisposition = 14 THEN 1
            END) Voicemail,
        SUM(CASE WHEN cd.CallDispositionFlag = 1
                      AND cd.PeripheralCallType IN ( 2, 4, 13 )
                      AND cd.CallDisposition IN ( 29, 13, 34, 28, 6 )
                 THEN cd.TalkTime
            END) TalkTime,
        SUM(cd.NetQTime) QTime,
        MAX(cd.TalkTime) MaxTalkTime,
        MAX(cd.NetQTime) MaxQTime
FROM    t_Termination_Call_Detail cd
WHERE   cd.DateTime >= @start
        AND cd.DateTime < @end
        AND cd.DigitsDialed IN ( '11581', '11683', '11722', '11767', '11783' )
GROUP BY CASE WHEN cd.DigitsDialed = '11783' THEN 'CCU01'
              WHEN cd.DigitsDialed = '11767' THEN 'CCU02'
              WHEN cd.DigitsDialed IN ( '11581', '11683' ) THEN 'CCU03'
              ELSE 'CCU04'
         END

The results give me the following format

                  Ans    Abn      Ans20   Voicemail      etc
CCU01        2
CCU02        2
CCU03        2
CCU04        3

I would now like to display the results as following, but I not sure how


                CCU01           CCU02        CCU03    CCU04
Ans
Abn
Ans20
Voicemail

etc

Is this possible?
0
Mark Wilson
Asked:
Mark Wilson
  • 3
  • 2
1 Solution
 
elimesikaCommented:
0
 
Máté FarkasDatabase Developer and AdministratorCommented:
You should rewrite your base query (remove groupping and summarize):
than place it in a pivot query:
SELECT  Field, [CCU01], [CCU02], [CCU03], [CCU04]
FROM (<base query>) B
PIVOT (SUM([Value]) FOR Field IN ([CCU01], [CCU02], [CCU03], [CCU04])) P

SELECT  CASE WHEN cd.DigitsDialed = '11783' THEN 'CCU01'
             WHEN cd.DigitsDialed = '11767' THEN 'CCU02'
             WHEN cd.DigitsDialed IN ( '11581', '11683' ) THEN 'CCU03'
             ELSE 'CCU04'
        END Line,
        CASE 
			WHEN cd.CallDispositionFlag = 1
                      AND cd.PeripheralCallType IN ( 2, 4, 13 )
                      AND cd.CallDisposition IN ( 29, 13, 34, 28, 6 ) THEN 'Ans'
			WHEN ( cd.CallDispositionFlag = 4
                        OR cd.CallDisposition IN ( 3, 1, 2 )
                      ) THEN 'Abn'
			WHEN cd.CallDispositionFlag = 1
                      AND cd.PeripheralCallType IN ( 2, 4, 13 )
                      AND cd.CallDisposition IN ( 29, 13, 34, 28, 6 )
                      AND ( cd.NetQTime + cd.RingTime + cd.DelayTime ) <= 20
                 THEN 'Ans20'
			WHEN cd.CallDisposition = 14 THEN 'Voicemail'
			END Field,
		1 [Value]
FROM    t_Termination_Call_Detail cd
WHERE   cd.DateTime >= @start
        AND cd.DateTime < @end
        AND cd.DigitsDialed IN ( '11581', '11683', '11722', '11767', '11783' )

Open in new window

0
 
Mark WilsonBI DeveloperAuthor Commented:
Thanks aqux3e

I have done as suggested and get the following error message

Invalid Column Name Field
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Máté FarkasDatabase Developer and AdministratorCommented:
Sorry! Use "FOR Line IN" instead of Field in the pivot expression....
0
 
Mark WilsonBI DeveloperAuthor Commented:
Hi

I am now getting invalid Column Name Line

I have included the code as it is now
DECLARE @start DATETIME,
    @end DATETIME


        SET @start = DATEADD(day, -1, CONVERT(DATETIME, CONVERT(VARCHAR(10), GETDATE(), 120), 120))
        SET @end = DATEADD(day, 1, @start)
         
SELECT  Line, [CCU01], [CCU02], [CCU03], [CCU04]
FROM (SELECT  CASE WHEN cd.DigitsDialed = '11783' THEN 'CCU01' 
             WHEN cd.DigitsDialed = '11767' THEN 'CCU02' 
             WHEN cd.DigitsDialed IN ( '11581', '11683' ) THEN 'CCU03' 
             ELSE 'CCU04' 
        END Line, 
        CASE  
                        WHEN cd.CallDispositionFlag = 1 
                      AND cd.PeripheralCallType IN ( 2, 4, 13 ) 
                      AND cd.CallDisposition IN ( 29, 13, 34, 28, 6 ) THEN 'Ans' 
                        WHEN ( cd.CallDispositionFlag = 4 
                        OR cd.CallDisposition IN ( 3, 1, 2 ) 
                      ) THEN 'Abn' 
                        WHEN cd.CallDispositionFlag = 1 
                      AND cd.PeripheralCallType IN ( 2, 4, 13 ) 
                      AND cd.CallDisposition IN ( 29, 13, 34, 28, 6 ) 
                      AND ( cd.NetQTime + cd.RingTime + cd.DelayTime ) <= 20 
                 THEN 'Ans20' 
                        WHEN cd.CallDisposition = 14 THEN 'Voicemail' 
                        END Field, 
                1 [Value] 
FROM    t_Termination_Call_Detail cd 
WHERE   cd.DateTime >= @start 
        AND cd.DateTime < @end 
        AND cd.DigitsDialed IN ( '11581', '11683', '11722', '11767', '11783' )) B
PIVOT (SUM([Value]) FOR Line IN ([CCU01], [CCU02], [CCU03], [CCU04])) P 

Open in new window

0
 
Máté FarkasDatabase Developer and AdministratorCommented:
You sould use Field after SELECT  and Line in the PIVOT expression...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now