Transposing a SQL query result

Question

I have a query that produces a table that looks like this:

+----------------+-----------+------------+----------+
| CustomerNumber | FirstName | MiddleName | LastName |
+----------------+-----------+------------+----------+
|         123456 | Test      | Test1      | Test2    |
+----------------+-----------+------------+----------+

What I am needing is for the table to look like this:

+----------------+--------+
| CustomerNumber | 123456 |
+----------------+--------+
| FirstName      | Test   |
| MiddleName     | Test1  |
| LastName       | Test2  |
+----------------+--------+

This is my current sql query:

SELECT 
        CustomerNumber,
        FirstName,
        MiddleName,
        LastName
FROM    Customers
WHERE   CustermerNumber = 123456

Is there a way to complete this? I have been looking at transposing it via unpivot but have not been able to understand how.

It seems like you are looking for dynamic unpivot, which would be the last example from [**Taryn's answer**](https://stackoverflow.com/questions/18026236/sql-server-columns-to-rows) — S3S, Jun 13 '18 at 18:25

score 2 · Accepted Answer · answered Jun 13 '18 at 18:24

2

You can use apply :

select tt.*
from table t cross apply ( 
         values ('CustomerNumber', CustomerNumber), ('FirstName', FirstName),
                 ('MiddleName', MiddleName), ('LastName', LastName)
       ) tt (name, v);

answered Jun 13 '18 at 18:24

Yogesh Sharma

49,870
5
26
52

score 1 · Answer 2 · answered Jun 14 '18 at 09:28

With dynamic TSQL pivoting you can also manage multiple rows input:

if OBJECT_ID('Test') is not null 
    drop table [dbo].[Test] 

CREATE TABLE [dbo].[Test](CustomerNumber varchar(10), FirstName varchar(10), 
                          MiddleName varchar(10), LastName varchar(10)) 

--populate test table
insert into [dbo].[Test] values
 (123456, 'Test','Test1','Test2')
 , (234567, 'Test_2','Test_21','Test_22')
 , (345678, 'Test_3','Test_31','Test_32')

--this variable holds all the customer numbers that will become column names 
declare @columns nvarchar(max)='' 
--this variable contains the dinamically generated TSQL code
declare @sql nvarchar(max)=''     

select @columns = @columns + ', [' + [CustomerNumber] + ']' from [dbo].[Test] 
set @columns = RIGHT(@columns, len(@columns)-2) 

set @sql = @sql + 'select piv.COL as CustomerNumber, ' + @columns 
set @sql = @sql + ' from ' 
set @sql = @sql + ' ( ' 
set @sql = @sql + '  select [CustomerNumber], col, val, ord ' 
set @sql = @sql + '  from [dbo].[Test] ' 
set @sql = @sql + '  CROSS APPLY (' 
set @sql = @sql + '  VALUES (''FirstName'' ,FirstName , 1), ' 
set @sql = @sql + '         (''MiddleName'',MiddleName, 2), ' 
set @sql = @sql + '         (''LastName''  ,LastName,   3) ' 
set @sql = @sql + '  )CS (COL,VAL,ORD) ' 
set @sql = @sql + ' ) src ' 
set @sql = @sql + ' pivot ( max(val) for [CustomerNumber] in ('+@columns+') ) piv' 
set @sql = @sql + ' order by ord' 

exec(@sql)

Sample input and output with one row:

Sample input and output with three rows:

Transposing a SQL query result

2 Answers2