T-SQL simple transform

Question

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

Please update your question title to represent the technical question... as it stands its meaningless. — Dale K
– Dale K, Commented Mar 15, 2022 at 21:34
You already know that you need to pivot - and that requires an aggregate even if it doesn't "seem suitable". The bigger issue is that you have a table that logically represents an EAV (Entity Attribute Value) design - which is something that requires more than basic programming skills. Much depends on how many values you expect to find in the column named "ColumnName". You show two different values - is that just the tip of the iceberg here? — SMor
– SMor, Commented Mar 15, 2022 at 21:39

John Cappelletti · Accepted Answer · 2022-03-15 22:08:35Z

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

Thank you @John-Cappelletti, I did not know that about how to use PIVOT by feeding the required columns. Thanks for your answer

Stu · Accepted Answer · 2022-03-15 21:36:42Z

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.

Thank you @Stu, I added the RowID for the purposes of posting the question here. But seeing your answer I can add it into my original query. Thanks for your answer

Mable98 · Accepted Answer · 2022-03-15 22:47:39Z

Expanding on from what I learned from @John-Cappelletti answer I had a fiddle around and came up with this solution that combines an XML Path query to help build a dynamic-sql query. This works for an unknown number of ColumnName values.

Posting it here as it may be of use to others

CREATE TABLE #TempTable(RowId int, ColumnName VARCHAR(20), DataValue VARCHAR(20)) INSERT INTO #TempTable VALUES (0, 'First Name', 'David'), (1, 'First Name', 'Sarah'), (0, 'Last Name', 'Jones'), (1, 'Last Name', 'Vaughan'), (0, 'Age', '100'), (1, 'Age', '50') -- Get the column names in the format [Name 1], [Name 2] -- as required for the Pivot query DECLARE @ColumnNames VARCHAR(MAX) = ( SELECT DISTINCT '[' + ColumnName + ']' + ',' AS 'data()' FROM #TempTable FOR XML path('') ) --- Remove the trailing comma (must be a better way to do this) SET @ColumnNames = LEFT(@ColumnNames, LEN(@ColumnNames) - 1) --- Build the Pivot query DECLARE @SqlQuery VARCHAR(MAX) = 'Select * From #TempTable Pivot ( max(DataValue) for ColumnName in (' + @ColumnNames + ') ) pvt' EXEC(@SqlQuery)

Well done. There are a couple of tweaks. Use QUOTENAME() and STUFF() Take a peek at dbfiddle.uk/…
QUOTENAME() will handle the []'s and STUFF() will remove the LEADING comma (vs trailing)

Collectives™ on Stack Overflow

T-SQL simple transform

3 Answers 3

1 Comment

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

2 Comments

Related