SQL Transpose Data with Column Name

Question

I am trying to transpose data in a SQL Server table with one row of data but with several columns, all into one column along with their respective column headers.

Original Data Table:

**TABLE Column Names:**   Id, ColumnA , ColumnB , ColumnC , StartDate

**Data:**                 1, 'aa' , 'bb' , 'cc', 2016-10-10

Required Format of Data:

**ColumnName     Values**
Id               1
ColumnA         aa
ColumnB         bb
ColumnC         cc
StartDate     2016-10-10


CREATE DATABASE ToDelete
GO

USE [ToDelete]
GO

CREATE TABLE [dbo].[sourceData](
    [id] [int] NULL,
    [ColumnA] [varchar](50) NULL,
    [ColumnB] [varchar](50) NULL,
    [ColumnC] [varchar](50) NULL,
    [StartDate] [datetime] NULL
) 
GO
INSERT [dbo].[sourceData] ([id], [ColumnA], [ColumnB], [ColumnC], [StartDate], [EndDate]) VALUES (1, 'aa', N'bb', N'cc', GETDATE())
GO

The query I am using to pull the table column names is:

SELECT c.name
FROM sys.tables t
JOIN sys.columns c
ON t.object_id = c.object_id
WHERE t.name = 'sourceData'

Your help would be appreciated.

Thank you

Answer 1

Here is an option that may help you create something more like an EAV structure

Example

Declare @YourTable Table ([Id] varchar(50),[ColumnA] varchar(50),[ColumnB] varchar(50),[ColumnC] varchar(50),[StartDate] date)
Insert Into @YourTable Values 
 (1,'aa','bb','cc','2016-10-10')


Select A.ID
      ,C.*
 From @YourTable A
 Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)
 Cross Apply (
                Select Field = a.value('local-name(.)','varchar(100)')
                      ,Value = a.value('.','varchar(max)') 
                 From  B.XMLData.nodes('/row')  as C1(n)
                 Cross Apply C1.n.nodes('./@*') as C2(a)
                 Where a.value('local-name(.)','varchar(100)') not in ('ID','OtherColumnsTo','Exclude')
             ) C

Returns

ID  Field       Value
1   ColumnA     aa
1   ColumnB     bb
1   ColumnC     cc
1   StartDate   2016-10-10