November 3, 2008 at 10:36 am
I've got a data set that looks like the following:
ID Day1 Code1 Txt1 Day2 Code2 Txt2 Day3 Code3 Txt3
1Mon123BecauseTueNULLNULLWedNULLNULL
1Mon345BecauseTue456ReasonWedNULLNULL
1MonNULLNULLTue789ReasonWedNULLNULL
1MonNULLNULLTueNULLNULLWed12Cause
2MonNULLNULLTueNULLNULLWed333Cause
I'm trying to get the resulting table/matrix in the report to look like this:
1Mon123BecauseTue456ReasonWed12Cause
1Mon345BecauseTue789ReasonWedNULLNULL
2MonNULLNULLTueNULLNULLWed333Cause
I can do this pretty easily if I was summing the Code or txt column. The problem is I need the detail for each code and txt, not a aggregation. Anyone done something similar to this before? I'm basically trying to condense the data into the fewest number of rows possible.
November 3, 2008 at 1:51 pm
I've created a solution for this. I'll post it shortly.
November 3, 2008 at 4:23 pm
Ok, here's how I resolved it. First I change the format of the initial query to return the data as 1 record per day per id and code. ID/Code is a unique combination. Once this was done, I used the following code to get the desired results. I can now display the data in a table on the report using the minimum number of rows possible.
I inlcuded code to create a sample source table so you can see how it actually works. Suggestions on improvements are always welcome.
-- Build Source Table for example
declare @BaseTbl table (ID int, DayN varchar(3), CodeN int, TextN varchar(10))
insert into @BaseTbl
select 1,'Mon',123,'Text1'
insert into @BaseTbl
select 1,'Mon',345,'Text2'
insert into @BaseTbl
select 1,'Tue',456,'Text3'
insert into @BaseTbl
select 1,'Tue',789,'Text4'
insert into @BaseTbl
select 1,'Wed',12,'Text5'
insert into @BaseTbl
select 2,'Tue',222,'Text7'
insert into @BaseTbl
select 2,'Wed',333,'Text6'
-- Seperate out records by day
select
ID,
DayN,
CodeN,
TextN,
'Rnk' = rank() over (partition by ID order by CodeN)
into #Day1
from @BaseTbl
where DayN = 'Mon'
select
ID,
DayN,
CodeN,
TextN,
'Rnk' = rank() over (partition by ID order by CodeN)
into #Day2
from @BaseTbl
where DayN = 'Tue'
select
ID,
DayN,
CodeN,
TextN,
'Rnk' = rank() over (partition by ID order by CodeN)
into #Day3
from @BaseTbl
where DayN = 'Wed'
-- Get list of distinct id/rnk combinations
select
a.ID
,a.Rnk
into
#Main
from
#Day1 a
union
select
a.ID
,a.Rnk
from
#Day2 a
union
select
a.ID
,a.Rnk
from
#Day3 a
-- Pull it all together
select
x.ID
,a.DayN as Day1
,a.CodeN as Code1
,a.TextN as Text1
,b.DayN as Day2
,b.CodeN as Code2
,b.TextN as Text2
,c.DayN as Day3
,c.CodeN as Code3
,c.TextN as Text3
from
#Main x
left outer join #Day1 a on x.ID = a.ID and x.Rnk = a.Rnk
left outer join #Day2 b on x.ID = b.ID and x.Rnk = b.Rnk
left outer join #Day3 c on x.ID = c.ID and x.Rnk = c.Rnk
drop table #Day1, #Day2, #Day3, #Main
Source set:
IDDayNCodeNTextN
1Mon123Text1
1Mon345Text2
1Tue456Text3
1Tue789Text4
1Wed12Text5
2Tue222Text7
2Wed333Text6
Result set:
IDDay1Code1Text1Day2Code2Text2Day3Code3Text3
1Mon123Text1Tue456Text3Wed12Text5
1Mon345Text2Tue789Text4NULLNULLNULL
2NULLNULLNULLTue222Text7Wed333Text6
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply