November 26, 2009 at 2:36 am
Hi I hope someone can help.
I have a very large table with records as follows:
ID, Cust_id, Title, First, Last, Add1, Town, Prod1, Prod2, Prod3, Prod4, Prod5
1, 22654, Mr, Jack, Branning, 3 The End, Preston, Y, NULL, NULL, NULL, NULL
2, 22654, Mr, Jack, Branning, 3 The End, Preston, NULL, NULL, NULL, Y, NULL
3, 22654, Mr, Jack, Branning, 3 The End, Preston, NULL, Y, NULL, NULL, NULL
4, 22654, Mr, Jack, Branning, Unit 1, Bolton Ind Est, Y, NULL, NULL, NULL, NULL
5, 22654, Mr, Jack, Branning, Unit 1, Bolton Ind Est, NULL, NULL, NULL, Y, NULL
6, 22654, Mr, Jack, Branning, Unit 1, Bolton Ind Est, NULL, Y, NULL, NULL, NULL
What I would like to end up with is one record for each address, combining the positives from the Prod columns:
1, 22654, Mr, Jack, Branning, 3 The End, Preston, Y, Y, NULL, Y, NULL
2, 22654, Mr, Jack, Branning, Unit 1, Bolton Ind Est, Y, Y, NULL, Y, NULL
Paul
November 26, 2009 at 3:04 am
Table structure you have given is not normalized. Try to satisfy the normalization principles in the table design. Best solution would be to denormalize the table.
For the output you are expecting you may use the following query and the same query can be altered to use data migration script for normalizing the table :
select custid, Title, first, last, add1, Town, case when sum(case when prod1 = 'Y' then 1 else 0 end) > 0 then 'Y' else null end,
case when sum(case when prod2 = 'Y' then 1 else 0 end) > 0 then 'Y' else null end,
case when sum(case when prod3 = 'Y' then 1 else 0 end) > 0 then 'Y' else null end,
case when sum(case when prod4 = 'Y' then 1 else 0 end) > 0 then 'Y' else null end,
case when sum(case when prod5 = 'Y' then 1 else 0 end) > 0 then 'Y' else null end
from Customer
group by custid, Title, first, last, add1, Town
November 26, 2009 at 3:24 am
This piece of code produces what you're after, for the example you give:
SELECT MAX(Cust_id),MAX(Title),MAX(First),MAX(Last),Add1,Town,MAX(Prod1),MAX(Prod2),MAX(Prod3),MAX(Prod4),MAX(Prod5)
FROM MergeTest
GROUP BY Add1, Town
I've taken the liberty of attaching a script that creates the table and data for someone else to have a go. I'm not convinced my code would cater for all cases as the data you give is a tad 'narrow' in its range, so I'd like someone else to to provide another way of doing this.
BrainDonor
Preston and Bolton? You mix in some exotic circles.
November 26, 2009 at 3:56 am
Thanks for the replies. I am jus tworking through them now.
A more realistic scenario of my data is as follows:
ID, Cust_id, Title, First, Last, Add1, Town, Prod1, Prod2, Prod3, Prod4, Prod5
22654, 'Mr', 'Jack', 'Branning', '3 The End', 'Preston', Y, NULL, NULL, NULL, NULL
21987, 'Miss', 'Zoe', 'Slater', 'Big House', 'Wigan', NULL, NULL, NULL, 'NULL', Y
22654, 'Mr', 'Jack', 'Branning', 'Unit 1', 'Bolton Ind Est', Y, NULL, NULL, NULL, NULL
22654, 'Mr', 'Jack', 'Branning', 'Unit 1', 'Bolton Ind Est', NULL, NULL, NULL, Y, NULL
20555, 'Mr', 'Steven', 'McDonald', 'Elder End', Crawley, NULL, NULL, Y, NULL, NULL
21987, 'Miss', 'Zoe', 'Slater', '1 The Square', 'Oldham', Y, NULL, NULL, NULL, NULL
22654, 'Mr', 'Jack', 'Branning', '3 The End', 'Preston', NULL, NULL, NULL, Y, NULL
21987, 'Miss', 'Zoe', 'Slater', '1 The Square', 'Oldham', NULL, NULL, NULL, 'NULL', Y
20555, 'Mr', 'Steven', 'McDonald', 'Elder End', Crawley, NULL, NULL, NULL, NULL, NULL
22654, 'Mr', 'Jack', 'Branning', 'Unit 1', 'Bolton Ind Est', NULL, Y, NULL, NULL, NULL
21987, 'Miss', 'Zoe', 'Slater', 'Big House', 'Wigan', Y, NULL , NULL, 'NULL', NULL
22654, 'Mr', 'Jack', 'Branning', 3 The End, Preston, NULL, Y, NULL, NULL, NULL
Looking for results of:
22654, 'Mr', 'Jack', 'Branning', '3 The End', 'Preston', Y, Y, NULL, Y, NULL
22654, 'Mr', 'Jack', 'Branning', 'Unit 1', 'Bolton Ind Est', Y, Y, NULL, Y, NULL
21987, 'Miss', 'Zoe', 'Slater', 'Big House', 'Wigan', Y, NULL, NULL, 'NULL', Y
21987, 'Miss', 'Zoe', 'Slater', '1 The Square', 'Oldham', Y, NULL, NULL, 'NULL', Y
20555, 'Mr', 'Steven', 'McDonald', 'Elder End', Crawley, NULL, NULL, Y, NULL, NULL
These are just examples. I think the exotic locations are more indicitave of my state of mind than the true data!
November 26, 2009 at 6:39 am
Thanks both!
They work well. I now have a list unique per each person's address, whic is thebest I could hope for from such a table.
I can now work on eithe rdeleting the remaining records, or somehow using the result of this query to insert new data into another table.
Thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply