I've found this trick useful, and maybe you will too.
I was given a table with a number of columns which needed to be turned into a vertical table with one row for each column as name-value pairs.
i.e. changing:
Title FirstName Surname
------- ----------- --------
Mr John Smith
into:
NamePart NameValue
----------- ---------
Title Mr
FirstName John
Surname Smith
To solve this, I created a table with one column and 3 distinct rows: one for each column I wanted to split out, and included it unjoined in the query. I then used a CASE to return the appropriate column for each value in the temporary table, and sorted the output by the temporary table value to keep the ordering. Of course this only works if all the columns have the same data type, otherwise they will have to be CAST to a common type.
The example makes more sense, so here is the code:
declare @Tab table (Id INT IDENTITY(1,1), Title VARCHAR(30), FirstName VARCHAR(30), Surname VARCHAR(30));
insert into @Tab values ('Mr','John','Smith');
insert into @Tab values ('Dr','David','Jones');
insert into @Tab values ('Mrs','Sally','Brown');
select Id, Title, FirstName, Surname
from @Tab;
with P (Col1)
as
(select Col1=1
union all
select Col1=2
union all
select Col1=3)
select T.Id,
case P.Col1
when 1 then 'Title'
when 2 then 'FirstName'
when 3 then 'Surname'
end as [Name Part],
case P.Col1
when 1 then T.Title
when 2 then T.FirstName
when 3 then T.Surname
end as [Name Value]
from @Tab T, P
order by T.Id, P.Col1;
Output:
Id Title FirstName Surname
----------- ------------ ------------- ------------------------------
1 Mr John Smith
2 Dr David Jones
3 Mrs Sally Brown
Id NamePart NameValue
----------- --------- ------------------------------
1 Title Mr
1 FirstName John
1 Surname Smith
2 Title Dr
2 FirstName David
2 Surname Jones
3 Title Mrs
3 FirstName Sally
3 Surname Brown
No comments:
Post a Comment