Condensing a data set

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services 2005 Development » Condensing a data set

Condensing a data set

Rate Topic

Display Mode

Topic Options

Author

Message

RSCERO

Posted Monday, November 03, 2008 10:36 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 7:57 AM
Points: 18, Visits: 136

I've got a data set that looks like the following:

ID Day1 Code1 Txt1 Day2 Code2 Txt2 Day3 Code3 Txt3
1 Mon 123 Because Tue NULL NULL Wed NULL NULL
1 Mon 345 Because Tue 456 Reason Wed NULL NULL
1 Mon NULL NULL Tue 789 Reason Wed NULL NULL
1 Mon NULL NULL Tue NULL NULL Wed 12 Cause
2 Mon NULL NULL Tue NULL NULL Wed 333 Cause

I'm trying to get the resulting table/matrix in the report to look like this:

1 Mon 123 Because Tue 456 Reason Wed 12 Cause
1 Mon 345 Because Tue 789 Reason Wed NULL NULL
2 Mon NULL NULL Tue NULL NULL Wed 333 Cause

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.

Post #595948

RSCERO

Posted Monday, November 03, 2008 1:51 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 7:57 AM
Points: 18, Visits: 136

I've created a solution for this. I'll post it shortly.

Post #596108

RSCERO

Posted Monday, November 03, 2008 4:23 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 7:57 AM
Points: 18, Visits: 136

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:

ID DayN CodeN TextN
1 Mon 123 Text1
1 Mon 345 Text2
1 Tue 456 Text3
1 Tue 789 Text4
1 Wed 12 Text5
2 Tue 222 Text7
2 Wed 333 Text6

Result set:

ID Day1 Code1 Text1 Day2 Code2 Text2 Day3 Code3 Text3
1 Mon 123 Text1 Tue 456 Text3 Wed 12 Text5
1 Mon 345 Text2 Tue 789 Text4 NULL NULL NULL
2 NULL NULL NULL Tue 222 Text7 Wed 333 Text6

Post #596209

« Prev Topic | Next Topic »

Permissions