CODEWITHSUNDEEP
sql-serversql-server-2008t-sqlpivotunpivot
Matte
Matte

Reputation: 315

T-SQL: from rows to columns but not an actual pivot

Is there a T-SQL (SQL Server 2008R2) query to transform TABLE_1 into the expected resultset?

TABLE_1

+----------+-------------------------+---------+------+
| IdDevice | Timestamp               | M300    | M400 |
+----------+-------------------------+---------+------+
| 3        | 2012-12-05 16:29:51.000 | 2357,69 | 520  |
| 6        | 2012-12-05 16:29:51.000 | 1694,81 | 470  |
| 1        | 2012-12-05 16:29:51.000 | 2046,33 | 111  |
+----------+-------------------------+---------+------+

Expected resultset

+-------------------------+---------+--------+---------+--------+---------+--------+
|        Timestamp        | 3_M300  | 3_M400 | 6_M300  | 6_M400 | 6_M300  | 6_M400 |
+-------------------------+---------+--------+---------+--------+---------+--------+
| 2012-12-05 16:29:51.000 | 2357,69 |    520 | 1694,81 |    470 | 2046,33 |    111 |
+-------------------------+---------+--------+---------+--------+---------+--------+

Upvotes: 1

Views: 265

Answers (1)

Taryn
Taryn

Reputation: 247710

This is still a PIVOT query but before you PIVOT you must perform an UNPIVOT of your columns.

First, you perform the UNPIVOT which takes your current multiple columns and transforms them into two columns - one with the value and the other with the column name. The key for the UNPIVOT is that the datatypes be the same, so in the subquery I cast any columns to the same datatype: 

select timestamp,
    value, cast(iddevice as varchar(10)) + '_'+col as col
  from
  (
    select iddevice,
      timestamp,
      cast(m300 as varchar(10)) m300,
      cast(m400 as varchar(10)) m400
    from yourtable
  ) src
  unpivot
  (
    value
    for col in (m300, m400)
  ) unpiv

See SQL Fiddle with Demo

Result:

|                       TIMESTAMP |   VALUE |    COL |
------------------------------------------------------
| December, 05 2012 16:29:51+0000 | 2357,69 | 3_m300 |
| December, 05 2012 16:29:51+0000 |     520 | 3_m400 |
| December, 05 2012 16:29:51+0000 | 1694,81 | 6_m300 |
| December, 05 2012 16:29:51+0000 |     470 | 6_m400 |
| December, 05 2012 16:29:51+0000 | 2046,33 | 1_m300 |
| December, 05 2012 16:29:51+0000 |     111 | 1_m400 |

Once you complete the unpivot, then you can apply the PIVOT function:

select *
from
(
  select timestamp,
    value, cast(iddevice as varchar(10)) + '_'+col as col
  from
  (
    select iddevice,
      timestamp,
      cast(m300 as varchar(10)) m300,
      cast(m400 as varchar(10)) m400
    from yourtable
  ) src
  unpivot
  (
    value
    for col in (m300, m400)
  ) unpiv
) src1
pivot
(
  max(value)
  for col in ([3_m300], [3_m400],
              [6_m300], [6_m400],
              [1_m300], [1_m400])
) piv

See SQL Fiddle with Demo

Results:

|                       TIMESTAMP |  3_M300 | 3_M400 |  6_M300 | 6_M400 |  1_M300 | 1_M400 |
--------------------------------------------------------------------------------------------
| December, 05 2012 16:29:51+0000 | 2357,69 |    520 | 1694,81 |    470 | 2046,33 |    111 |

If you have an unknown number of IdDevices that you want to transform into columns, then you can use dynamic SQL:

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

select @cols = STUFF((SELECT DISTINCT ',' 
                      + quotename(cast(t.IdDevice as varchar(10)) +'_'
                                  +c.name)
                    from yourtable t
                     cross apply sys.columns as C
                   where C.object_id = object_id('yourtable') and
                         C.name not in ('IdDevice', 'Timestamp')
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


set @query = 'SELECT timestamp,' + @cols + ' from 
             (
               select timestamp,
                value, cast(iddevice as varchar(10)) + ''_''+col as col
              from
              (
                select iddevice,
                  timestamp,
                  cast(m300 as varchar(10)) m300,
                  cast(m400 as varchar(10)) m400
                from yourtable
              ) src
              unpivot
              (
                value
                for col in (m300, m400)
              ) unpiv
            ) x
            pivot
            (
              max(value)
              for col in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

Edit, if you need a totals field for each m value, then you can use:

select timestamp,
  [3_m300], [3_m400],
  [6_m300], [6_m400],
  [1_m300], [1_m400],
  [1_m300] + [3_m300] + [6_m300] Total_m300,
  [1_m400] + [3_m400] + [6_m400] Total_m400
from
(
  select timestamp,
    value, cast(iddevice as varchar(10)) + '_'+col as col
  from
  (
    select iddevice,
      timestamp,
      m300,
      m400
    from yourtable
  ) src
  unpivot
  (
    value
    for col in (m300, m400)
  ) unpiv
) src1
pivot
(
  sum(value)
  for col in ([3_m300], [3_m400],
              [6_m300], [6_m400],
              [1_m300], [1_m400])
) piv

See SQL Fiddle with Demo

Upvotes: 2

Related Questions