Split Columns into Multiple Rows

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Split Columns into Multiple Rows

Author

Topic

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2014-11-19 : 10:59:16

Good morning, I did search for answers on this but could not find that met my needs.

I have a table with 2 columns (contactid, mailings)
The mailings column can have multiple numbers of entries seperated by commas.

Sample:

ContactID Mailings

C6UJ9A0036CP LVOForum,Cohnnect,FMLFNJ,LawInd

What I am looking to do is to insert into a new table the contactid, and individaul mailing field.

After conversion table example from Above Data:

ContactID Mailing
C6UJ9A0036CP LVOForum
C6UJ9A0036CP Cohnnect,FMLFNJ,LawInd
C6UJ9A0036CP FMLFNJ
C6UJ9A0036CP LawInd

Thank you in advance with any help.

Bryan Holmstrom

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-19 : 11:05:25

[code]
insert into table2 (contactId, Mailing)
select ContactId, c.mailing
from table1 t1
cross apply (
select Mailings
from table1 t2
where t1.ContactId = t2.ContactId) c(mailing)
[/code]

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2014-11-19 : 11:39:28

Thaks Yak, I don't think I explained very well (and my example was bad as well). I have one record that has a contactid, and a mailing field.
The mailing field could have 1 string, 4, 5 etc. seperated by a comma. What I need is for this example if the record has 4 strings in the mailing column the my new table would have 4 records with the same contactid, and one entry per mailing.

Original Data
ContactID Combined Mailings
C6UJ9A0036CP LVOForum,Cohnnect,FMLFNJ,LawInd

The new table should then contain the following entries

ContactID Individual Mailing
C6UJ9A0036CP LVOForum
C6UJ9A0036CP Cohnnect
C6UJ9A0036CP FMLFNJ
C6UJ9A0036CP LawInd

I hope this helps, and thanks again for the quick reply

Bryan Holmstrom

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-19 : 11:53:28

You can use the string splitter for this. See this article: http://www.sqlservercentral.com/articles/Tally+Table/72993/

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2014-11-19 : 12:21:06

That works, Thanks.

Question: if use the splitter code here:

insert into dbo.SLX_SingleMailing (SLX_contactId, SLX_Mailing)
SELECT test.ContactID, Item = QUOTENAME(split.Item,'"')
FROM dbo.SLX_Mailing test
CROSS APPLY ncos.dbo.DelimitedSplit8k(test.Mailings,',') split
WHERE test.Mailings IS NOT NULL AND LEN(TEST.MAILINGS) > 1 and len(test.contactid) > 1
;

I end up with this: C2CC9A100003 "FMLFNY"

If I remove the '"' above and use '' I end up with this: C2CC9A100003 [FMLFNY]

Anyway to just have it populate the field with FMLFNY

Bryan Holmstrom

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2014-11-19 : 12:29:13

I got it with this, just wondering if there was a way to do it with out doing the replace portion?

USE CRMvsSLX
GO

TRUNCATE TABLE DBO.SLX_SINGLEmAILING

insert into dbo.SLX_SingleMailing (SLX_contactId, SLX_Mailing)
SELECT test.ContactID, Item = QUOTENAME(split.Item,'')
FROM dbo.SLX_Mailing test
CROSS APPLY ncos.dbo.DelimitedSplit8k(test.Mailings,',') split
WHERE test.Mailings IS NOT NULL AND LEN(TEST.MAILINGS) > 1 and len(test.contactid) > 1
;

update [CRMvsSLX].[dbo].[SLX_SingleMailing]
SET SLX_Mailing = REPLACE(LTRIM(RTRIM(REPLACE(SLX_Mailing, '[', ' '))), ']', ' ')

SELECT top 1000
SLX_ContactID
,SLX_Mailing
--,[CRM_Mailing]

FROM [CRMvsSLX].[dbo].[SLX_SingleMailing]
order by SLX_ContactID
GO

Bryan Holmstrom

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-11-19 : 12:29:51

Don'e use QUOTENAME

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources