2

Suppose you have repeated measures on individuals over time displayed vertically and you wish to convert that to a wide display, e.g. from

family | year | salary
fam1   | 2002 | 80
fam1   | 2003 | 82
fam1   | 2008 | 90
fam2   | 1996 | 45
fam3   | 2002 | 70
fam3   | 2003 | 72

to

family | salary1996 | salary2002 | salary2003 | salary2008
fam1   | 45         | .          | .          | .
fam2   | .          | 80         | 82         | 90
fam3   | .          | 70         | 72         | .

The problem is that there is arbitrary number of possible values for "year".

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
AdamO
  • 4,283
  • 1
  • 27
  • 39
  • 1
    For what database? A dynamic number of years guarantees needing to use dynamic SQL. – OMG Ponies Jun 29 '11 at 17:45
  • 1
    Why would you convert to a wide table? The way you have it to begin with is proper for your situation. – Nik Jun 29 '11 at 17:47
  • @Nik We're merging with a "family" level dataset that is very large. We need rectangular data for statistical software. Replicating observations from the family level data would be too large and unstable to do anything useful. – AdamO Jun 29 '11 at 17:48

1 Answers1

3

If you are using SQL Server, this is what you are looking for:

Update

Still assuming you are using SQL Server 2005 or above, here is the code:

DECLARE @cols VARCHAR(1000)
DECLARE @sqlquery VARCHAR(2000)

SELECT  @cols = STUFF(( SELECT distinct  '], [' + cast([year] as varchar)
                        FROM YourTable FOR XML PATH('') ), 1, 2, '') + ']'


SET @sqlquery = 'SELECT * FROM
      (SELECT family,  [year], [salary]
       FROM YourTable) base
       PIVOT (SUM(salary) FOR [year]
       IN (' + @cols + ')) AS finalpivot'

EXECUTE ( @sqlquery )
Community
  • 1
  • 1
Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123