Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Condensing a data set


Condensing a data set

Author
Message
RSCERO
RSCERO
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 138
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
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

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



RSCERO
RSCERO
Grasshopper
Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)Grasshopper (18 reputation)

Group: General Forum Members
Points: 18 Visits: 138
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