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