SQL Server: Column to Row

SQL Server : Columns to Rows

You can use the UNPIVOT function to convert the columns into rows:

select id, entityId,
  indicatorname,
  indicatorvalue
from yourtable
unpivot
(
  indicatorvalue
  for indicatorname in (Indicator1, Indicator2, Indicator3)
) unpiv;

Note, the datatypes of the columns you are unpivoting must be the same so you might have to convert the datatypes prior to applying the unpivot.

You could also use CROSS APPLY with UNION ALL to convert the columns:

select id, entityid,
  indicatorname,
  indicatorvalue
from yourtable
cross apply
(
  select 'Indicator1', Indicator1 union all
  select 'Indicator2', Indicator2 union all
  select 'Indicator3', Indicator3 union all
  select 'Indicator4', Indicator4 
) c (indicatorname, indicatorvalue);

Depending on your version of SQL Server you could even use CROSS APPLY with the VALUES clause:

select id, entityid,
  indicatorname,
  indicatorvalue
from yourtable
cross apply
(
  values
  ('Indicator1', Indicator1),
  ('Indicator2', Indicator2),
  ('Indicator3', Indicator3),
  ('Indicator4', Indicator4)
) c (indicatorname, indicatorvalue);

Finally, if you have 150 columns to unpivot and you don't want to hard-code the entire query, then you could generate the sql statement using dynamic SQL:

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

select @colsUnpivot 
  = stuff((select ','+quotename(C.column_name)
           from information_schema.columns as C
           where C.table_name = 'yourtable' and
                 C.column_name like 'Indicator%'
           for xml path('')), 1, 1, '')

set @query 
  = 'select id, entityId,
        indicatorname,
        indicatorvalue
     from yourtable
     unpivot
     (
        indicatorvalue
        for indicatorname in ('+ @colsunpivot +')
     ) u'

exec sp_executesql @query;

Simple way to transpose columns and rows in SQL?

There are several ways that you can transform this data. In your original post, you stated that PIVOT seems too complex for this scenario, but it can be applied very easily using both the UNPIVOT and PIVOT functions in SQL Server.

However, if you do not have access to those functions this can be replicated using UNION ALL to UNPIVOT and then an aggregate function with a CASE statement to PIVOT:

Create Table:

CREATE TABLE yourTable([color] varchar(5), [Paul] int, [John] int, [Tim] int, [Eric] int);

INSERT INTO yourTable
    ([color], [Paul], [John], [Tim], [Eric])
VALUES
    ('Red', 1, 5, 1, 3),
    ('Green', 8, 4, 3, 5),
    ('Blue', 2, 2, 9, 1);

Union All, Aggregate and CASE Version:

select name,
  sum(case when color = 'Red' then value else 0 end) Red,
  sum(case when color = 'Green' then value else 0 end) Green,
  sum(case when color = 'Blue' then value else 0 end) Blue
from
(
  select color, Paul value, 'Paul' name
  from yourTable
  union all
  select color, John value, 'John' name
  from yourTable
  union all
  select color, Tim value, 'Tim' name
  from yourTable
  union all
  select color, Eric value, 'Eric' name
  from yourTable
) src
group by name

See SQL Fiddle with Demo

The UNION ALL performs the UNPIVOT of the data by transforming the columns Paul, John, Tim, Eric into separate rows. Then you apply the aggregate function sum() with the case statement to get the new columns for each color.

Unpivot and Pivot Static Version:

Both the UNPIVOT and PIVOT functions in SQL server make this transformation much easier. If you know all of the values that you want to transform, you can hard-code them into a static version to get the result:

select name, [Red], [Green], [Blue]
from
(
  select color, name, value
  from yourtable
  unpivot
  (
    value for name in (Paul, John, Tim, Eric)
  ) unpiv
) src
pivot
(
  sum(value)
  for color in ([Red], [Green], [Blue])
) piv

See SQL Fiddle with Demo

The inner query with the UNPIVOT performs the same function as the UNION ALL. It takes the list of columns and turns it into rows, the PIVOT then performs the final transformation into columns.

Dynamic Pivot Version:

If you have an unknown number of columns (Paul, John, Tim, Eric in your example) and then an unknown number of colors to transform you can use dynamic sql to generate the list to UNPIVOT and then PIVOT:

DECLARE @colsUnpivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @colsPivot as  NVARCHAR(MAX)

select @colsUnpivot = stuff((select ','+quotename(C.name)
         from sys.columns as C
         where C.object_id = object_id('yourtable') and
               C.name <> 'color'
         for xml path('')), 1, 1, '')

select @colsPivot = STUFF((SELECT  ',' 
                      + quotename(color)
                    from yourtable t
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
  = 'select name, '+@colsPivot+'
      from
      (
        select color, name, value
        from yourtable
        unpivot
        (
          value for name in ('+@colsUnpivot+')
        ) unpiv
      ) src
      pivot
      (
        sum(value)
        for color in ('+@colsPivot+')
      ) piv'

exec(@query)

See SQL Fiddle with Demo

The dynamic version queries both yourtable and then the sys.columns table to generate the list of items to UNPIVOT and PIVOT. This is then added to a query string to be executed. The plus of the dynamic version is if you have a changing list of colors and/or names this will generate the list at run-time.

All three queries will produce the same result:

| NAME | RED | GREEN | BLUE |
-----------------------------
| Eric |   3 |     5 |    1 |
| John |   5 |     4 |    2 |
| Paul |   1 |     8 |    2 |
|  Tim |   1 |     3 |    9 |

How to convert columns to rows in sql server

Use UNPIVOT. Try something like:

SELECT ID, Page, Line, City, Value
FROM SourceTable
UNPIVOT
   (Value FOR City IN 
      (C01, C02, C03)
)AS unpvt;

Where 'SourceTable' is your source table name. (Note: I can't test this at the moment, so it may not be exactly right.)

Full details here: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

SQL Query Convert columns to rows

You could use UNPIVOT to get the desired result also:

   SELECT
    *
FROM
    (
        SELECT
            SUM(cases)      AS total_cases,
            SUM(deaths)     AS total_deaths
        FROM
            myschema.metrics
    ) UNPIVOT ( value
        FOR category
    IN ( total_cases,
         total_deaths ) );
     

The output of the above will be:

Category                Value
Total_cases             1234
Total_deaths            123

How to transform columns to rows in SQL Server

Judging by your column names I suspect the source data will expand over time

Here is a method where you can dynamically unpivot your data without actually using dynamic SQL

Example

Select A.[Material]
      ,B.* 
 From  YourTable A
 Cross Apply  (
                 Select [Key]
                       ,Value
                 From OpenJson(  (Select A.* For JSON Path,Without_Array_Wrapper )  ) 
                 Where [Key] not in ('Material','OtherColumns','ToExclude')
              ) B

Results

Material        Key         Value
01-298450-02    Past        0
01-298450-02    WK1_05_23   1
01-298450-02    WK2_05_30   2
01-298450-02    WK3_06_06   3

How to move columns to rows in a SQL Server select?

You need to "unpivot" which I prefer to do using cross apply and values, like this

SELECT
    row_number() over (order by SECTION_NAME, SORT_ORDER, ca.value) as ID
  , section_name
  , sort_order
  , ca.value
FROM pivoted
CROSS APPLY (
    VALUES 
                (col1)
              , (col2)
              , (col3)
              , (col4)
              , (col5)
              , (col6)
              , (col7)
              , (col8)
              , (col9)
              , (col10)
            ) AS ca(value)

which produces:

+----+--------------+------------+-------+
| ID | section_name | sort_order | value |
+----+--------------+------------+-------+
|  1 | ONE          |          1 | A     |
|  2 | ONE          |          1 | B     |
|  3 | ONE          |          1 | C     |
|  4 | ONE          |          1 | D     |
|  5 | ONE          |          1 | E     |
|  6 | ONE          |          1 | F     |
|  7 | ONE          |          1 | G     |
|  8 | ONE          |          1 | H     |
|  9 | ONE          |          1 | I     |
| 10 | ONE          |          1 | J     |
| 11 | ONE          |          2 | C     |
| 12 | ONE          |          2 | D     |
| 13 | ONE          |          2 | E     |
| 14 | ONE          |          2 | F     |
| 15 | ONE          |          2 | G     |
| 16 | ONE          |          2 | H     |
| 17 | ONE          |          2 | I     |
| 18 | ONE          |          2 | X     |
| 19 | ONE          |          2 | Y     |
| 20 | ONE          |          2 | Z     |
+----+--------------+------------+-------+

see this demo

---

Related Topics