Transpose a Row into Columns with MySQL Without Using Unions

Transpose a row into columns with MySQL without using UNIONS?

I got this out of the book The Art of SQL, pages 284-286:

Let's say your table name is foo.

First, create a table called pivot:

CREATE Table pivot (
  count int
);

Insert into that tables as many rows as there are columns that you want to pivot in foo. Since you have three columns in foo that you want to pivot, create three rows in the pivot table:

insert into pivot values (1);
insert into pivot values (2);
insert into pivot values (3);

Now do a Cartesian join between foo and pivot, using a CASE to select the correct column based on the count:

SELECT foo.id, Case pivot.count
  When 1 Then cat
  When 2 Then one_above
  When 3 Then top_level
End Case
FROM foo JOIN pivot;

This should give you what you want.

Transpose mysql query rows into columns

You can do it with a crosstab like this -

SELECT
    `year`,
    `month`,
    SUM(IF(`transporttype` = 'inbound',                 1, 0)) AS `inbound`,
    SUM(IF(`transporttype` = 'LocalPMB',                1, 0)) AS `LocalPMB`,
    SUM(IF(`transporttype` = 'Long Distance',           1, 0)) AS `Long Distance`,
    SUM(IF(`transporttype` = 'shuttle',                 1, 0)) AS `shuttle`,
    SUM(IF(`transporttype` = 'export',                  1, 0)) AS `export`,
    SUM(IF(`transporttype` = 'Extrusions-LongDistance', 1, 0)) AS `Extrusions-LongDistance`,
    SUM(IF(`transporttype` = 'Extrusions-Shuttle',      1, 0)) AS `Extrusions-Shuttle`
FROM `deliveries`
GROUP BY `year`, `month`

On a different note, you should move transporttype values to a lookup table and have transporttype_id in this table.

How to convert row into columns in SQL?

You want to unpivot the data. The challenge is dealing with the datatypes. You need to convert them all to the same type. Presumably, this only applies to amount and perhaps to accountnumber:

select firstName as anyName from t
union all
select cast(Amount as char) from t
union all
select PostalCode from t
union all
select LastName from t
union all
select cast(AccountNumber as char) from t;

If your table is very large or is really a complicated view, then there are other methods that don't require scanning the table once for each column.

You can also use cross join and case:

select (case when n.n = 1 then firstName
             when n.n = 2 then cast(Amount as char)
             when n.n = 3 then PostalCode
             when n.n = 4 then lastName
             when n.n = 5 then cast(AccountNumber as char)
        end) as anyName
from t cross join
    (select 1 as n union all select 2 union all select 3 union all select 4 union all select 5
      ) n

Transposing Dynamic Columns to Rows

MySQL does not have an UNPIVOT function, but you can convert your columns into rows using a UNION ALL.

The basic syntax is:

select id, word, qty
from
(
  select id, 'abc' word, abc qty
  from yt
  where abc > 0
  union all
  select id, 'brt', brt
  from yt
  where brt > 0
) d
order by id;

In your case, you state that you need a solution for dynamic columns. If that is the case, then you will need to use a prepared statement to generate dynamic SQL:

SET @sql = NULL;

SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'select id, ''',
      c.column_name,
      ''' as word, ',
      c.column_name,
      ' as qty 
      from yt 
      where ',
      c.column_name,
      ' > 0'
    ) SEPARATOR ' UNION ALL '
  ) INTO @sql
FROM information_schema.columns c
where c.table_name = 'yt'
  and c.column_name not in ('id')
order by c.ordinal_position;

SET @sql 
  = CONCAT('select id, word, qty
           from
           (', @sql, ') x  order by id');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

See SQL Fiddle with Demo

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 |

MySQL transpose columns sum into rows with column names as a value

This can be a possible solution:

SELECT 'A' as `codes`, SUM(`A`) as `sums` FROM `your_table_name`
UNION ALL
SELECT 'B1' as `codes`, SUM(`B1`) as `sums` FROM `your_table_name`
UNION ALL
SELECT 'B2' as `codes`, SUM(`B2`) as `sums` FROM `your_table_name`

SQL/MYSQL /Transpose table / Columns into rows and rows sum as column with new header

This should fairly give you some ideas. Supposing we are using a test database named testdb and your original table is named test which has 3 columns i.e a,b,c . The three rows in the table are just like what you provided before. Next we can proceed to create a stored procedure. Note: The reason behind using a prepared statement to get the sum value for each column is due to the rules that column names have to be hardcoded , which can not be replaced with variables. e.g select sum(a) from test; can not be written as select sum(@column_name) from test;. By using a prepared statement, we can hardcode the column name dynamically.

delimiter //
drop procedure if exists table_sum//
create procedure table_sum (db_name varchar(20),tb_name varchar(20))
begin
declare col_name varchar(10);
declare fin bool default false;
declare c cursor for select column_name from information_schema.columns where table_schema=db_name and table_name=tb_name;
declare continue handler for not found set fin=true;
drop temporary table if exists result_tb;
create temporary table result_tb (`Columns` varchar(10),`Values` varchar(25));
open c;
lp:loop

    fetch c into col_name;
    if fin=true then
        leave lp;
    end if;
    set @stmt=concat('select sum(',col_name,') into @sum from test ;'); 
    prepare stmt from @stmt;
    execute stmt;
    deallocate prepare stmt;
    
    set @val=concat('sum(',col_name,') = ',@sum);
    insert result_tb values(col_name,@val);

end loop lp;

close c;
select * from result_tb;
end//

delimiter ;

Finally we call the procedure to get the desired output:

call table_sum('testdb','test'); 

transpose column to row in mysql

If there are only two values, you can use min() and max():

select pid, min(phone) as phone_1,
       (case when min(phone) <> max(phone) then max(phone) end) as phone_2
from t
group by pid;

---

Related Topics