August 6, 2013 at 11:25 pm
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
*/
August 7, 2013 at 3:10 am
Any reason for not using splitter udf?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
August 7, 2013 at 6:40 am
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
August 7, 2013 at 7:34 am
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/
August 7, 2013 at 7:45 am
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:
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
August 7, 2013 at 7:57 am
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/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy