August 6, 2013 at 11:28 pm
I want to split the Comma Separated Values into rows
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
*/
Reason: Not allowed to build custom function on a proprietary database
Any help please?
August 7, 2013 at 1:55 am
Use APPLY to "encapsulate" a splitter function - in this case DelimitedSplit8k[/url];
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';
-----------------------------------------------------------------------
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
iTally(N) AS (SELECT 1 FROM E2 a, E2 b) --10E+4 or 10,000 rows max
SELECT
m.AreaID,
AreaName = x1.Item,
Responsible = x2.Item
FROM @t m
CROSS APPLY ( -- x1n (ripped from DelimitedSplit8k)
SELECT
ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(m.AreaName, l.N1, l.L1)
FROM ( -- l
SELECT s.N1, L1 = ISNULL(NULLIF(CHARINDEX(',', m.AreaName, s.N1),0)-s.N1,8000)
FROM ( -- s
SELECT n1 = 1
UNION ALL
SELECT t.N+1 FROM (SELECT TOP (ISNULL(DATALENGTH(m.AreaName),0)) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM iTally) t
WHERE SUBSTRING(m.AreaName,t.N,1) = ','
) s
) l
) x1
CROSS APPLY ( -- x2
SELECT
ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(m.Responsible, l.N1, l.L1)
FROM ( -- l
SELECT s.N1, L1 = ISNULL(NULLIF(CHARINDEX(',', m.Responsible, s.N1),0)-s.N1,8000)
FROM ( -- s
SELECT n1 = 1
UNION ALL
SELECT t.N+1 FROM (SELECT TOP (ISNULL(DATALENGTH(m.Responsible),0)) n = ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM iTally) t
WHERE SUBSTRING(m.Responsible,t.N,1) = ','
) s
) l
) x2
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:34 am
Please don't cross post. It just fragments the replies.
Here is the other one. http://www.sqlservercentral.com/Forums/Topic1481652-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/
Viewing 3 posts - 1 through 2 (of 2 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