January 24, 2015 at 6:49 pm
I have a database full of different types of leads some for company A some for company B and so on, each doing a different service. However the leads from B can be used for A and leads from A can be used for B, so I want to merge the data.
Exampled:
Phone Number Name Home Owner Credit Insurance
727-555-1234 Dave Thomas Yes B
727-555-1234 Dave Thomas Gieco
I would like the end result to be one record:
Phone Number Name Home Owner Credit Insurance
727-555-1234 Dave Thomas Yes B Gieco
Since these were imported into SQL they all have a unique ID, here are the current labels:
ID,phone_number,first_name,last_name,address1,address2,address3,city,state,postal_code,HOME_OWNR,HH_INCOME,CREDIT_RATING,AGE,MATCH,source_id,title,comments,dnc_flag,provider,vehicle,coverage,alt_phone,email,marital status,dob
January 24, 2015 at 7:13 pm
Tried working with the data you provided and it's a mess. Could you please post table definitions and insert statements for your data? Then those trying to help will be dealing with your data as it is, not just their best guess.
January 24, 2015 at 9:29 pm
k.allen.emt (1/24/2015)
I have a database full of different types of leads some for company A some for company B and so on, each doing a different service. However the leads from B can be used for A and leads from A can be used for B, so I want to merge the data.Exampled:
Phone Number Name Home Owner Credit Insurance
727-555-1234 Dave Thomas Yes B
727-555-1234 Dave Thomas Gieco
I would like the end result to be one record:
Phone Number Name Home Owner Credit Insurance
727-555-1234 Dave Thomas Yes B Gieco
Since these were imported into SQL they all have a unique ID, here are the current labels:
ID,phone_number,first_name,last_name,address1,address2,address3,city,state,postal_code,HOME_OWNR,HH_INCOME,CREDIT_RATING,AGE,MATCH,source_id,title,comments,dnc_flag,provider,vehicle,coverage,alt_phone,email,marital status,dob
Hello and welcome aboard!
To get the best help more quickly, please see the article at the first link under "Helpful Links" in my signature line below.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 25, 2015 at 5:40 am
Try the following:
with data
as (
select PhoneNo = '727-555-1234'
,Name = 'Dave Thomas'
,HomeOwner = 'Yes'
,Credit = 'B'
,Insurance = ''
union all
select '727-555-1234'
,'Dave Thomas'
,''
,''
,'Gieco'
)
select data.PhoneNo
,Name = max(data.Name)
,HomeOwner = max(data.HomeOwner)
,Credit = max(data.Credit)
,Insurance = max(data.Insurance)
from data
group by data.PhoneNo
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 25, 2015 at 6:56 am
ET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for leads
-- ----------------------------
DROP TABLE IF EXISTS `leads`;
CREATE TABLE `leads` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`phone_number` varchar(255) DEFAULT NULL,
`first_name` varchar(255) DEFAULT NULL,
`last_name` varchar(255) DEFAULT NULL,
`address1` varchar(255) DEFAULT NULL,
`address2` varchar(255) DEFAULT NULL,
`address3` varchar(255) DEFAULT NULL,
`city` varchar(255) DEFAULT NULL,
`state` varchar(255) DEFAULT NULL,
`postal_code` varchar(255) DEFAULT NULL,
`HOME_OWNR` varchar(255) DEFAULT NULL,
`HH_INCOME` varchar(255) DEFAULT NULL,
`CREDIT_RATING` varchar(255) DEFAULT NULL,
`AGE` varchar(255) DEFAULT NULL,
`MATCH` varchar(255) DEFAULT NULL,
`source_id` varchar(255) DEFAULT NULL,
`title` varchar(255) DEFAULT NULL,
`comments` varchar(255) DEFAULT NULL,
`dnc_flag` varchar(255) DEFAULT NULL,
`provider` varchar(255) DEFAULT NULL,
`vehicle` varchar(255) DEFAULT NULL,
`coverage` varchar(255) DEFAULT NULL,
`alt_phone` varchar(255) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
`marital status` varchar(255) DEFAULT NULL,
`dob` varchar(255) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `postal_code` (`postal_code`),
KEY `source_id` (`source_id`)
) ENGINE=MyISAM AUTO_INCREMENT=13876968 DEFAULT CHARSET=utf8;
January 25, 2015 at 7:08 am
Phil,
that looks like it would work for data you give to the statement itself.
I'm looking to go through close to 6 million records in leadsdb.leads
not manually do it
January 25, 2015 at 7:28 am
k.allen.emt (1/25/2015)
Phil,that looks like it would work for data you give to the statement itself.
I'm looking to go through close to 6 million records in leadsdb.leads
not manually do it
There was never any suggestion that you would do things manually. My code merely demonstrates a technique which you could use – you just have to plug in your table names and column names where I have used a CTE.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 25, 2015 at 7:32 am
Phil,
=) sorry, thanks I will give it a shot.
Beyond new here in this, my buddy tossed me his MySQL book "The Language of SQL" and said read.
Thank you again... also I was a bit lost on trying to obtain data for you guys with
SELECT 'SELECT '
+ QUOTENAME(ID,'''')+','
+ QUOTENAME(phone_number,'''')+','
+ QUOTENAME(first_name,'''')+','
+ QUOTENAME(last_name,'''')+','
+ QUOTENAME(address1,'''')+','
+ QUOTENAME(address2,'''')+','
+ QUOTENAME(address3,'''')+','
+ QUOTENAME(city,'''')+','
+ QUOTENAME(state,'''')+','
+ QUOTENAME(postal_code,'''')+','
+ QUOTENAME(HOME_OWNR,'''')+','
+ QUOTENAME(HH_INCOME,'''')+','
+ QUOTENAME(CREDIT_RATING,'''')+','
+ QUOTENAME(AGE,'''')+','
+ QUOTENAME(source_id,'''')+','
+ QUOTENAME(title,'''')+','
+ QUOTENAME(comments,'''')+','
+ QUOTENAME(dnc_flag,'''')+','
+ QUOTENAME(provider,'''')+','
+ QUOTENAME(vehicle,'''')+','
+ QUOTENAME(coverage,'''')+','
+ QUOTENAME(alt_phone,'''')+','
+ QUOTENAME(email,'''')+','
+ QUOTENAME(marital_status,'''')+','
+ QUOTENAME(dob,'''')
+ ' UNION ALL'
FROM leads
Running that reports:
[Err] 1305 - FUNCTION leaddb.QUOTENAME does not exist
January 26, 2015 at 1:43 pm
k.allen.emt (1/25/2015)
Phil,=) sorry, thanks I will give it a shot.
Beyond new here in this, my buddy tossed me his MySQL book "The Language of SQL" and said read.
Thank you again... also I was a bit lost on trying to obtain data for you guys with
SELECT 'SELECT '+ QUOTENAME(ID,'''')+','
+ QUOTENAME(phone_number,'''')+','
+ QUOTENAME(first_name,'''')+','
+ QUOTENAME(last_name,'''')+','
+ QUOTENAME(address1,'''')+','
+ QUOTENAME(address2,'''')+','
+ QUOTENAME(address3,'''')+','
+ QUOTENAME(city,'''')+','
+ QUOTENAME(state,'''')+','
+ QUOTENAME(postal_code,'''')+','
+ QUOTENAME(HOME_OWNR,'''')+','
+ QUOTENAME(HH_INCOME,'''')+','
+ QUOTENAME(CREDIT_RATING,'''')+','
+ QUOTENAME(AGE,'''')+','
+ QUOTENAME(source_id,'''')+','
+ QUOTENAME(title,'''')+','
+ QUOTENAME(comments,'''')+','
+ QUOTENAME(dnc_flag,'''')+','
+ QUOTENAME(provider,'''')+','
+ QUOTENAME(vehicle,'''')+','
+ QUOTENAME(coverage,'''')+','
+ QUOTENAME(alt_phone,'''')+','
+ QUOTENAME(email,'''')+','
+ QUOTENAME(marital_status,'''')+','
+ QUOTENAME(dob,'''')
+ ' UNION ALL'
FROM leads
Running that reports:
[Err] 1305 - FUNCTION leaddb.QUOTENAME does not exist
If you're working with MySQL, you may fare better posting your problem in a MySQL forum rather than a SQL Server 2014 forum.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply