Splitting Comma Separated Values into Rows

Question

Post reply

Splitting Comma Separated Values into Rows

rka

SSCommitted

Points: 1599
More actions
August 6, 2013 at 11:25 pm

#276730

DECLARE @t Table
(
AreaID int,
AreaName nvarchar(100),
Responsible nvarchar(100)
)
Insert Into @t
Select 1, 'Finance',Null
Union All
Select 2, 'IT','Internal, External'
Union All
Select 3, 'Audit, Security', 'Internal'
Union All
Select 4, 'Health, Safety, Compliance', 'Internal, External'
Select * From @t
/*
I want the results like the following without having to use any splitter udf
AreaIDAreaNameResponsible
----------------------------------
1FinanceNull
2ITInternal
2ITExternal
3AuditInternal
3SecurityInternal
4HealthInternal
4HealthExternal
4SafetyInternal
4SafetyExternal
4ComplianceInternal
4ComplianceExternal
*/

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

Any reason for not using splitter udf?

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Umer Akram SSC-Insane Points: 22209 More actions · Answer 2

Hope it helps u and others !!!

DECLARE @t Table

(

AreaID int,

AreaName nvarchar(100),

Responsible nvarchar(100)

)

Insert Into @t

Select 1, 'Finance',Null

Union All

Select 2, 'IT','Internal, External'

Union All

Select 3, 'Audit, Security', 'Internal'

Union All

Select 4, 'Health, Safety, Compliance', 'Internal, External'

UPDATE @T

SET AREANAME = REPLACE(AREANAME,' ','') + ','

, RESPONSIBLE = REPLACE(RESPONSIBLE,' ','') + ','

;WITH C1 (AREAID,RESPONSIBLE, STARTPOS, ENDPOS) AS

(

SELECT AREAID, RESPONSIBLE,CAST(1 AS INT) AS STARTPOS, CASE WHEN CHARINDEX(',' , RESPONSIBLE) = 0 THEN LEN(RESPONSIBLE) ELSE CHARINDEX(',' , RESPONSIBLE) END AS ENDPOS

FROM @T

UNION ALL

SELECT AREAID, RESPONSIBLE, CAST(ENDPOS AS INT)+1 AS STARTPOS, CHARINDEX(',',RESPONSIBLE, ENDPOS + 1) AS ENDPOS

FROM C1

WHERE CHARINDEX(',', RESPONSIBLE, ENDPOS + 1) <> 0

),

C2 (AREAID,AREANAME, STARTPOS, ENDPOS) AS

(

SELECT AREAID, AREANAME,CAST(1 AS INT) AS STARTPOS, CASE WHEN CHARINDEX(',' , AREANAME) = 0 THEN LEN(AREANAME) ELSE CHARINDEX(',' , AREANAME) END AS ENDPOS

FROM @T

UNION ALL

SELECT AREAID, AREANAME, CAST(ENDPOS AS INT)+1 AS STARTPOS, CHARINDEX(',',AREANAME, ENDPOS + 1) AS ENDPOS

FROM C2

WHERE CHARINDEX(',', AREANAME, ENDPOS + 1) <> 0

)

SELECT A.AREAID, SUBSTRING(B.AREANAME, B.STARTPOS, B.ENDPOS - B.STARTPOS) AS AREANAME

, REPLACE(SUBSTRING(REPLACE(A.RESPONSIBLE,' ',''), A.STARTPOS, A.ENDPOS - A.STARTPOS),',','') AS RESPONSIBLE

FROM C1 A

INNER JOIN C2 B ON A.AREAID = B.AREAID

ORDER BY A.AREAID

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Please don't cross post. It just fragments the replies.

Here is the other one. http://www.sqlservercentral.com/Forums/Topic1481654-392-1.aspx

_______________________________________________________________

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/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Sean Lange (8/7/2013)
Please don't cross post. It just fragments the replies.
Here is the other one. http://www.sqlservercentral.com/Forums/Topic1481654-392-1.aspx

1970's toilet graffiti: on the left-hand side of the cubicle is written "see other side for Wibmledon paractice". On the right-hand side is written "see other side for Wibmledon paractice" :hehe:

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

ChrisM@Work (8/7/2013)
Sean Lange (8/7/2013)
Please don't cross post. It just fragments the replies.
Here is the other one. http://www.sqlservercentral.com/Forums/Topic1481654-392-1.aspx
1970's toilet graffiti: on the left-hand side of the cubicle is written "see other side for Wibmledon paractice". On the right-hand side is written "see other side for Wibmledon paractice" :hehe:

hehe I guess I kind of just waster my time to post the same thing. 🙂

_______________________________________________________________

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/