SQL Query Help - Separate Column
-
Hi All:
Need help with SQL Query. I have a column named Participants with Names and email addresses in one single column. I want to separate the once with email address in separate column.
Participants:
Green, Jack;ews@coby.com;jim.beck@downing.com;Yue, Ian
cody.Ortiz@about.com;Enverga, Frank;carol.green@michi.com
mtorrent@cali.com;Mike, John;tom.frank@ncsc.comOutput:
Thanks !
- --Deleted
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel - Phil Parkin - Monday, May 14, 2018 8:52 AMsqldba20 - Monday, May 14, 2018 8:38 AMHi All:
Need help with SQL Query. I have a column named Participants with Names and email addresses in one single column. I want to separate the once with email address in separate column.
Participants:
Green, Jack;ews@coby.com;jim.beck@downing.com;Yue, Ian
cody.Ortiz@about.com;Enverga, Frank;carol.green@michi.com
mtorrent@cali.com;Mike, John;tom.frank@ncsc.comOutput:
Thanks !
Based on this sample data, what will your desired output look like?
Output:
Names Email Id
Green, Jack;Yue, Ian ews@coby.com;jim.beck@downing.com
Enverga, Frank cody.Ortiz@about.com;carol.green@michi.com
Mike, John mtorrent@cali.com;tom.frank@ncsc.com - sqldba20 - Monday, May 14, 2018 8:53 AM
Apologies, I misread your original post. It's clear enough!
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Output File Attached:
-
The issue here is that your original data structure is downright awful. It violates 1NF. And the desired output isn't really any better. You are taking one denormailzed pile of data and changing into a different denormalized disaster.
Do you always have the exact format of 1 email, 2 names, 1 email? And is the delimiter consistent? If so you could use the splitter found in my signature below.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
The participant column is a CRM column and I want to separate it for reporting. The format is not always same. The delimeter is consistent ";"
Thanks !
- sqldba20 - Monday, May 14, 2018 9:57 AM
The delimiter is always a semicolon, ok that is helpful. So you have no way of knowing of what order these might come in or how many delimited lists of each you might have? What a nightmare!!! Do you have anything in your table you can use as a primary key or is it really just these two columns?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Yes, there is ID column which is a primary key and Account column which is name of the company. Here is the image file with table structure.
- sqldba20 - Monday, May 14, 2018 1:11 PMYes, there is ID column which is a primary key and Account column which is name of the company. Here is the image file with table structure.
Instead of pictures can you post ddl and sample data? See the first link in my signature.
BTW, your image did not come through for me.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
The basic gist here is that you would use Jeff Moden's splitter to normalize this first. Then you would use STUFF and FOR XML to denormalize into two pieces of delimited data again.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Here's the detail on using the function Grant refers to:
CREATE TABLE #Participants (
Participant varchar(100) NOT NULL PRIMARY KEY CLUSTERED,
ParticipantID int IDENTITY(1,1) NOT NULL
);
INSERT INTO #Participants (Participant)
VALUES ('Green, Jack;ews@coby.com;jim.beck@downing.com;Yue, Ian'),
('cody.Ortiz@about.com;Enverga, Frank;carol.green@michi.com'),
('mtorrent@cali.com;Mike, John;tom.frank@ncsc.com');WITH SplitData AS (
SELECT P.ParticipantID, S.ItemNumber, S.Item
FROM #Participants AS P
CROSS APPLY dbo.DelimitedSplit8K(P.Participant, ';') AS S
),
SeparatedData AS (SELECT SD.ParticipantID, SD.ItemNumber,
CASE
WHEN SD.Item LIKE '%@%' THEN SD.Item
ELSE NULL
END AS EMail,
CASE
WHEN SD.Item NOT LIKE '%@%' THEN SD.Item
ELSE NULL
END AS Name
FROM SplitData AS SD
)
SELECT DISTINCT SD.ParticipantID, N.Names, E.EMails
FROM SeparatedData AS SD
CROSS APPLY (
SELECT STUFF((
SELECT ';' + S2.Name
FROM SeparatedData AS S2
WHERE S2.ParticipantID = SD.ParticipantID
AND S2.Name IS NOT NULL
FOR XML PATH('')
), 1, 1, '') AS Names
) AS N
CROSS APPLY (
SELECT STUFF((
SELECT ';' + S2.EMail
FROM SeparatedData AS S2
WHERE S2.ParticipantID = SD.ParticipantID
AND S2.EMail IS NOT NULL
FOR XML PATH('')
), 1, 1, '') AS EMails
) AS EORDER BY SD.ParticipantID;
DROP TABLE #Participants;
You'll have to adapt the column names and table names to your environment and eliminate the CREATE TABLE and DROP TABLE statements. You can get Jeff Moden's string splitter here: http://www.sqlservercentral.com/articles/72993/
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
DECLARE @t table(Participants nvarchar(1000));
INSERT @t ( Participants )
VALUES ( N'Green, Jack;ews@coby.com;jim.beck@downing.com;Yue, Ian' )
, ( N'cody.Ortiz@about.com;Enverga, Frank;carol.green@michi.com' )
, ( N'mtorrent@cali.com;Mike, John;tom.frank@ncsc.com' )
SELECT names.rn id
, a.Item Emails
, b.item Names
FROM (SELECT Participants, Row_Number() OVER(ORDER BY Participants) rn FROM @t) names
JOIN (SELECT Participants, Row_Number() OVER(ORDER BY Participants) rn FROM @t) emails ON names.rn = emails.rn
CROSS APPLY dbo.DelimitedSplit8K(names.Participants,';') a
CROSS APPLY dbo.DelimitedSplit8K(emails.Participants,';') b
WHERE a.item LIKE '%@%'
AND b.Item NOT LIKE '%@%' - Joe Torre - Monday, May 14, 2018 4:58 PM
DECLARE @t table(Participants nvarchar(1000));
INSERT @t ( Participants )
VALUES ( N'Green, Jack;ews@coby.com;jim.beck@downing.com;Yue, Ian' )
, ( N'cody.Ortiz@about.com;Enverga, Frank;carol.green@michi.com' )
, ( N'mtorrent@cali.com;Mike, John;tom.frank@ncsc.com' )
SELECT names.rn id
, a.Item Emails
, b.item Names
FROM (SELECT Participants, Row_Number() OVER(ORDER BY Participants) rn FROM @t) names
JOIN (SELECT Participants, Row_Number() OVER(ORDER BY Participants) rn FROM @t) emails ON names.rn = emails.rn
CROSS APPLY dbo.DelimitedSplit8K(names.Participants,';') a
CROSS APPLY dbo.DelimitedSplit8K(emails.Participants,';') b
WHERE a.item LIKE '%@%'
AND b.Item NOT LIKE '%@%'I applaud the effort but this doesn't produce the results the OP is asking for.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Hi All, Thanks a lot for all the help. I think this is very helpful and gives me something to start.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply