December 31, 2013 at 3:54 am
I know there have been several forum discussions about flattening hierarchies using recursive CTEs but the examples I have found have a ParentID field in the data. My data does not have this.
Please take a look at this sample code
declare @table table
(id varchar(10),
levelid int,
leveldesc varchar(250))
insert into @table
select 'DP961A1111', 1, 'LevelDesc1'
union
select 'DP961A1111', 2, 'LevelDesc2'
union
select 'DP961A1111', 3, 'LevelDesc3'
select * from @table
--I need to display the results like this:
select 'DP961A1111' as id, 'LevelDesc1' as LevelDesc1, 'LevelDesc2' as LevelDesc2, 'LevelDesc3' as LevelDesc3
Can you guys help?
December 31, 2013 at 5:11 am
Try this - it's called a CROSSTAB query:
SELECT id,
LevelDesc1 = MAX(CASE WHEN levelid = 1 THEN leveldesc ELSE NULL END),
LevelDesc2 = MAX(CASE WHEN levelid = 2 THEN leveldesc ELSE NULL END),
LevelDesc3 = MAX(CASE WHEN levelid = 3 THEN leveldesc ELSE NULL END)
FROM @table
GROUP BY id
Here's a great article to explain how it works, and how far you can go with it:
http://www.sqlservercentral.com/articles/T-SQL/63681/%5B/url%5D
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 31, 2013 at 8:29 am
I have came across the crosstab query before but only in theory, never used it in anger! I'll give this a go.
Thanks for your help, greatly appreciated.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy