I have a Tree table with three Columns (like user_id,advisor_Id,Tree).
Table Name:tbl_treeIntro
My table records are like this,
user_id advisor_Id Tree
7 3 3,1,0
9 4 4,1,0
12 5 5,2,0
I have tried split function,which i have refered from google.
My Split fuction script is here,
CREATE FUNCTION [dbo].[Split]
(
@String NVARCHAR(4000),
@Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
WITH Split(stpos,endpos)
AS(
SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
UNION ALL
SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
FROM Split
WHERE endpos > 0
)
SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
FROM Split
)
GO
And Query i have tried is,
declare @string nvarchar(max);
select @string=Tree from tbl_treeIntro;
SELECT * FROM dbo.Split(@string, ',')
But i showing result for only single userId.
This is my output,
ID Data
1 5
2 2
3 0
But i need results for all userId.
My Expecting Output:
ID Data UserId
1 5 12
2 2 12
3 0 12
4 4 9
5 1 9
6 0 9
7 3 7
8 1 7
9 0 7
Here i have provided reference table script and table values.
CREATE TABLE [dbo].[tbl_treeIntro](
[t_id] [int] IDENTITY(1,1) NOT NULL,
[user_id] [bigint] NULL,
[advisor_id] [bigint] NULL,
[Tree] [nvarchar](max) NULL,
CONSTRAINT [PK_tbl_treeIntro] PRIMARY KEY CLUSTERED
(
[t_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into tbl_treeIntro(user_id,advisor_id,Tree) values(7,3,'3,1,0')
insert into tbl_treeIntro(user_id,advisor_id,Tree) values(9,4,'4,1,0')
insert into tbl_treeIntro(user_id,advisor_id,Tree) values(12,5,'5,2,0')
Thanks in Advance,
R@JEESH