August 9, 2011 at 8:30 am
I have a table I am trying to unpivot the data in
Name ID Examid1 Examproduct1 Examid2 Examproduct2
tom 5235 1 diploma 3 Adv diploma
John 69231 1 Diploma 9 degree
dave 12348 5 degree
SELECT
name, id, exam
FROM dbo.import
UNPIVOT (
exam for examtype in (examproduct1, Examproduct2 )) as Exam
Im using the above to give me the following
Name ID exam
tom 5235 Dimploma
tom 5235 Adv diploma
John 69231 Dimploma
John 69231 degree
dave 12348 degree
But I cant get the examid1 and examid2 to unpivot accordingly, I need it in the following format
Name ID examid exam
tom 5235 1 Dimploma
tom 5235 3 Adv diploma
John 69231 1 Dimploma
John 69231 9 degree
dave 12348 5 degree
Any help here would be great, thank you
August 9, 2011 at 9:27 am
Next time, please provide ddl and sample data insert script as part of your question (see the link at the bottom of my signature how to do so)
SQL UNPIVOT has a limitation of unpivoting single column, however there is no limitation of how many unpivots you can do 😀
In your case if columns to unpivot can be correlated using their index you can use the following query:
declare @table table (Name varchar(10), ID int, Examid1 int, Examproduct1 varchar(20), Examid2 int, Examproduct2 varchar(20))
insert @table values ('tom', 5235, 1, 'diploma', 3, 'Adv diploma'),
('John', 69231, 1, 'Diploma', 9, 'degree'),
('dave', 12348, 5, 'degree', null, null)
SELECT name, id, examid, exam
FROM @table
UNPIVOT (
examid for examidtype in (Examid1, Examid2 )) as examid
UNPIVOT (
exam for examtype in (examproduct1, Examproduct2 )) as Exam
WHERE RIGHT(examidtype,1) = RIGHT(examtype,1)
RIGHT(examidtype,1) = RIGHT(examtype,1) : It will only work when your columns can be correlated by name as ExamId1 Examproduct1
I'm not sure about performance of the above, looks like using union will run much much faster:
select Name, ID, ExamId1 As ExamId, Examproduct1 As ExamType from @table where Examid1 is not null
union
select Name, ID, ExamId2 As ExamId, Examproduct2 As ExamType from @table where Examid2 is not null
order by id
August 11, 2011 at 3:19 am
Thanks, I’m not normally a fan of union was advised to stay away where possible as it’s not the best on method with performance. But that said the data being held in the table is small so shouldn’t be an issue thanks for your advice.
August 11, 2011 at 7:34 am
dave 92282 (8/11/2011)
Thanks, I’m not normally a fan of union was advised to stay away where possible as it’s not the best on method with performance. But that said the data being held in the table is small so shouldn’t be an issue thanks for your advice.
Personally I hate unions too...
I mean trade unions! :w00t:
But unlike to the above, T-SQL UNION operation is very handy.
It combines the results of two or more queries into a single result set that includes all the rows that belong to all queries in the union.
I don't think there any fan-clubs arround T-SQL statments, clauses, operations, datatypes etc.
They all uncontitionally exist and they all have some use.
I dont know who advised you to stay away of it, but it either:
1. This person has no idea about this subject
or, most likely,
2. You missunderstood his advice. (It could be given for some specific case where union wasn't required).
Actually, if you know that resulting set will not contain duplicates (eg. you never have the same value in columns you unpivoting) you can use UNION ALL, it will be faster as it will not need to dedupe result.
And the last one. Did you ever heard the rumour that using UNPIVOT is also "not the best on method with performance" :hehe:
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply