Transact SQL - dynamically build column names

Question

This one is to tricky for me...

I work on Microsoft SQL Server 2008 and I have a table with personnames in it. The persons names could have changed over time so there are also historical information.

Example:

PID Sequence Name
1   0        Michael Hansen
2   0        Ulla Hansen
2   94       Ulla Margrethe Hansen
2   95       Ulla Margrethe Jensen
3   0        Daniella Oldfield
3   95       Daniella Quist

(I have not build this table - so I cannot go in and change how data is stored). Person with PID 1 is called Michael Hansen. This is his current name (sequence 0 is always specifying the current name) and as there are no other records he has always been named Michael Hansen.

Person PID 2 is currently called Ulla Hansen (sequence 0). Before that she was called Ulla Margrethe Hansen (as this is the next sequence number) and before that again she was called Ulla Margrethe Jensen.

What I know about this table is that the current name is always sequence 0. I also know that if there have been two names the next sequence is 95. And three historical names: current name: sequence 0, before that sequence 94 and oldest name 95.

And my database contains information about up to 6 historical names (sequence 0, 91, 92, 93, 94, 95).

Now I have been told to list all names in a new table with just one row per person like:

PID Name1             Name2                   Name3
1   Michael Hansen
2   Ulla Hansen       Ulla Margrethe Hansen   Ulla Margrethe Jensen
3   Daniella Oldfield Daniella Quist

So far I have the following SQL which almost works:

SELECT PID 
  ,MAX(CASE sequence WHEN 0 THEN Name ELSE '' END) AS Name1
  ,MAX(CASE sequence WHEN 91 THEN Name ELSE '' END) AS Name2
  ,MAX(CASE sequence WHEN 92 THEN Name ELSE '' END) AS Name3
  ,MAX(CASE sequence WHEN 93 THEN Name ELSE '' END) AS Name4
  ,MAX(CASE sequence WHEN 94 THEN Name ELSE '' END) AS Name5
  ,MAX(CASE sequence WHEN 95 THEN Name ELSE '' END) AS Name6
FROM tblShipTypeHistory
GROUP BY PID

It gives me all the names as I want it in one row per PID. And the current name is also always listed under Name1. The problem is that I need the second newest name to be in column Name2 etc. I my case it (of course) only works if a person have had six names.

So what I need to do is name the columns Name2 to Name6 dynamically based on how many names the PID have actually had. So I have tried building my SQL dynamically like (DECLARE @SQL AS NVARCHAR(MAX) and then set @SQL = to the above SQL example). Then I have tried something like

SET @SQL = 'SELECT ....
,MAX(CASE sequence WHEN 91 THEN Name ELSE '' END) AS Name' + COUNT(PID) - 4 + '
,MAX(CASE sequence WHEN 92 THEN Name ELSE '' END) AS Name' + COUNT(PID) - 3 + '
,MAX(CASE sequence WHEN 93 THEN Name ELSE '' END) AS Name' + COUNT(PID) - 2 + '
,MAX(CASE sequence WHEN 94 THEN Name ELSE '' END) AS Name' + COUNT(PID) - 1 + '
,MAX(CASE sequence WHEN 95 THEN Name ELSE '' END) AS Name' + COUNT(PID) + '

Logically this could work (it would give the the correct column names), but unfortunately the syntax "+ count(PID)" doesn't work.

(Phew!) So anyone who has a solution for this?

Thank you in advance.

Answer 1

with names(pid, name, rn) as 
(
  select pid, 
         name, 
         ROW_NUMBER() over (partition by pid order by sequence) 
  from tblShipTypeHistory
)
select pid, 
       [1] as Name1, 
       [2] as Name2, 
       [3] as Name3, 
       [4] as Name4, 
       [5] as Name5, 
       [6] as Name6
from names pivot(max(name) for rn in ([1], [2], [3], [4], [5], [6])) as a;

Answer 2

Do it with RANK() and PIVOT() combined. Rank() to figure out the "age" of the name, pivot to get all the columns.

create table t(pid int not null, sequence int not null, name nvarchar(50))


INSERT INTO t VALUES
(1,   0,        N'Michael Hansen'),
(2,   0,        N'Ulla Hansen'),
(2,   94,       N'Ulla Margrethe Hansen'),
(2,   95,       N'Ulla Margrethe Jensen'),
(3,   0,        N'Daniella Oldfield'),
(3,   95,       N'Daniella Quist')

SELECT pid, [1] as Name1, [2] as Name2, [3] as Name3, [4] as Name4, [5] as Name5, [6] as Name6
FROM
(
SELECT pid, name, rank() over(partition BY pid ORDER BY sequence) AS name_age
FROM t) SOURCE
PIVOT
(
  max(name)
 FOR [name_age] in ([1], [2], [3], [4], [5], [6])) as pvt

Sql fiddle: http://sqlfiddle.com/#!3/d8301/16

Answer 3

Your syntax error lies in the fact that you are trying to concatenate COUNT(*) which is an int, with NVARCHAR. I think something like this would work for you:

SET @SQL = 'SELECT ....
,MAX(CASE sequence WHEN 91 THEN Name ELSE '' END) AS Name' + CAST(COUNT(PID) - 4 AS NVARCHAR) + '
,MAX(CASE sequence WHEN 92 THEN Name ELSE '' END) AS Name' + CAST(COUNT(PID) - 3 AS NVARCHAR) + '
,MAX(CASE sequence WHEN 93 THEN Name ELSE '' END) AS Name' + CAST(COUNT(PID) - 2 AS NVARCHAR) + '
,MAX(CASE sequence WHEN 94 THEN Name ELSE '' END) AS Name' + CAST(COUNT(PID) - 1 AS NVARCHAR) + '
,MAX(CASE sequence WHEN 95 THEN Name ELSE '' END) AS Name' + CAST(COUNT(PID) AS NVARCHAR) + '

However, this doesn't seem very future proof as you are still manually creating the SQL, I am not sure why your sequence goes from 0 to 91, but I have assumed that they will always be in ascending order, even if there are gaps so have used the ROW_NUMBER() function to get the instance of each name:

DECLARE @SQL NVARCHAR(MAX) = '',
        @PVT NVARCHAR(MAX) = ''

SELECT  @SQL = @SQL + ', COALESCE(' + QUOTENAME('Name' + RowNum) + ', '''') AS ' +  QUOTENAME('Name' + RowNum),
        @PVT = @PVT + ', ' + QUOTENAME('Name' + RowNum)
FROM    (   SELECT  DISTINCT CONVERT(VARCHAR, ROW_NUMBER() OVER(PARTITION BY PID ORDER BY Sequence)) [RowNum]
            FROM    tblShipTypeHistory
        ) rn

SET @SQL = 'SELECT PID' + @SQL + '
            FROM    (   SELECT  PID, 
                                Name, 
                                ''Name'' + CONVERT(VARCHAR, ROW_NUMBER() OVER(PARTITION BY PID ORDER BY Sequence)) [NameID]
                        FROM    tblShipTypeHistory
                    ) data
                    PIVOT
                    (   MAX(Name)
                        FOR NameID IN (' + STUFF(@PVT, 1, 2, '') + ')
                    ) pvt'

EXECUTE SP_EXECUTESQL @SQL

Example on SQL Fiddle

Answer 4

Try Dynamic PIVOT http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx