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  

Answer 1

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

Answer 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.