T-SQL Transposing column headers into Rows

Question

I want to transpose my table. I have simple 'Person' table as shown below.

+---+----------+------------+------------+----------------------+
|ID | Person   | BirthDate  | Phone      | Email                |
+---+----------+------------+------------+----------------------+
| 1 | Tom      | 1985-11-08 | 1111111111 | tom@somedomain.com   |
+---+----------+------------+------------+----------------------+
| 2 | Dick     | 1982-02-24 | 2222222222 | dick@otherdomain.com |
+---+----------+------------+------------+----------------------+
| 3 | Harry    | 1986-04-17 | 3333333333 | harry@thatdomain.com |
+---+----------+------------+------------+----------------------+

And I want this table to be transposed like below.

+-----------+--------------------+----------------------+----------------------+
| Key       | Value1             | Value2               | Value3               |
+-----------+--------------------+----------------------+----------------------+
| ID        | 1                  | 2                    | 3                    |
+-----------+--------------------+----------------------+----------------------+
| Person    | Tom                | Dick                 | Harry                |
+-----------+--------------------+----------------------+----------------------+
| BirthDate | 1985-11-08         | 1982-02-24           | 1986-04-17           |
+-----------+--------------------+----------------------+----------------------+
| Phone     | 1111111111         | 2222222222           | 3333333333           |
+-----------+--------------------+----------------------+----------------------+
| Email     | tom@somedomain.com | dick@otherdomain.com | harry@thatdomain.com |
+-----------+--------------------+----------------------+----------------------+

I am using MS SQL server 2008 R2.

Pரதீப் · Accepted Answer · 2014-11-09T03:21:29.133

Try this.. First u need to unpivot the columns using Cross apply to get the data in single row. Then pivot that row to get the result.

CREATE TABLE #tt
  (ID        INT,Person    VARCHAR(50),BirthDate DATE,
     Phone     BIGINT,Email     VARCHAR(50)
  )

INSERT INTO #tt
VALUES      (1,'Tom','1985-11-08',1111111111,'tom@somedomain.com' ),
            ( 2,'Dick','1982-02-24',2222222222,'dick@otherdomain.com'),
            ( 3,'Harry ','1986-04-17',3333333333,'harry@thatdomain.com' ) 

SELECT [key],
       Max([value1]) [value1],
       Max([value2]) [value2],
       Max([value3]) [value3]
FROM   (SELECT 'value' + CONVERT(VARCHAR(30), id) valued,
               *
        FROM   #tt
               CROSS apply (VALUES ('ID',
                           CONVERT(VARCHAR(50), ID)),
                                   ('Person',Person),
                                   ('BirthDate',CONVERT(VARCHAR(50), BirthDate)),
                                   ('Phone',CONVERT(VARCHAR(50), Phone)),
                                   ('Email',Email)) cp ([key], data))a
       PIVOT (Max(data)
             FOR valued IN([value1],[value2],[value3])) piv
GROUP  BY [key]

DYNAMIC VERSION

Declare @cols varchar(max)='',@aggcols varchar(max)='',@sql nvarchar(max)

SELECT @cols+= ',value' + CONVERT(VARCHAR(30), id) 
        FROM   #tt
SELECT @aggcols+= ',max([value' + CONVERT(VARCHAR(30), id) +']) value' + CONVERT(VARCHAR(30), id) 
        FROM   #tt
select @cols=  right(@cols,LEN(@cols)-1)
select @aggcols =right(@aggcols,LEN(@aggcols)-1)


set @sql = 'SELECT [key],
       '+@aggcols+'
FROM   (SELECT ''value'' + CONVERT(VARCHAR(30), id) valued,
               *
        FROM   #tt
               CROSS apply (VALUES (''ID'',CONVERT(VARCHAR(50), ID)),
                                   (''Person'',Person),
                                   (''BirthDate'',CONVERT(VARCHAR(50), BirthDate)),
                                   (''Phone'',CONVERT(VARCHAR(50), Phone)),
                                   (''Email'',Email)) cp ([key], data))a
       PIVOT (Max(data)
             FOR valued IN('+@cols+')) piv
GROUP  BY [key] '

execute sp_executesql @sql

OUTPUT

+----------+--------------------+---------------------+----------------------+
|key       |    value1          |   value2            |     value3           |
+----------+--------------------+---------------------+----------------------+
|BirthDate |    1985-11-08      |   1982-02-24        |     1986-04-17       |
+----------+--------------------+---------------------+----------------------+
|Email     | tom@somedomain.com |dick@otherdomain.com | harry@thatdomain.com |
+----------+--------------------+---------------------+----------------------+
|ID        |    1               |   2                 |     3                |
+----------+--------------------+---------------------+----------------------+
|Person    |    Tom             |   Dick              |     Harry            |
+----------+--------------------+---------------------+----------------------+
|Phone     |    1111111111      |   2222222222        |     3333333333       |
+----------+--------------------+---------------------+----------------------+

Thanks for the solution. Is there a way to make this dynamic? I mean as and when new records got entered into the first table, can we have that many columns? — Kunapareddy, Nov 08 '14 at 18:56
Use STUFF to accept all the column names and by a dynamic query you can execute it. Probably this post will help you [Efficiently convert rows to columns in sql server](http://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server) — sqluser, Nov 09 '14 at 00:37

T-SQL Transposing column headers into Rows

1 Answers1