SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Condensing a data set


Condensing a data set

Author
Message
RSCERO
RSCERO
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 143
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.



RSCERO
RSCERO
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 143
I've created a solution for this. I'll post it shortly.



RSCERO
RSCERO
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 143
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



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search