Convert Columns into Rows in SQL Server

Question

I have a table called Dateinfo and the data looks like below:

Cntr_Date   Appv_Date   Prop_Date     ID    Lvl
------------------------------------------------
2014-04-11  2014-03-21  2014-07-29    4867  1
2014-04-21  2014-04-29  2014-04-21    4867  1a

I want my output to look like this:

ID      Lvl    DT_Type      Dates_Hist
---------------------------------------
4867    1      Cntr         2014-04-11
4867    1      Appv         2014-03-21
4867    1      Prop         2014-07-29
4867    1a     Cntr         2014-04-21
4867    1a     Appv         2014-04-29
4867    1a     Prop         2014-04-21

Where is the DT_Type and Dates_Hist columns coming from? Are they part of the same table? — Tiny Haitian
– Tiny Haitian, Commented Aug 13, 2014 at 19:18

M.Ali · Accepted Answer · 2014-08-13 19:54:09Z

2

Test Data

DECLARE @TABLE 
   TABLE(Cntr_Date DATE, Appv_Date DATE, Prop_Date DATE, ID INT, Lvl VARCHAR(10))

INSERT INTO @TABLE VALUES 
('2014-04-11','2014-03-21','2014-07-29',4867,'1'),
('2014-04-21','2014-04-29','2014-04-21',4867,'1a')

Query

SELECT  ID
      , Lvl
      , LEFT(DT_Type, CHARINDEX('_',DT_Type)-1) AS DT_Type 
      , Date_Hist
FROM @TABLE
 UNPIVOT ( Date_Hist FOR 
           DT_Type IN (Cntr_Date, Appv_Date, Prop_Date) 
         ) UP

Result

╔══════╦═════╦═════════╦════════════╗
║  ID  ║ Lvl ║ DT_Type ║ Date_Hist  ║
╠══════╬═════╬═════════╬════════════╣
║ 4867 ║ 1   ║ Cntr    ║ 2014-04-11 ║
║ 4867 ║ 1   ║ Appv    ║ 2014-03-21 ║
║ 4867 ║ 1   ║ Prop    ║ 2014-07-29 ║
║ 4867 ║ 1a  ║ Cntr    ║ 2014-04-21 ║
║ 4867 ║ 1a  ║ Appv    ║ 2014-04-29 ║
║ 4867 ║ 1a  ║ Prop    ║ 2014-04-21 ║
╚══════╩═════╩═════════╩════════════╝

Update

For column names with or without Underscore you can hardcode the values inside a case statement, something as follows

SELECT  ID
      , Lvl
      , CASE DT_Type
          WHEN 'Cntr_Date' THEN 'Cntr' 
          WHEN 'Appv_Date' THEN 'Appv'
          WHEN 'Prop_Date' THEN 'Prop'
         END AS DT_Type
      , Date_Hist
FROM @TABLE
 UNPIVOT ( Date_Hist FOR 
           DT_Type IN (Cntr_Date, Appv_Date, Prop_Date) 
         ) UP

edited Aug 13, 2014 at 19:54

answered Aug 13, 2014 at 19:19

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

peter Over a year ago

Thank you Ali Just one question what can we do to have custom value or name in DT_type like in above example i want cntr_date to be cntr, appv_date to be appv

peter Over a year ago

as long as the format is same it works but if were to give alias it doesn't work right?

M.Ali Over a year ago

I have just done exactly what you asked for, what alias you are asking about?

Lamak Over a year ago

Op is saying that even though in this case the values for DT_Type are the ones s/he wants, it's only because the column names have a specific format, but s/he couldn't give just any value to those if s/he wanted

M.Ali Over a year ago

@peter Alright ok, well as long as a column name has an undersocre it will pull the first Part of the column names before underscore, with this specific query.

|

Lamak · Accepted Answer · 2014-08-13 19:22:42Z

2

Another option aside of UNPIVOT and using UNION ALL is CROSS APPLY:

SELECT  t.id,
        t.Lvl,
        x.*
FROM YourTable t
CROSS APPLY 
(
    VALUES
        ('Cntr', t.Cntr_Date),
        ('Appv', t.Appv_Date),
        ('Prop', t.Prop_Date)
) x (DT_Type, Dates_Hist);

Here is a sqlfiddle with a demo.

answered Aug 13, 2014 at 19:22

Lamak

70.8k12 gold badges119 silver badges119 bronze badges

Collectives™ on Stack Overflow

Convert Columns into Rows in SQL Server

2 Answers 2

Test Data

Query

Result

Update

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Test Data

Query

Result

Update

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related