February 17, 2012 at 10:18 am
Hi All,
I have a table with two columns ID, Path as following. please excuse the formatting. please check the attached image for better formatted table structure
ID Path
11 .16
13 .11.13
4 .10.4
6 .1.3.6
14 .7.899
9 .10.3.4.600.7
34 .76.1
And am trying to build a t-sql query which will return results as following
IDPath Level 1Level 2Level 3Level 4Level 5
11.1616
13.11.131113
4.10.4104
6.1.3.6136
14.7.8997899
9.10.3.4.600.710346007
34.76.1761
Notice path always start with a dot and ends without a dot! And the max path level will be 5.
Tries using SUBSTRING and CHARINDEX but was not able to get desired results. Any help is appreciated. Thanks in advance.
February 17, 2012 at 10:33 am
It looks like you're looking for a split string function and a CrossTab solution.
One solution could look like this:
DECLARE @tbl TABLE
(
ID INT,
[PATH] VARCHAR(30)
)
INSERT INTO @tbl
SELECT 11 ,'.16' UNION ALL
SELECT 13 ,'.11.13' UNION ALL
SELECT 4 ,'.10.4' UNION ALL
SELECT 6 ,'.1.3.6' UNION ALL
SELECT 14 ,'.7.899' UNION ALL
SELECT 9 ,'.10.3.4.600.7' UNION ALL
SELECT 34 ,'.76.1'
SELECT
ID,
[PATH],
MAX(CASE WHEN ItemNumber=1 THEN Item ELSE '' END) AS Level1,
MAX(CASE WHEN ItemNumber=2 THEN Item ELSE '' END) AS Level2,
MAX(CASE WHEN ItemNumber=3 THEN Item ELSE '' END) AS Level3,
MAX(CASE WHEN ItemNumber=4 THEN Item ELSE '' END) AS Level4,
MAX(CASE WHEN ItemNumber=5 THEN Item ELSE '' END) AS Level5
FROM @tbl t
CROSS APPLY dbo.DelimitedSplit8K(STUFF([PATH],1,1,''),'.')
GROUP BY ID,[PATH]
A link to the DelimitedSplit8K can be found in my signature as well as the link to the CrossTab concept.
February 17, 2012 at 11:59 am
Hey Lutz,
Thanks for you response. but the data i gave you was just a sample. i need to this on a table with millions of IDs and associated Paths with them IDs. and i guess the above query will not work in my case?
Really appreciate your help..
Danny
February 17, 2012 at 12:10 pm
What makes you think this query would not work?
Did you gave it a try?
If so, what errors did you get?
February 17, 2012 at 12:30 pm
Hey Lutz,
sorry my bad! i guess i have to look at the DelimitedSplit8K link first and then run the above query?
by the way when i run the query i got the following error
Invalid object name 'dbo.DelimitedSplit8K'.
Danny
February 17, 2012 at 12:50 pm
go the link Lutz provided for DelimitedSplit8K
read the article....get the code...create the DelimitedSplit8K function
re run Lutz's code
...pls post back if still having problems
kind regards
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 17, 2012 at 3:13 pm
Hi All,
I finally managed to write the code .Thank you so much for all your support.Really appreciate it.
I found a similar approach as LutzM suggested. see following
--Start--
CREATE FUNCTION dbo.fn_nums(@n AS bigint) RETURNS TABLE AS
RETURN
WITH
L0 AS(SELECT 1 AS c UNION ALL SELECT 1),
L1 AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
L2 AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
L3 AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
L4 AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
L5 AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
SELECT TOP (@n) n AS Number FROM Nums WHERE n <= @n ORDER BY n;
GO
-- Erland's split function
CREATE FUNCTION dbo.inline_split_me(@param nvarchar(MAX))
RETURNS TABLE AS
RETURN(
SELECT
ROW_NUMBER() OVER(ORDER BY Number) AS pos,
ltrim(
rtrim(
convert(nvarchar(4000), substring(@param, Number,
charindex(N',' COLLATE Slovenian_BIN2, @param + convert(nvarchar(MAX), N','), Number) - Number)
))) AS Value
FROM dbo.fn_nums(convert(int, len(@param)))
WHERE substring(convert(nvarchar(MAX), N',') + @param, Number, 1) = N',' COLLATE Slovenian_BIN2
)
GO
DECLARE @T TABLE (
id int NOT NULL IDENTITY(1, 1) UNIQUE,
data varchar(max)
);
insert @T values('DEF,KHL,MNO');
insert @T values('DEF,KHL,MNO,LKJ,MNJ,BKS');
SELECT
*
FROM
@T AS T
OUTER APPLY
dbo.inline_split_me(T.data) AS S
PIVOT
(
MAX(Value)
FOR pos IN ([1], [2], [3], [4], [5], [6])
) AS P;
GO
DROP FUNCTION dbo.inline_split_me, dbo.fn_nums;
GO
--- END----
Original source - http://social.msdn.microsoft.com/Forums/en/transactsql/thread/b01291e9-03f7-457c-bcd2-f1241b0aac3f
Replacing the
insert @T values('DEF,KHL,MNO');
insert @T values('DEF,KHL,MNO,LKJ,MNJ,BKS');
with my select statement containing the field i wanted to parse did the magic.hope this helps someone with similar issue. cheers....
Danny
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply