Need help denormalizing query for a report
-
/*
Using SQL 2005.
I need to return denormalized data from this sample data:
*/
DECLARE @Tmp TABLE (
GroupColumnA varchar(6),
GroupColumnB varchar(6) NULL,
GroupColumnC varchar(13),
DetailColumnA varchar(15),
DetailColumnB varchar(30))
insert into @Tmp
select '123456', '','1111223334444', 'AB1984', 'Remarks1' UNION
select '123456', '','1111223334444', 'AB7458', 'Remarks2' UNION
select '123456', '','1111223334444', 'AB7461', 'Remarks3' UNION
select '123456', '','1111223334444', 'AB8461', 'Remarks4' UNION
select '123456', '','4444332221111', 'AB1234', 'Remarks5' UNION
select '123456', '','4444332221111', 'AB4567', 'Remarks6' UNION
select '123456', '','4444332221111', 'AB8574', 'Remarks7' UNION
select '123456', '','4444332221111', 'AB9876', 'Remarks8' UNION
select '147852', '','2222334445555', 'SS8000-1', 'Remarks9' UNION
select '147852', '','2222334445555', 'SS8000-2', 'Remarks10' UNION
select '147852', '','2222334445555', 'SS8000-3', 'Remarks11' UNION
select '147852', '','2222334445555', 'SS8000-4', 'Remarks12'
select * from @Tmp
-- data going into @Tmp will be sorted by the first four columns.
-- Results should look like:
DECLARE @tmp1 TABLE (
GroupColumnA varchar(6),
GroupColumnB varchar(6) NULL,
GroupColumnC varchar(13),
DetailColumnA1 varchar(15),
DetailColumnB1 varchar(30),
DetailColumnA2 varchar(15),
DetailColumnB2 varchar(30),
DetailColumnA3 varchar(15),
DetailColumnB3 varchar(30))
insert into @tmp1
select '123456', NULL, '1111223334444', 'AB1984', 'Remarks1', 'AB7458',
'Remarks2', 'AB7461', 'Remarks3' UNION
select '123456', NULL, '1111223334444', 'AB8461', 'Remarks4', NULL, NULL,
NULL, NULL UNION
select '123456', NULL, '4444332221111', 'AB1234', 'Remarks5', 'AB4567',
'Remarks6', 'AB8574', 'Remarks7' UNION
select '123456', NULL, '4444332221111', 'AB9876', 'Remarks8', NULL, NULL,
NULL, NULL UNION
select '147852', NULL, '2222334445555', 'SS8000-1', 'Remarks9', 'SS8000-2',
'Remarks10', 'SS8000-3', 'Remarks11' UNION
select '147852', NULL, '2222334445555', 'SS8000-4', 'Remarks12', NULL, NULL,
NULL, NULL
select * from @tmp1
/*
where the first 3 rows of @Tmp have the DetailColumns filled in for @tmp1.
This will cause the DetailColumnA to be in ascending order from left to
right, top to bottom in @tmp1 DetailColumnA1/A2/A3. At any change in any of
the three GroupColumn's, this should start all over with the next
DetailColumnA going into DetailColumnA1.
I'd like to do this in a set-based solution, if possible.
Thanks,
Wayne
*/
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
Have you looked at Pivot and Unpivot for this?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
GSquared (4/9/2008)
Have you looked at Pivot and Unpivot for this?Yes. Pivot performs an aggregrate on one grouping column and makes a column for each row. Not quite what I need.
I have three grouping columns, multiple detail columns, and only want to go up to three (sets of) detail columns wide. And I'm not doing an aggregrate.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2 -
Pivot can be used that way, you just have to trick it.
Max(ID) = ID
If you aggregate on the column you group by, you end up with the column exactly as it is.
If you don't want to mess around with that, then what you do is an "old-style pivot". There are a couple of discussions on how to do that on this page. If you do a search for those terms, you should find them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
Try this
with cte as (select GroupColumnA,
GroupColumnB,
GroupColumnC,
DetailColumnA,
DetailColumnB,
(row_number() over(partition by GroupColumnA,GroupColumnB,GroupColumnC
order by DetailColumnA)-1)%3 as rnmod,
(row_number() over(partition by GroupColumnA,GroupColumnB,GroupColumnC
order by DetailColumnA)-1)/3 as rndiv
from @Tmp
)
select GroupColumnA,
GroupColumnB,
GroupColumnC,
max(case when rnmod=0 then DetailColumnA end) as DetailColumnA1,
max(case when rnmod=0 then DetailColumnB end) as DetailColumnB1,
max(case when rnmod=1 then DetailColumnA end) as DetailColumnA2,
max(case when rnmod=1 then DetailColumnB end) as DetailColumnB2,
max(case when rnmod=2 then DetailColumnA end) as DetailColumnA3,
max(case when rnmod=2 then DetailColumnB end) as DetailColumnB3
from cte
group by GroupColumnA,GroupColumnB,GroupColumnC,rndiv
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Thanks Mark, this works really nice.
Mark (4/10/2008)
Try thiswith cte as (select GroupColumnA,
GroupColumnB,
GroupColumnC,
DetailColumnA,
DetailColumnB,
(row_number() over(partition by GroupColumnA,GroupColumnB,GroupColumnC
order by DetailColumnA)-1)%3 as rnmod,
(row_number() over(partition by GroupColumnA,GroupColumnB,GroupColumnC
order by DetailColumnA)-1)/3 as rndiv
from @Tmp
)
select GroupColumnA,
GroupColumnB,
GroupColumnC,
max(case when rnmod=0 then DetailColumnA end) as DetailColumnA1,
max(case when rnmod=0 then DetailColumnB end) as DetailColumnB1,
max(case when rnmod=1 then DetailColumnA end) as DetailColumnA2,
max(case when rnmod=1 then DetailColumnB end) as DetailColumnB2,
max(case when rnmod=2 then DetailColumnA end) as DetailColumnA3,
max(case when rnmod=2 then DetailColumnB end) as DetailColumnB3
from cte
group by GroupColumnA,GroupColumnB,GroupColumnC,rndiv
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply