Transpose unknown number of Columns

Question

I'm looking for an efficient way to convert rows to columns using T-SQL.

This the sample data:

+----+--------------+----------------+
| ID |  ColumnName  |     Value      |
+----+--------------+----------------+
|  1 | FirstName    | Name 1         |
|  2 | FirstName    | Name 2         |
|  3 | FirstName    | Name 3         |
|  4 | FirstName    | Name 4         |
|  1 | LastName     | LastName 1     |
|  2 | LastName     | LastName 2     |
|  3 | LastName     | LastName 3     |
|  4 | LastName     | LastName 4     |
|  1 | MobileNumber | MobileNumber 1 |
|  2 | MobileNumber | MobileNumber 2 |
|  3 | MobileNumber | MobileNumber 3 |
|  4 | MobileNumber | MobileNumber 4 |
+----+--------------+----------------+

This is the expected result:

+----+-----------+------------+----------------+
| ID | FirstName |  LastName  |  MobileNumber  |
+----+-----------+------------+----------------+
|  1 | Name 1    | LastName 1 | MobileNumber 1 |
|  2 | Name 2    | LastName 2 | MobileNumber 2 |
|  3 | Name 3    | LastName 3 | MobileNumber 3 |
|  4 | Name 4    | LastName 4 | MobileNumber 4 |
+----+-----------+------------+----------------+

How can I build the query?

I have tried the following links (and much more), but I can't achieve the expected result:

Efficiently convert rows to columns in sql server

Simple way to transpose columns and rows in Sql?

Is your example data correct? ID 1 has only 4 first names, and nothing else, but in the result it has 3 different items... — James Z, May 26 '15 at 14:09
Agreed. Your sample data and desired output are not even close to each other. You have ID 4 in the output but that doesn't even exist in the sample data. — Sean Lange, May 26 '15 at 14:11
possible duplicate of [Dynamic pivot table with multiple columns in sql server](http://stackoverflow.com/questions/21325394/dynamic-pivot-table-with-multiple-columns-in-sql-server) — Anon, May 26 '15 at 15:18
@jarlh, I need a solution that is not working with fixed columns, For Example if Home number is added, It must have it's own column in the output. — Hennie Francis, May 27 '15 at 10:28
@GordonLinoff, Thanks, I will have a look and see what is there. — Hennie Francis, May 27 '15 at 10:32
@JamesZ, Each ID has a FirstName, LastName and MobileNumber (example), I sorted the data by ID, not Column Name — Hennie Francis, May 27 '15 at 10:32

score 2 · Answer 1 · edited May 26 '15 at 15:38

Try using a dynamic pivot like so (This is following the assumption that the ID in your question is a mistake and should actually be 1,2,3,4,1,2,3,4... etc). I have included a "data cleanse" to avoid any SQL Injection comebacks:

Declare @SQL nvarchar(max)
Declare @columns nvarchar(max)

Select @columns = (Select Distinct Quotename(Columnname) + ',' from table for xml path(''))
Set @columns = Left(@columns, Len(@columns) - 1)

Set @SQL = 'Select ID, ' + @columns + '
from
(Select ID, Value, Columnname
from table) as src
PIVOT
(Max(Value) FOR Columnname in (' + @columns + ')) as pvt'

exec sp_executesql @SQL

SQL Fiddle: http://www.sqlfiddle.com/#!3/7a79d2/5

score 1 · Accepted Answer · answered May 26 '15 at 14:35

1

Welcome to Dynamic Pivots! You can use a query such as this, to extract a dynamic set of columns:

DECLARE @SQL NVARCHAR(MAX)
DECLARE @VALS NVARCHAR(500)

SELECT DISTINCT [COLUMNNAME] AS VALS
INTO #VALS
FROM TABLE1

SELECT @VALS = COALESCE(@VALS+', ','') + '[' + VALS + ']' FROM #VALS

SET @SQL = '
SELECT ID, '+@VALS+'
FROM TABLE1
PIVOT(MIN([VALUE]) FOR [COLUMNNAME] IN ('+@VALS+')) PIV'
PRINT @SQL
EXEC(@SQL)

Here's an SQL Fiddle: http://sqlfiddle.com/#!6/48bc8/1

Note how I've actually changed your sample data, as I assumed you constructed it incorrectly. See the ID column to see the difference. I'd be very surprised if this is representative of your actual data.

answered May 26 '15 at 14:35

John Bell

2,350
1
14
23

1

What happens when you hit a row where `VAL = '_])) x; DROP TABLE Students;--'`? – Anon May 26 '15 at 14:50
Your point is out of the scope of the question. That's an application layer problem, not something handled in the back end. Down voting a correct answer because you *think* there's SQL Injection in the data is ridiculous. – John Bell May 26 '15 at 14:52
1

@JohnBell I strongly disagree!!! Protecting the data is a fundamental task of the data layer. Sure we can try to prevent this type of thing in the front end but just blindly executing parameters passed in and assuming they are safe is NOT a good approach. – Sean Lange May 26 '15 at 14:56
Injection is always within the scope of a dynamic SQL question. An answer that ignores the issue is not a correct answer. – Anon May 26 '15 at 15:00
Link me to where you've read up on this. I'm interested to know of course, since it would disprove everything I've learnt about tiered architectures for the past 15 years. Databases should have constraints and defaults. A correctly written DB model would not accept SQL Injection, regardless of the application layer. I don't agree that an answer should be down voted because you assume something else that's out of the scope of this subject is true. – John Bell May 26 '15 at 15:01

Transpose unknown number of Columns

2 Answers2