SQL Transpose Rows as Columns

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 |

SQL Transpose row to columns

You need to enumerate the values to pivot them:

select id,
       max(case when seqnum = 1 then club end) as club_1,
       max(case when seqnum = 2 then club end) as club_2,
       max(case when seqnum = 3 then club end) as club_3
from (select b.*,
             row_number() over (partition by id order by club) as seqnum
      from b
     ) b
group by id;

How to convert or transpose rows to columns in SQL without using pivot?

Try STRING_AGG instead of any_value:

select id, string_agg(if(source_name = 'cp', value, null)) as cp,
string_agg(if(source_name = 'hi', value, null)) as hi,
string_agg(if(source_name = 'li', value, null)) as li
string_agg(if(source_name = 'mi', value, null)) as mi
from table_name group by id

Pivot/transpose rows into columns efficiently with multiple columns

SELECT
  [Num1], 
  [Type1], 
  [Code], 
  [Group], 
  [DA], 
  [123],
  [234]
FROM
  yourTable
PIVOT
(
  MAX([value])  
  FOR [account] IN ([123], [234])
)
  AS PivotTable

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=7fbe16b9254aa5ee60a23e43eec9597f

How to transpose ROW to Column in DB2

Short answer is you can't.

There's nothing in Db2 for IBM i that will do this with SELECT * and a dynamic table.

Long answer, you can build a stored procedure or user defined table function that dynamically builds and executes an old school statement that looks like so:

with firstRow as 
  (select F1, F2, F3 from table fetch first row only)
select F1
from firstRow
UNION ALL 
select F2
from firstRow
UNION ALL
select F3
from firstRow;

Alternately, since you're on v7.4, you could build and execute a dynamic statement that CONCAT the fields into string list and then use the SPLIT() table function to deconstruct the the list into rows.

Lastly, you might be able to build and execute a dynamic statement that uses the JSON functions to build a JSON array which could then be deconstructed into rows with the JSON_TABLE() function.

But as emphasized, in all cases you'll need to know column and table names for the actual SELECT. Thus the need to dynamically build the statement.

SQL Server : how to transpose rows into columns

If you have a limited machines, then i would do conditional aggregation instead of PIVOT:

SELECT Project,
       MAX(CASE WHEN machine = 'X' THEN cost END) AS MX,
       MAX(CASE WHEN machine = 'Y' THEN cost END) AS MY,
       MAX(CASE WHEN machine = 'Z' THEN cost END) AS MZ,
       MAX(CASE WHEN machine = 'W' THEN cost END) AS MW
FROM Company c
GROUP BY Project;

If you want to go with PIVOT, then i would fix the syntax error with missing closing parenthesis :

SELECT * 
FROM (SELECT project, machine, cost 
      FROM Company 
     ) AS p PIVOT
     (MAX(cost)
      FOR machine IN ([X], [Y], [Z], [W]) 
     ) AS pvt;

Note :

• Don't use single quote as column name use square brackets instead.

---

Related Topics