Geek Answers Handbook

Transact SQL - dynamically creates column names

It's difficult for me...

I am working on Microsoft SQL Server 2008, and I have a table with usernames in it. The names of people could change over time, so there is 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 did not create this table, so I cannot log in and change the way data is stored). The person with PID 1 is called Michael Hansen. This is his current name (sequence 0 always indicates the current name), and since there are no other entries, he was always called Michael Hansen.

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

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

And my database contains information on six historical names (sequences 0, 91, 92, 93, 94, 95).

Now I was told to list all the names in the new table with only one row per person, for example:

 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 that 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

He gives me all the names since I want them on the same line on the PID. And the current name is also always displayed under Name1. The problem is that I need the middle name to be in the column Name2, etc. In my case (of course), I only work if the person had six names.

So what I need to do is name the columns Name2 to Name6 dynamically based on how many names the PID actually had. So I tried to build my SQL dynamically like (DECLARE @SQL AS NVARCHAR (MAX), and then set @SQL = to the SQL example above). Then I 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 can work (it will give the correct column names), but, unfortunately, the syntax "+ count (PID)" does not work.

(Fu!) So, who has a solution for this?

Thanks in advance.

+4
source share
4 answers

Do this with RANK () and PIVOT () together. The rank () to determine the "age" of the name, the rotation 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 script: http://sqlfiddle.com/#!3/d8301/16

+3
source

Your syntax error is that you are trying to combine COUNT(*) , which is an int, with NVARCHAR. I think something like this will 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 does not seem like very promising evidence, since you are still manually creating SQL, I am not sure why your sequence goes from 0 to 91, but I assumed that they will always be in ascending order, even if there are spaces, so they used the function ROW_NUMBER() to get an 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

SQL script example

+2
source 
 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;
+1
source

More articles:


All Articles