Convert Column values of a single row into 2 columns with the column headers in SQL Server

user_11361775 0
Viewed 330

I need to convert Column and a row into 2 columns. Below is the sample Table [Table01] with 4 columns and a single row.

First   Second  Third   Forth
01      02      03      04

I need to Transpose it as a new table [Table01_T]

Column01     Column02
First         01
Second        02
Third         03
Forth         04

Every time we receive a single row with 4 columns and we need them to convert in 2 columns and four rows.

sql-server
2 Answers

Something like this

select
  v.*
from [Table01]
 cross apply
  (values ('First', [First]),('Second',[Second]),
          ('Third',[Third]),('Forth',[Forth])) v(Column01, Column02);

Using the same sample data this query produces the same results. I find this syntax to be more readable then UNPIVOT.

Results

Column1 Column02
First   01
Second  02
Third   03
Forth   04
user_67487580

You can use UNPIVOT:

SELECT Column01, Column02
  -- INTO dbo.Table01_T
  FROM dbo.Table01 
  UNPIVOT 
    (                         -- vvvvv column names you want to turn to rows
      Column02 FOR Column01 IN ([First],[Second],[Third],[Fourth])
  --               ^^^^^^^^ becomes column name from each original column 
  --  ^^^^^^^^ becomes column value from each original column
    ) AS u;

Isolated example using a table variable:

DECLARE @x table([First] char(2),Second char(2),Third char(2),Fourth char(2));
INSERT @x([First],[Second],[Third],[Fourth]) VALUES ('01','02','03','04');

SELECT Column01, Column02
  FROM @x UNPIVOT 
  (
    Column02 FOR Column01 IN ([First],[Second],[Third],[Fourth])
  ) AS u;

Results:

Column01    Column02
--------    --------
First       01
Second      02
Third       03
Fourth      04

Working fiddle

user_613050
Related