April 18, 2005 at 4:48 pm
I have a question regarding a problem I have run into before and have never been happy with my solution. I'm sure others have run into this as well.
How can I combine records into one record from a result set
id val1 val2 val3
1 1
1
1 1
2 2
2 2
2 2
I would like the result as:
id val1 val2 val3
1 1 1
2 2 2 2
Thanks in advance. Anyone looking for gunsmithing or reloading advice feel free to ask!!!!!
April 18, 2005 at 5:34 pm
I'm pretty sure someone else will have a better solution than this:
SELECT COALESCE( a1.id, a2.id, a3.id) as id, a1.val1, a2.val2, a3.val3
FROM (
select distinct id, val1 from #t
where val1 is not null
) A1
FULL OUTER JOIN (
select distinct id, val2 from #t
where val2 is not null
) A2 ON a2.id = a1.id
FULL OUTER JOIN (
select distinct id, val3 from #t
where val3 is not null
) A3 ON a3.id = a1.id
April 18, 2005 at 5:59 pm
One of ways
select [id],
sum(case when val1 is not null then val1 else null end) val1,
sum(case when val2 is not null then val2 else null end) val2,
sum(case when val3 is not null then val3 else null end) val3
from table group by [id]
April 18, 2005 at 10:31 pm
this will also work,
select id,max(val1),max(val2),max(val3) from t4 group by id
My Blog:
April 20, 2005 at 2:11 am
Question is, can you guarantee, that every column will always have non-NULL value in one row with a given ID? The sample of data looks like that, but is it enforced somehow? What I mean is, can there be rows like that:
id val1 val2 val3
1 1
1 1 1
1 1
2 2
2 2
2 2
And if yes, how do you want to treat them? Either SUM or MAX could do the trick, it depends on what you require.
wz700: Just curious... is there any advantage or performance gain in your proposed CASE solution, over a simple SUM(ISNULL(val1,0))? In fact, SQLS can cope with NULL values during aggregation itself so SUM(val1) would produce the same result - but I agree that it is a good policy to get rid of NULLs before aggregation. I do it myself in a "permanent" code (i.e., I don't bother with it if I'm just checking something and will throw the query away after I'm done).
April 20, 2005 at 7:19 am
Solving problems like this is very easy with the RAC utility for S2k.You concentrate on the problem and not the messy convoluted sql code to solve it.With RAC you could transpose the data to normal form and then preform a crosstab using the @rank option.
Post/e-mail if you want further details.
RAC and QALite @
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply