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.

Jonas Lincoln · Accepted Answer · 2012-08-10 09:40:45Z

3

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

answered Aug 10, 2012 at 9:40

Jonas Lincoln

9,8079 gold badges38 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

olf Over a year ago

Well it works for me! I do know that there is a maximum of 6 historical names and it is actually naming the columns as I want it, so thumbs up from here! Thank you!

Frank Myat Thu Over a year ago

Thank a lot, your sql code solve my problem also. @Jonas Lincoln

GarethD · Accepted Answer · 2012-08-10 09:24:16Z

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

Well, I see your point, but for some reason it doesn't work. The first example says it doesn't know PID so it was not just a matter for casting to nvarchar. The second example says "Column Name unknown".

Paciv · Accepted Answer · 2012-08-10 09:48:02Z

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;

answered Aug 10, 2012 at 9:48

Paciv

1,48710 silver badges16 bronze badges

4 Comments

Paciv Over a year ago

The op example result only shows 3 names columns. However, all his tries clearly shows that he's trying to pivot the name column depending on the sequence order with 6 different names columns as a result. He says 'list all names in a new table with just one row per person' meaning he will store this in an other table structure, which obviously will contain 6 names columns. Best is to ask for confirmation though.

olf Over a year ago

Well it works for me! I do know that there is a maximum of 6 historical names and it is actually naming the columns as I want it, so thumbs up from here! Thank you!

olf Over a year ago

I gave Jonas Lincoln the accepted answer as it is similar to yours - but he was seven minutes quicker :-).

Paciv Over a year ago

Yes he was quicker, but I was using CTE instead of sub-query, that's why I still posted the answer.

Madhivanan · Accepted Answer · 2012-08-10 09:12:10Z

0

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

answered Aug 10, 2012 at 9:12

Madhivanan

13.7k1 gold badge28 silver badges30 bronze badges

Collectives™ on Stack Overflow

Transact SQL - dynamically build column names

4 Answers 4

2 Comments

1 Comment

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related