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

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%
0
jfreeman2010
Asked:
jfreeman2010
  • 4
  • 4
  • 3
  • +1
4 Solutions
 
Kevin CrossChief Technology OfficerCommented:
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
0
 
SharathData EngineerCommented:
try this.
SELECT YEAR_MONTH, 
       ONLINE_TOT, 
       CONVERT(DECIMAL(10, 2), ONLINE_TOT*100.0/GR_TOT) [ONLINE_TOT_%], 
       PHONE_TOT, 
       CONVERT(DECIMAL(10, 2), PHONE_TOT*100.0/GR_TOT)  [PHONE_TOT_%], 
       MANUAL_TOT, 
       CONVERT(DECIMAL(10, 2), MANUAL_TOT*100.0/GR_TOT) [MANUAL_TOT_%], 
       GR_TOT, 
       '100%'                                           [GR_TOT_%] 
  FROM (SELECT YEAR_MONTH, 
               MAX(CASE 
                     WHEN [TYPE] = 'ONLINE' THEN TOTAL 
                     ELSE 0 
                   END)    ONLINE_TOT, 
               MAX(CASE 
                     WHEN [TYPE] = 'PHONE' THEN TOTAL 
                     ELSE 0 
                   END)    PHONE_TOT, 
               MAX(CASE 
                     WHEN [TYPE] = 'MANUAL' THEN TOTAL 
                     ELSE 0 
                   END)    MANUAL_TOT, 
               MAX(GR_TOT) GR_TOT 
          FROM (SELECT *, 
                       SUM(TOTAL) OVER (PARTITION BY YEAR_MONTH) GR_TOT 
                  FROM your_table) t1 
         GROUP BY YEAR_MONTH) t2 

Open in new window

tested with your sample data.
CREATE TABLE #Test 
  ( 
     YEAR_MONTH VARCHAR(7), 
     [TYPE]     VARCHAR(10), 
     TOTAL      INT 
  ) 

INSERT #Test 
VALUES ('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)
       
SELECT YEAR_MONTH, 
       ONLINE_TOT, 
       CONVERT(DECIMAL(10, 2), ONLINE_TOT*100.0/GR_TOT) [ONLINE_TOT_%], 
       PHONE_TOT, 
       CONVERT(DECIMAL(10, 2), PHONE_TOT*100.0/GR_TOT)  [PHONE_TOT_%], 
       MANUAL_TOT, 
       CONVERT(DECIMAL(10, 2), MANUAL_TOT*100.0/GR_TOT) [MANUAL_TOT_%], 
       GR_TOT, 
       '100%'                                           [GR_TOT_%] 
  FROM (SELECT YEAR_MONTH, 
               MAX(CASE 
                     WHEN [TYPE] = 'ONLINE' THEN TOTAL 
                     ELSE 0 
                   END)    ONLINE_TOT, 
               MAX(CASE 
                     WHEN [TYPE] = 'PHONE' THEN TOTAL 
                     ELSE 0 
                   END)    PHONE_TOT, 
               MAX(CASE 
                     WHEN [TYPE] = 'MANUAL' THEN TOTAL 
                     ELSE 0 
                   END)    MANUAL_TOT, 
               MAX(GR_TOT) GR_TOT 
          FROM (SELECT *, 
                       SUM(TOTAL) OVER (PARTITION BY YEAR_MONTH) GR_TOT 
                  FROM #Test) t1 
         GROUP BY YEAR_MONTH) t2 
/*
YEAR_MONTH	ONLINE_TOT	ONLINE_TOT_%	PHONE_TOT	PHONE_TOT_%	MANUAL_TOT	MANUAL_TOT_%	GR_TOT	GR_TOT_%
2010-01	17	1.13	1488	98.87	0	0.00	1505	100%
2010-02	6	0.33	1793	99.67	0	0.00	1799	100%
2010-03	8	0.23	3171	89.27	373	10.50	3552	100%
2010-04	1	0.04	2148	94.71	119	5.25	2268	100%
2010-05	7	0.31	2196	98.39	29	1.30	2232	100%
2010-06	20	1.36	1436	97.62	15	1.02	1471	100%
2011-04	0	0.00	12	100.00	0	0.00	12	100%
*/
DROP TABLE #Test  

Open in new window

0
 
jfreeman2010Author Commented:
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
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
SharathData EngineerCommented:
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

0
 
jfreeman2010Author Commented:
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,
0
 
SharathData EngineerCommented:
Alter the database to set the compatibility level to 90 or 100.
ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = { 80 | 90 | 100 }

Open in new window

Or, you can try my initial post (without PIVOT).
If you have bigger query, instead of a table as I used in my intial post, you can wrap your query in a CTE and use that.
;with CTE as ( your_qiery)
SELECT YEAR_MONTH, 
       ONLINE_TOT, 
       CONVERT(DECIMAL(10, 2), ONLINE_TOT*100.0/GR_TOT) [ONLINE_TOT_%], 
       PHONE_TOT, 
       CONVERT(DECIMAL(10, 2), PHONE_TOT*100.0/GR_TOT)  [PHONE_TOT_%], 
       MANUAL_TOT, 
       CONVERT(DECIMAL(10, 2), MANUAL_TOT*100.0/GR_TOT) [MANUAL_TOT_%], 
       GR_TOT, 
       '100%'                                           [GR_TOT_%] 
  FROM (SELECT YEAR_MONTH, 
               MAX(CASE 
                     WHEN [TYPE] = 'ONLINE' THEN TOTAL 
                     ELSE 0 
                   END)    ONLINE_TOT, 
               MAX(CASE 
                     WHEN [TYPE] = 'PHONE' THEN TOTAL 
                     ELSE 0 
                   END)    PHONE_TOT, 
               MAX(CASE 
                     WHEN [TYPE] = 'MANUAL' THEN TOTAL 
                     ELSE 0 
                   END)    MANUAL_TOT, 
               MAX(GR_TOT) GR_TOT 
          FROM (SELECT *, 
                       SUM(TOTAL) OVER (PARTITION BY YEAR_MONTH) GR_TOT 
                  FROM CTE) t1 
         GROUP BY YEAR_MONTH) t2 

Open in new window

0
 
jfreeman2010Author Commented:
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!!!
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
Kevin CrossChief Technology OfficerCommented:
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

0
 
Kevin CrossChief Technology OfficerCommented:
Type-O: forgot the GROUP BY:
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_here}) AS t1
  GROUP BY YEAR_MONTH
) AS t2
;
0
 
jfreeman2010Author Commented:
Thank you sharath123, mwvisal and patelAlpesh for all your help.  I try both suggestion and all works.

A BIG THANK YOU FOR ALL OF YOU!!!
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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