Rotate table in T-SQL

Question

I have a table that contains sequential date in first column and type of date (CreatedOn and ClosedOn). I need to with SELECT that has 2 columns (CreatedOn, ClosedOn) from my table.
I have this:

| Date       | ColumnName |
|------------|------------|
| 2017-01-01 | ClosedOn   |
| 2017-01-02 | CreatedOn  |
| 2017-01-03 | ClosedOn   |
| 2017-01-04 | CreatedOn  |

And I need to get this:

| CreatedOn  | ClosedOn   |
|------------|------------|
| NULL       | 2017-01-01 |
| 2017-01-02 | 2017-01-03 |
| 2017-01-04 | NULL       |

I've tried this:

SELECT
    CASE [ColumnName]
        WHEN 'CreatedOn' THEN [Date]
        ELSE NULL
    END,
    CASE [ColumnName]
        WHEN 'ClosedOn' THEN [Date]
        ELSE NULL
    END
FROM #Temp

but it doesn't work.

Answer 1

Try this and hope it helps. You may have to test it and modify as needed. But the logic if my understanding is correct should be sufficient to build on.

;WITH cte_TestData(Date,ColumnName) AS
(
SELECT '2017-01-01','ClosedOn ' UNION ALL
SELECT '2017-01-02','CreatedOn' UNION ALL
SELECT '2017-01-03','ClosedOn ' UNION ALL
SELECT '2017-01-04','CreatedOn'
)
,cte_PreserveSeq AS
(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS SeqID,Date,ColumnName
FROM cte_TestData
)
,cte_PreResult AS
(
SELECT *
    ,LEAD  (ColumnName, 1,0) OVER (ORDER BY SeqID) AS NextColumnName
    ,LEAD  (Date, 1,0) OVER (ORDER BY SeqID) AS NextDate
    ,LAG  (ColumnName, 1,0) OVER (ORDER BY SeqID) AS PreviousColumnName
    ,LAG  (Date, 1,0) OVER (ORDER BY SeqID) AS PreviousDate
FROM cte_PreserveSeq
)
SELECT DISTINCT
    CASE 
        WHEN ColumnName = 'CreatedOn' AND NextColumnName = 'ClosedOn' THEN DATE
        WHEN ColumnName = 'ClosedOn' AND PreviousColumnName = 'CreatedOn' THEN PreviousDate
        WHEN ColumnName = 'CreatedOn' THEN DATE
        ELSE NULL
        END AS CreatedOn,
    CASE 
        WHEN ColumnName = 'CreatedOn' AND NextColumnName = 'ClosedOn' THEN NextDate
        WHEN ColumnName = 'ClosedOn' THEN DATE
        ELSE NULL
        END AS ClosedOn
FROM cte_PreResult

Answer 2

I'm assuming you have other columns, so let's do this

select A1.OtherColumn, A1.[Date], A2.Date
from #Temp A1
full outer join #Temp A2
  on A1.OtherColumn = A2.OtherColumn
  and A1.ColumnName = 'CreatedOn'
  and A2.ColumnName = 'ClosedOn'

EDIT: If no other columns, try

with MyData as
(
select [Date], ColumnName , row_number() over (order by [Date], ColumnName desc) as rn
from #Temp
)
select M1.[Date], M2.[Date]
from MyData M1
full outer join MyData M2
  on M2.rn = M1.rn + 1
  and mod(M1.rn, 2) = 1

Answer 3

This is a typical case of using a PIVOT in SQL Server, to transpose rows into columns:

select *
from table1 
pivot (max(colname) for colname in (ClosedOn, CreatedOn)) p
order by date