Pivot on a single table

Question

Imagine we have the following dataset:

Name    City           Date
Paul    Milan          04/01/2013
Charls  Rome           04/01/2013
Jim     Tokyo          04/01/2013
Justin  San Francisco  04/01/2013
Bill    London         04/01/2013
Paul    Berlin         05/01/2013
Charls  El Cairo       05/01/2013
Jim     Milan          05/01/2013
Justin  Paris          05/01/2013
Bill    Madrid         05/01/2013

where each people [Name] has visited a certain town [City] in a certain day [Date].

What we want to do is to have a a table with [Name], [City day 1], [City day 2] as follows:

Name    City 04/01/2013 City 05/01/2013
Paul    Milan           Berlin
Charls  Rome            El Cairo
Jim     Tokyo           Milan
Justin  San Francisco   Paris
Bill    London          Madrid

How can we write a query for doing that?

Answer 1

This type of data transformation is known as a PIVOT. Starting in SQL Server 2005 there is a function that can perform this data rotation for you. But this can be done many different ways.

You can use an aggregate function and a CASE to pivot the data:

select
  name,
  max(case when date = '2013-04-01' then city end) [City 04/01/2013],
  max(case when date = '2013-05-01' then city end) [City 05/01/2013]
from yourtable
group by name

See SQL Fiddle with Demo

Or you can use the PIVOT function:

select name, [2013-04-01] as [City 04/01/2013], [2013-05-01] as [City 05/01/2013]
from
(
  select name, city, date
  from yourtable
) src
pivot
(
  max(city)
  for date in ([2013-04-01], [2013-05-01])
) piv

See SQL Fiddle with Demo.

This can even be done by joining on your table multiple times:

select d1.name,
  d1.city [City 04/01/2013], 
  d2.city [City 05/01/2013]
from yourtable d1
left join yourtable d2
  on d1.name = d2.name
  and d2.date = '2013-05-01'
where d1.date = '2013-04-01'

See SQL Fiddle with Demo.

The above queries will work great if you have known dates that you want to transform into columns. But if you have an unknown number of columns, then you will want to use dynamic sql:

DECLARE @cols AS NVARCHAR(MAX),
    @colNames AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(char(10), date, 120)) 
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @colNames = STUFF((SELECT distinct ',' + QUOTENAME(convert(char(10), date, 120)) +' as '+ QUOTENAME('City '+convert(char(10), date, 120))
                    from yourtable
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT name, ' + @colNames + ' from 
             (
                select name, 
                  city, 
                  convert(char(10), date, 120) date
                from yourtable
            ) x
            pivot 
            (
                max(city)
                for date in (' + @cols + ')
            ) p '

execute(@query)

See SQL Fiddle with Demo

All of them give the result:

|   NAME | CITY 04/01/2013 | CITY 05/01/2013 |
----------------------------------------------
|   Paul |           Milan |          Berlin |
| Charls |            Rome |        El Cairo |
|    Jim |           Tokyo |           Milan |
| Justin |   San Francisco |           Paris |
|   Bill |          London |          Madrid |

Answer 2

The simpliest possible way is by using CASE

SELECT  Name,
        MAX(CASE WHEN DATE = '04/01/2013' THEN City END) [City 04/01/2013],
        MAX(CASE WHEN DATE = '05/01/2013' THEN City END) [City 05/01/2013]
FROM    tableName
GROUP   BY Name