Convert Data from Wide Format to Long Format in Sql

Convert data from wide format to long format in SQL

Provided your score columns are fixed and you require no aggregation, you can use multiple SELECT and UNION ALL statements to generate the shape of data you requested. E.g.

SELECT [VAR1], [VAR2], [VarName] = 'Score1', [Value] = [Score1]
FROM [dbo].[UnknownMe]
UNION ALL
SELECT [VAR1], [VAR2], [VarName] = 'Score2', [Value] = [Score2]
FROM [dbo].[UnknownMe]
UNION ALL
SELECT [VAR1], [VAR2], [VarName] = 'Score3', [Value] = [Score3]
FROM [dbo].[UnknownMe]

SQL Fiddle: http://sqlfiddle.com/#!6/f54b2/4/0

Convert wide format data to long format using SQL

You can use a lateral join:

select t.id, v.*
from t cross join lateral
     (values ('a', pg_a), ('b', pg_b)
     ) v(pg, value);

If you are new to SQL, you might you might be more comfortable with union all:

select id, 'a' as pg, pg_a as value
from t
union all
select id, 'b', pg_b
from t;

Mysql, reshape data from long / tall to wide

Cross-tabs or pivot tables is the answer. From there you can SELECT FROM ... INSERT INTO ... or create a VIEW from the single SELECT.

Something like:

SELECT country, 
       MAX( IF( key='President', value, NULL ) ) AS President,
       MAX( IF( key='Currency', value, NULL ) ) AS Currency,
       ...

FROM table 
GROUP BY country;

Converting wide to long format in sql server and dataframe has around 1000 columns - Optimize

Clearly Unpivot would be more performant, but here is a approach that will "dynamically" unpivot your data without having to use dynamic SQL.

The plus side is that you won't have to specify 1000 columns.

Example

Select A.Date
      ,C.*
 From  YourTable A
 Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)
 Cross Apply (
                Select Item  = xAttr.value('local-name(.)', 'varchar(100)')
                      ,Value = xAttr.value('.','varchar(max)')
                 From  XMLData.nodes('//@*') xNode(xAttr)
                 Where xAttr.value('local-name(.)','varchar(100)') not in ('Date')
             ) C

Returns

EDIT

 ...
,Item  = replace(xAttr.value('local-name(.)', 'varchar(100)'),'_x0020_',' ')
 ...

MS Access From Wide Format to Long Format New variable Union ALL

You are close

SELECT TABL1.ID, TABL1.HDD, 0 AS CDD, HDDKEY as [Key]
FROM TABL1
WHERE ID = 186
UNION ALL
SELECT TABL1.ID, 0 AS HDD, TABL1.CDD, CDDKEY as [Key]
FROM TABL1
WHERE ID = 186;

Note that KEY is a poor name for a column because it is a SQL keyword.

Using PIVOT to Flip Data from Wide to Tall

You can unpivot the data using CROSS APPLY (VALUES). Here is an article to explain how this is done:

http://www.sqlservercentral.com/articles/CROSS+APPLY+VALUES+UNPIVOT/91234/

Basically the code is:

SELECT vend,
  year,
  month,
  dols, 
  qty
FROM YourTable t
CROSS APPLY 
(
    VALUES
        (1, I1_DOLS, I1_QTY),
        (2, I2_DOLS, I2_QTY),
        (3, I3_DOLS, I3_QTY)
) x (month, dols, qty);

See SQL Fiddle with Demo

Or you could use a UNION ALL query:

select vend, year, 1 month, [I1_DOLS] Dols, [I1_QTY] Qty
from yourtable
union all
select vend, year, 2 month, [I2_DOLS] Dols, [I2_QTY] Qty
from yourtable
union all
select vend, year, 3 month, [I3_DOLS] Dols, [I3_QTY] Qty
from yourtable

See SQL Fiddle with Demo

Or you can even apply both the UNPIVOT and the PIVOT function to transform the data:

select *
from
(
  select vend,
    year,
    replace(replace(replace(col, 'I', ''), '_Dols', ''), '_Qty', '') month,
    case when col like '%Dols%' then 'dols' else 'qty' end col_name,
    value
  from 
  (
    select vend, year, [I1_DOLS], [I1_QTY], [I2_DOLS], [I2_QTY], [I3_DOLS], [I3_QTY]
    from yourtable
  ) src
  unpivot
  (
    value
    for col in ([I1_DOLS], [I1_QTY], [I2_DOLS], [I2_QTY], [I3_DOLS], [I3_QTY])
  ) un
) unp
pivot
(
  max(value)
  for col_name in (dols, qty)
) piv

See SQL Fiddle with Demo.

All three will give the same result:

| VEND | YEAR | MONTH |   DOLS | QTY |
--------------------------------------
| 1234 | 2011 |     1 | 101587 | 508 |
| 1234 | 2011 |     2 | 203345 | 334 |
| 1234 | 2011 |     3 | 105938 | 257 |
| 1234 | 2012 |     1 | 257843 | 587 |
| 1234 | 2012 |     2 | 235883 | 247 |
| 1234 | 2012 |     3 | 178475 | 456 |
| 1011 | 2010 |     1 | 584737 | 432 |
| 1011 | 2010 |     2 | 587274 | 356 |
| 1011 | 2010 |     3 | 175737 | 563 |
| 1011 | 2011 |     1 | 517774 | 356 |
| 1011 | 2011 |     2 | 483858 | 456 |
| 1011 | 2011 |     3 | 481785 | 354 |

Mysql convert table from long format to wide format

Try this:

insert into goodTable 
select
bt.id,
(select bt1.value from badTable bt1 where bt1.info = 'firstname' and bt1.id = bt.id),
(select bt1.value from badTable bt1 where bt1.info = 'lastname' and bt1.id = bt.id),
(select bt1.value from badTable bt1 where bt1.info = 'phone' and bt1.id = bt.id)
from
 badTable bt
group by id ;

Working fiddle here: http://sqlfiddle.com/#!2/45f29e/2

Transforming a table from long format to wide format in SQL

You don't want union. You want to pivot the data. One method uses conditional aggregation:

select id,
       max(case when key = 'users' then value end) as users,
       max(case when key = 'politics' then value end) as politics
from t
group by id;

---

Related Topics