T-SQL simple transform-CodePudding

I would like to transpose data returned from a SQL query in the manner outlined in the code below.

Given:

CREATE TABLE #TempTable(RowId INT, ColumnName VARCHAR(20), ColumnValue VARCHAR(20))

INSERT INTO #TempTable
VALUES 
(0, 'First Name', 'David'),
(1, 'First Name', 'Sarah'),
(0, 'Last Name', 'Jones'),
(1, 'Last Name', 'Vaughan')

I would like to produce a result set like this:

[RowID] | [First Name] | [Last Name]
   0    |    David     | Jones
   1    |    Sarah     | Vaughan

Preferably on SQL Server 2016

CodePudding user response：

You are after a Pivot, there are literally hundreds of examples of doing this on Stack Overflow and elsewhere.

However it's pretty simple to implement a conditional case like so:

select RowId,
    Max(case when ColumnName='First Name' then ColumnValue end) [First Name],
    Max(case when ColumnName='Last Name' then ColumnValue end) [Last Name]
from #TempTable
group by RowId

If you have more values you can can add these following the same pattern.

CodePudding user response：

Conditional aggregation offers more flexibility/control ( 1 Stu), but if you are looking for a simple PIVOT...

Remember to "FEED" your PIVOT with only the required columns.

Example

Select *
 From  #TempTable
 Pivot ( max(ColumnValue) for ColumnName in ( [First Name],[Last Name] ) ) pvt

Results

RowId   First Name  Last Name
0       David       Jones
1       Sarah       Vaughan