How can I cross tab this table

Question

I am using sql server 2008 and I want to cross tab this table

Month   Affec   KPI     Total   KPI_%   Out     rep_in_10  ftm
Jan-11  30565   34623   42003   82.4    7380    7003       5024
Jan-12  20955   25915   27857   93      1942    4754       3518
Feb-11  27754   27757   36483   76.1    8726    5648       4189
Feb-12  19513   25188   26962   93.4    1774    5768       4185
Mar-11  22838   23758   29951   79.3    6193    4394       3282
Mar-12  18778   25098   26177   95.9    1079    5784       4105
Apr-11  20235   21950   25917   84.7    3967    3895       2967

to

            Jan-11  Jan-12  Feb-11  Feb-12  Mar-11  Apr-11
Affec       30565   
KPI         34623   
Total       42003   
KPI_%       82.4         
Out         7380    
rep_in_10   7003

i have no idea where i should start from i think we should use aggregate function in pivot also i have never tried to cross tab multiple columns — Seif sammain, Dec 10 '12 at 09:22

score 1 · Answer 1 · answered Dec 10 '12 at 10:05

In my opinion you shouldn't do this. You can achieve this easily on presentation layer with PHP or whatever you are using. Databases are there to get you the data, not to format it nicely. See Mahmoud's answer more as a proof of concept. But the query will never be as fast as the query you have right now to get the data. Maintenance may be another argument against it.

score 0 · Answer 2 · answered Dec 10 '12 at 09:58

Like this:

;WITH UNpivoted
AS
(
  SELECT MonthsValue, Value, month
  FROM (SELECT [Month], 
        CAST([Affec] AS VARCHAR(10)) Affec ,
        CAST([KPIprecent] AS VARCHAR(10)) KPIprecent,
        CAST([Total] AS VARCHAR(10)) Total, 
        CAST([KPI] AS VARCHAR(10)) KPI, 
        CAST([Out] AS VARCHAR(10)) [Out],
        CAST([rep_in_10] AS VARCHAR(10)) [rep_in_10], 
        CAST([ftm] AS VARCHAR(10)) ftm
        FROM table1
       ) t
  UNPIVOT
  (
    MonthsValue FOR Value IN([Affec], 
                             [KPIprecent], 
                             [Total], 
                             [KPI],        
                             [Out], 
                             [rep_in_10], 
                             [ftm])
  ) u
)
  SELECT
    value,
    [Jan-11], 
    [Jan-12], 
    [Feb-11], 
    [Feb-12],
    [Mar-11], 
    [Mar-12], 
    [Apr-11]
  FROM
  (
    SELECT monthsvalue, value, month
    FROM Unpivoted
  ) t
  PIVOT
  (MAX(monthsvalue) for Month IN ([Jan-11], 
                                 [Jan-12], 
                                 [Feb-11], 
                                 [Feb-12],
                                 [Mar-11], 
                                 [Mar-12], 
                                 [Apr-11])
  ) p;

SQL Fiddle Demo

This will give you:

|      VALUE | JAN-11 | JAN-12 | FEB-11 | FEB-12 | MAR-11 | MAR-12 | APR-11 |
-----------------------------------------------------------------------------
|      Affec |  30565 |  20955 |  27754 |  19513 |  22838 |  18778 |  20235 |
|        ftm |   5024 |   3518 |   4189 |   4185 |   3282 |   4105 |   2967 |
|        KPI |   82.4 |   93.0 |   76.1 |   93.4 |   79.3 |   95.9 |   84.7 |
| KPIprecent |  34623 |  25915 |  27757 |  25188 |  23758 |  25098 |  21950 |
|        Out |   7380 |   1942 |   8726 |   1774 |   6193 |   1079 |   3967 |
|  rep_in_10 |   7003 |   4754 |   5648 |   5768 |   4394 |   5784 |   3895 |
|      Total |  42003 |  27857 |  36483 |  26962 |  29951 |  26177 |  25917 |

thanks for help you code is working but i used dynamic pivot — Seif sammain, Dec 10 '12 at 11:19

Taryn · Accepted Answer · 2012-12-10T10:24:52.347

For this type of data transformation, you will need to use the UNPIVOT function and then apply the PIVOT function in SQL Server.

There are two ways to perform this, either hard-coding the values with a static version of using dynamic sql to generate the values as run-time.

Static Version:

The UNPIVOT piece of this takes the data from your multiple columns and transforms it into two rows. Note, the one thing to keep in mind with unpivot is that the datatypes must be the same. So you might have to perform a data type conversion on the data.:

select [Month], value, col
from
(
  select DateName(month,[Month]) +'-'+Cast(datepart(year, [month]) as varchar(4)) Month,
    [Affec], [KPI], [Total], [KPI_%], [Out], [rep_in_10], [ftm]
  from yourtable
) src
unpivot
(
  value
  for col in ([Affec], [KPI], [Total], [KPI_%], [Out], [rep_in_10], [ftm])
) unpiv

See SQL Fiddle with Demo

Result:

|         MONTH | VALUE |       COL |
-------------------------------------
|  January-2011 | 30565 |     Affec |
|  January-2011 | 34623 |       KPI |
|  January-2011 | 42003 |     Total |
|  January-2011 |  82.4 |     KPI_% |
|  January-2011 |  7380 |       Out |  ---etc

Then you apply the PIVOT to the months:

select *
from
(
  select [Month], value, col
  from
  (
    select DateName(month,[Month]) +'-'+Cast(datepart(year, [month]) as varchar(4)) Month,
      [Affec], [KPI], [Total], [KPI_%], [Out], [rep_in_10], [ftm]
    from yourtable
  ) src
  unpivot
  (
    value
    for col in ([Affec], [KPI], [Total], [KPI_%], [Out], [rep_in_10], [ftm])
  ) unpiv
) src
pivot
(
  max(value)
  for month in ([January-2011], [February-2011], [March-2011],
             [April-2011], [January-2012], [February-2012], [March-2012])
) piv

See SQL Fiddle with Demo

Result:

|       COL | JANUARY-2011 | FEBRUARY-2011 | MARCH-2011 | APRIL-2011 | JANUARY-2012 | FEBRUARY-2012 | MARCH-2012 |
------------------------------------------------------------------------------------------------------------------
|     Affec |        30565 |         27754 |      22838 |      20235 |        20955 |         19513 |      18778 |
|       ftm |         5024 |          4189 |       3282 |       2967 |         3518 |          4185 |       4105 |
|       KPI |        34623 |         27757 |      23758 |      21950 |        25915 |         25188 |      25098 |
|     KPI_% |         82.4 |          76.1 |       79.3 |       84.7 |           93 |          93.4 |       95.9 |
|       Out |         7380 |          8726 |       6193 |       3967 |         1942 |          1774 |       1079 |
| rep_in_10 |         7003 |          5648 |       4394 |       3895 |         4754 |          5768 |       5784 |
|     Total |        42003 |         36483 |      29951 |      25917 |        27857 |         26962 |      26177 |

Dynamic Version:

The above works great, if you have a known number of values but it your values are unknown then you will want to use dynamic sql. I am going to guess that you will want a dynamic version since you will have a unknown number of dates:

DECLARE @colsUnpivot AS NVARCHAR(MAX),
    @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @colsUnpivot = STUFF((SELECT DISTINCT ',' 
                      + quotename(c.name)
                    from sys.columns as C
                   where C.object_id = object_id('yourtable') and
                         C.name not in ('Month')
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @cols = STUFF((SELECT ',' + QUOTENAME(DateName(month,[Month]) +'-'+Cast(datepart(year, [month]) as varchar(4))) 
                    from yourtable
                    group by [Month]
                    order by [Month]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT col,' + @cols + ' from 
             (
               select [Month], value, col
              from
              (
                select DateName(month,[Month]) +''-''+Cast(datepart(year, [month]) as varchar(4)) Month,
                  [Affec], [KPI], [Total], [KPI_%], [Out], [rep_in_10], [ftm]
                from yourtable
              ) src
              unpivot
              (
                value
                for col in ('+@colsunpivot+')
              ) unpiv
            ) x
            pivot
            (
              max(value)
              for [Month] in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

The result will be the same as the dynamic version.

UNION ALL/CASE with aggregate:

Lastly, if you do not have access to either the UNPIVOT or PIVOT functions, then you can use a UNION ALL to unpivot and an aggregate function with a CASE to pivot the data:

select col,
  max(case when month='January-2011' then value end) [January-2011],
  max(case when month='February-2011' then value end) [February-2011],
  max(case when month='March-2011' then value end) [March-2011],
  max(case when month='April-2011' then value end) [April-2011],
  max(case when month='January-2012' then value end) [January-2012],
  max(case when month='February-2012' then value end) [February-2012],
  max(case when month='March-2012' then value end) [March-2012]
from
(
  select DateName(month,[Month]) +'-'+Cast(datepart(year, [month]) as varchar(4)) Month,
    [Affec] value,
    'Affec' col
  from yourtable
  union all
  select DateName(month,[Month]) +'-'+Cast(datepart(year, [month]) as varchar(4)) Month,
    [KPI] value,
    'KPI' col
  from yourtable
  union all
  select DateName(month,[Month]) +'-'+Cast(datepart(year, [month]) as varchar(4)) Month,
    [Total] value,
    'Total' col
  from yourtable
  union all
  select DateName(month,[Month]) +'-'+Cast(datepart(year, [month]) as varchar(4)) Month,
    [KPI_%] value,
    'KPI_%' col
  from yourtable
  union all
  select DateName(month,[Month]) +'-'+Cast(datepart(year, [month]) as varchar(4)) Month,
    [rep_in_10] value,
    'rep_in_10' col
  from yourtable
  union all
  select DateName(month,[Month]) +'-'+Cast(datepart(year, [month]) as varchar(4)) Month,
    [ftm] value,
    'ftm' col
  from yourtable
) src
group by col

See SQL Fiddle with Demo

thank you both i tried both of your solutions and all of them worked perfectly but i prefer using the dynamic Version it suit my code perfectly and now i understand how the pivot thing work — Seif sammain, Dec 10 '12 at 10:55

How can I cross tab this table

3 Answers3

SQL Fiddle Demo