Split path folder into subfolder.
-
I have a two column ID and Path that contains below information. I have almost 1000 records but I just put 3 here for example.
ID Path
1./ACS/OTHER ACS REPORTS/XYZ/ABC
2./ACS/OTHER ACS REPORTS/Pending Deletion(JJP)
3./ACS/Pending Deletion(JJP)
I want to create each new column based on "/".
I want
1. ACS Other ACS reports XYZ ABC
2. ACS Others ACS reports Pending deletion (JJP)
3. ACS Pending Deletion (JJP)
I am getting 5 column and Path column split into 4 extra column (see record 1)
and second split into 3 column based on "/" and third split into 2 column based "/".
I have almost 1000 rows and I need to split by subfolder.
Can any one help me ?
Thanks
Bhavesh
-
Is there any one can help ??
-
patla4u (8/7/2014)
Is there any one can help ??Hey there!
It'd be great if you could have a read of this (http://qa.sqlservercentral.com/articles/Best+Practices/61537/)[/url], then set up some sample data, DDL and expected results based on your sample data. Otherwise, you're asking the unpaid volunteers of this site to do this for you so that they can provide you with a working, tested solution.
I've had a quick go, using the 8K Delimited Splitter[/url].
So first, you need this function: -
IF EXISTS ( SELECT 1FROM sys.[objects]
WHERE [name] = 'DelimitedSplit8K'
AND type = 'IF' )
BEGIN;
DROP FUNCTION [dbo].[DelimitedSplit8K];
END;
GO
CREATE FUNCTION [dbo].[DelimitedSplit8K]
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE! IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
-- enough to cover VARCHAR(8000)
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
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT 1 UNION ALL
SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
SELECT s.N1,
ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
FROM cteStart s
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
Item = SUBSTRING(@pString, l.N1, l.L1)
FROM cteLen l
;
GO
Now, here's some sample data: -
-- CREATE SAMPLE DATAIF object_id('tempdb..#testEnvironment') IS NOT NULL
BEGIN;
DROP TABLE #testEnvironment;
END;
SELECT [ID], [Path]
INTO #testEnvironment
FROM (VALUES(1,'./ACS/OTHER ACS REPORTS/XYZ/ABC'),
(2,'./ACS/OTHER ACS REPORTS/Pending Deletion(JJP)'),
(3,'./ACS/Pending Deletion(JJP)')
)a([ID], [Path]);
Now, assuming that it is only ever 5 deep, we could do this: -
SELECT ID,MAX(CASE WHEN ItemNumber = 1 THEN Item ELSE '' END) AS [1],
MAX(CASE WHEN ItemNumber = 2 THEN Item ELSE '' END) AS [2],
MAX(CASE WHEN ItemNumber = 3 THEN Item ELSE '' END) AS [3],
MAX(CASE WHEN ItemNumber = 4 THEN Item ELSE '' END) AS [4],
MAX(CASE WHEN ItemNumber = 5 THEN Item ELSE '' END) AS [5]
FROM [#testEnvironment] AS te
CROSS APPLY [dbo].[DelimitedSplit8K](te.[Path], '/') AS de
GROUP BY ID;
Which returns: -
ID 1 2 3 4 5----------- ---- ---- ---------------------- ---------------- ----
1 . ACS OTHER ACS REPORTS XYZ ABC
2 . ACS OTHER ACS REPORTS Pending Deletion
3 . ACS Pending Deletion(JJP)
That assumes that you want to return the root ".", do you actually want that bit? It also assumes that you only ever have 5 deep in your structure, we can make this "any level" using dynamic SQL if required, but without more information from you it seemed pointless to do so now.
Does that give you a starting point for getting a working solution?
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
-
If I understood your question correctly, this is likely also a solution you might consider -> Converting a delimited string of values into columns
(If not it's at least a great read)
;WITH CTE (strval)
AS
(
SELECT CONVERT(xml, '<col>' + REPLACE('/ACS/OTHER ACS REPORTS/XYZ/ABC', '/', '</col><col>') + '</col>')
)
SELECT strval.value(('/col[2]'), 'varchar(128)') 'Firstcol',
strval.value(('/col[3]'), 'varchar(128)') 'Secondol',
strval.value(('/col[4]'), 'varchar(128)') 'Thirdcol',
strval.value(('/col[5]'), 'varchar(128)') 'Fourthcol'
FROM CTE;
-
Sebastian Zeller (8/7/2014)
If I understood your question correctly, this is likely also a solution you might consider -> Converting a delimited string of values into columns(If not it's at least a great read)
Take a read through the 8K Delimited Splitter article[/url] and you'll see that XML splitters are significantly slower than the one I used in the solution above.
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
-
Cadavre (8/7/2014)
Sebastian Zeller (8/7/2014)
If I understood your question correctly, this is likely also a solution you might consider -> Converting a delimited string of values into columns(If not it's at least a great read)
Take a read through the 8K Delimited Splitter article[/url] and you'll see that XML splitters are significantly slower than the one I used in the solution above.
I didn't intend to challenge or even downgrade your solution. The sole purpose was to show the OP another possible way of doing it 🙂
-
Sebastian Zeller (8/7/2014)
Cadavre (8/7/2014)
Sebastian Zeller (8/7/2014)
If I understood your question correctly, this is likely also a solution you might consider -> Converting a delimited string of values into columns(If not it's at least a great read)
Take a read through the 8K Delimited Splitter article[/url] and you'll see that XML splitters are significantly slower than the one I used in the solution above.
I didn't intend to challenge or even downgrade your solution. The sole purpose was to show the OP another possible way of doing it 🙂
I know. . . I was just pointing you at the resource with test results that shows XML splitters are slower than the 8k splitter.
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
-
Thanks for your reply.
But
SELECT
ID,
MAX(CASE WHEN ItemNumber = 1 THEN Item ELSE '' END) AS [1],
MAX(CASE WHEN ItemNumber = 2 THEN Item ELSE '' END) AS [2],
MAX(CASE WHEN ItemNumber = 3 THEN Item ELSE '' END) AS [3],
MAX(CASE WHEN ItemNumber = 4 THEN Item ELSE '' END) AS [4],
MAX(CASE WHEN ItemNumber = 5 THEN Item ELSE '' END) AS [5]
FROM
#testEnvironment AS AB
CROSS APPLY
dbo.[DelimitedSplit8K](AB.path, '/') AS de
GROUP BY ID;
is not working for me.
I am getting
Error msg : Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '.'.
When I ran below
SELECT
ID,
MAX(CASE WHEN ItemNumber = 1 THEN Item ELSE '' END) AS [1],
MAX(CASE WHEN ItemNumber = 2 THEN Item ELSE '' END) AS [2],
MAX(CASE WHEN ItemNumber = 3 THEN Item ELSE '' END) AS [3],
MAX(CASE WHEN ItemNumber = 4 THEN Item ELSE '' END) AS [4],
MAX(CASE WHEN ItemNumber = 5 THEN Item ELSE '' END) AS [5]
FROM
#testEnvironment AS AB
CROSS APPLY
dbo.[DelimitedSplit8K]('./ACS/OTHER ACS REPORTS/XYZ/ABC', '/') AS de
GROUP BY ID;
This is run perfectly.
How can we resolve error msg
Error msg : Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '.'.
?
-
What data type is path?
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply