asked on

t-sql pivot a select query result data

I have a select query, the result as following:

YEAR_MONTH      TYPE            TOTAL
----------      ---------      ---------
2010-01            ONLINE            17
2010-02            ONLINE            6
2010-03            ONLINE            8
2010-04            ONLINE            1
2010-05            ONLINE            7
2010-06            ONLINE            20
2010-03            MANUAL            373
2010-04            MANUAL            119
2010-05            MANUAL            29
2010-06            MANUAL            15
2010-01            PHONE            1488
2010-02            PHONE            1793
2010-03            PHONE            3171
2010-04            PHONE            2148
2010-05            PHONE            2196
2010-06            PHONE            1436
2011-04            PHONE            12

I need to Pivot the above data to the following result, its positable?

MONTH ONLINE_TOT ONLINE_% MAN_TOT MAN_% PHONE_TOT PHONE_% GR_TOT GR_TOT_%
------- ---------- -------- ------- ----- --------- ------- ------ --------
2010-01        17        1.12%      0 0%        1488        98.98% 1505       100%
2010-02        6        0.33%      0 0%        1793        99.67% 1799       100%
2010-03        8        0.23%      373 10.5% 3171        89.74% 3552       100%
....
2011        0        0%             0 0%        12        100% 12        100%

Kevin Cross

Hi.

You can use the PIVOT keyword in SQL 2008 to pivot the data; however, since you want percentages of total included and appear to have a very finite list of TYPE values you can look at conditional aggregates also.

For the PIVOT option, it could look something like this:

SELECT pvt.[YEAR_MONTH] AS [MONTH]
     , COALESCE(pvt.[ONLINE], 0) AS ONLINE_TOT
	 , CONVERT(DECIMAL(10, 2), COALESCE(pvt.[ONLINE], 0) * 100.0/(COALESCE(pvt.[ONLINE], 0) + COALESCE(pvt.[MANUAL], 0) + COALESCE(pvt.[PHONE], 0))) AS [ONLINE_%]
	 , COALESCE(pvt.[MANUAL], 0) AS MAN_TOT
	 , CONVERT(DECIMAL(10, 2), COALESCE(pvt.[MANUAL], 0) * 100.0/(COALESCE(pvt.[ONLINE], 0) + COALESCE(pvt.[MANUAL], 0) + COALESCE(pvt.[PHONE], 0))) AS [MAN_%]
	 , COALESCE(pvt.[PHONE], 0) AS PHONE_TOT
	 , CONVERT(DECIMAL(10, 2), COALESCE(pvt.[PHONE], 0) * 100.0/(COALESCE(pvt.[ONLINE], 0) + COALESCE(pvt.[MANUAL], 0) + COALESCE(pvt.[PHONE], 0))) AS [PHONE_%]
	 , (COALESCE(pvt.[ONLINE], 0) + COALESCE(pvt.[MANUAL], 0) + COALESCE(pvt.[PHONE], 0)) AS GR_TOT 
	 , CONVERT(DECIMAL(10, 2), 100) AS [GR_%]
FROM your_query
PIVOT (
   SUM([TOTAL])
   FOR [TYPE]
   IN ([ONLINE], [MANUAL], [PHONE])
) pvt

Open in new window

You can improve the readability by deriving or using common table expression for the pivot portion and then use the value of GR_TOT in the final selection instead of having to repeat the addition of 3 columns of data for MANUAL, ONLINE and PHONE types. Additionally, if case you may have records in the data with 0 for all three types, then you could add a CASE statement to avoid divide by 0 GR_TOT in that instance.

Other than that, I hope you get the gist.

Hope that helps!

Kevin

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jfreeman2010

ASKER

Hi Kevin,

Thank you very much for the responses. I try it, only put my query with () and get the incorrect syntax near '(' on SUM, do you know why? thanks,:

SELECT pvt.[YEAR_MONTH] AS [MONTH]
, COALESCE(pvt.[ONLINE], 0) AS ONLINE_TOT
       , CONVERT(DECIMAL(10, 2), COALESCE(pvt.[ONLINE], 0) * 100.0/(COALESCE(pvt.[ONLINE], 0) + COALESCE(pvt.[MANUAL], 0) + COALESCE(pvt.[PHONE], 0))) AS [ONLINE_%]
       , COALESCE(pvt.[MANUAL], 0) AS MAN_TOT
       , CONVERT(DECIMAL(10, 2), COALESCE(pvt.[MANUAL], 0) * 100.0/(COALESCE(pvt.[ONLINE], 0) + COALESCE(pvt.[MANUAL], 0) + COALESCE(pvt.[PHONE], 0))) AS [MAN_%]
       , COALESCE(pvt.[PHONE], 0) AS PHONE_TOT
       , CONVERT(DECIMAL(10, 2), COALESCE(pvt.[PHONE], 0) * 100.0/(COALESCE(pvt.[ONLINE], 0) + COALESCE(pvt.[MANUAL], 0) + COALESCE(pvt.[PHONE], 0))) AS [PHONE_%]
       , (COALESCE(pvt.[ONLINE], 0) + COALESCE(pvt.[MANUAL], 0) + COALESCE(pvt.[PHONE], 0)) AS GR_TOT
       , CONVERT(DECIMAL(10, 2), 100) AS [GR_%]
FROM (your_query)
PIVOT (
SUM([TOTAL])
FOR [TYPE]
IN ([ONLINE], [MANUAL], [PHONE])
) pvt

Sharath S

give an alias to your query.

SELECT pvt.[YEAR_MONTH] AS [MONTH]
     , COALESCE(pvt.[ONLINE], 0) AS ONLINE_TOT
       , CONVERT(DECIMAL(10, 2), COALESCE(pvt.[ONLINE], 0) * 100.0/(COALESCE(pvt.[ONLINE], 0) + COALESCE(pvt.[MANUAL], 0) + COALESCE(pvt.[PHONE], 0))) AS [ONLINE_%]
       , COALESCE(pvt.[MANUAL], 0) AS MAN_TOT
       , CONVERT(DECIMAL(10, 2), COALESCE(pvt.[MANUAL], 0) * 100.0/(COALESCE(pvt.[ONLINE], 0) + COALESCE(pvt.[MANUAL], 0) + COALESCE(pvt.[PHONE], 0))) AS [MAN_%]
       , COALESCE(pvt.[PHONE], 0) AS PHONE_TOT
       , CONVERT(DECIMAL(10, 2), COALESCE(pvt.[PHONE], 0) * 100.0/(COALESCE(pvt.[ONLINE], 0) + COALESCE(pvt.[MANUAL], 0) + COALESCE(pvt.[PHONE], 0))) AS [PHONE_%]
       , (COALESCE(pvt.[ONLINE], 0) + COALESCE(pvt.[MANUAL], 0) + COALESCE(pvt.[PHONE], 0)) AS GR_TOT 
       , CONVERT(DECIMAL(10, 2), 100) AS [GR_%]
FROM  (your_query) as t1
PIVOT (
   SUM([TOTAL])
   FOR [TYPE]
   IN ([ONLINE], [MANUAL], [PHONE])
) pvt

Open in new window

jfreeman2010

ASKER

Hi Sharath 123,

I try your suggestion, give an alias to my query, got error:
Msg 325, Level 15, State 1, Line 38
Incorrect syntax near 'PIVOT'. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.

I am using 2008 R2 SQL server.
I may need to put my query result to a temp table and use you query to try it. Yes, I am going to try it now...

thanks,

SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jfreeman2010

ASKER

Hi sharath 123, I am trying use your initial post (without PIVOT) now. will by back with the result. I am new with PIVOT, it seem good to know/learn that and I also like your CTE suggest, will try that too.

thanks!!!

Kevin Cross

Sharath's solution without PIVOT is what I referred to as conditional aggregates and will work if your database is not set to SQL 2005 or higher compatibility which will be required for you to use PIVOT, Common Table Expressions (CTE) and the like.

Kevin Cross

My only alteration to Sharath's solution now looking back is, you will probably want to go with SUM(). Just in case you get multiple entries for a given [TYPE] for a specific [YEAR_MONTH] I would suspect those should be added together and not returned as highest only. Also note that ROW_NUMBER() will not work if you are not running SQL 2005 or higher either.

So, I would change to this:

SELECT YEAR_MONTH
     , ONLINE_TOT, CONVERT(DECIMAL(10, 2), ONLINE_TOT*100.0/GR_TOT) AS [ONLINE_%]
	 , MAN_TOT, CONVERT(DECIMAL(10, 2), MAN_TOT*100.0/GR_TOT) AS [MAN_%]
	 , PHONE_TOT, CONVERT(DECIMAL(10, 2), PHONE_TOT*100.0/GR_TOT) AS [PHONE_%]
	 , GR_TOT, CONVERT(DECIMAL(10, 2), 100) AS [GR_%] 
FROM (
   SELECT YEAR_MONTH
        , SUM(CASE [TYPE] WHEN 'ONLINE' THEN [TOTAL] ELSE 0 END) AS ONLINE_TOT
        , SUM(CASE [TYPE] WHEN 'MANUAL' THEN [TOTAL] ELSE 0 END) AS MAN_TOT
        , SUM(CASE [TYPE] WHEN 'PHONE' THEN [TOTAL] ELSE 0 END) AS PHONE_TOT
        , SUM([TOTAL]) AS GR_TOT
   FROM (your_query) AS t1
) AS t2
;

Open in new window

SOLUTION

Kevin Cross

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION