January 27, 2010 at 5:02 pm
Hi Everyone,
I am facing a peculiar scenario of data. I actually need to find out the dependency values on col1 data and its correspondence dependies.
Please help me in this matter. Thanks in advance.
create table abc(col1 varchar(20),col2 varchar(20))
insert into abc values('5.N.1','5.N.4')
insert into abc values('5.N.4','5.N.5')
insert into abc values('5.N.5','5.N.6')
insert into abc values('5.N.6','5.N.9')
insert into abc values('5.N.1','5.N.10')
insert into abc values('5.N.2','5.N.4')
insert into abc values('5.N.10','5.N.11')
insert into abc values('5.N.11','5.N.12')
insert into abc values('5.N.13','5.N.11')
insert into abc values('5.N.14','5.N.15')
The output should be in the format of
COL1COL2
5.N.15.N.4 , 5.N.5 , 5.N.6 , 5.N.9
5.N.1 5.N.10 , 5.N.11 , 5.N.12
5.N.45.N.5 , 5.N.6 , 5.N.9
5.N.55.N.6 , 5.N.9
5.N.65.N.9
5.N.105.N.11 , 5.N.12
5.N.115.N.12
5.N.135.N.11 , 5.N.12
5.N.145.N.15
January 27, 2010 at 11:32 pm
--create table abc(col1 varchar(20),col2 varchar(20))
--
--insert into abc values('5.N.1','5.N.4')
--insert into abc values('5.N.4','5.N.5')
--insert into abc values('5.N.5','5.N.6')
--insert into abc values('5.N.6','5.N.9')
--insert into abc values('5.N.1','5.N.10')
--insert into abc values('5.N.2','5.N.4')
--insert into abc values('5.N.10','5.N.11')
--insert into abc values('5.N.11','5.N.12')
--insert into abc values('5.N.13','5.N.11')
--insert into abc values('5.N.14','5.N.15')
--
--SELECT * FROM abc
;WITH CTE AS
(
SELECT
T1.COL1,CONVERT(VARCHAR(1000),T1.Col2) AS ColCheck,T1.COL2,0 AS ID,
ROW_NUMBER() OVER(ORDER BY T1.COL1,T2.COL2) AS ROW
FROM
Abc T1
LEFT JOIN
ABCT2
ON
T1.COl2 =T2.Col1
UNION ALL
SELECT
T2.COL1,CONVERT(VARCHAR(1000),ColCheck+','+T1.Col2 ) AS ColCheck,T1.COL2,ID+1 AS ID,
T2.ROW
FROM
Abc T1
INNER JOIN
CTET2
ON
T1.COl1 =T2.COL2
)
,CTE2 AS
(
SELECT
ROW,MAX(ID) AS ID
FROM
CTE
GROUP BY
ROW
)
SELECT COL1,COLCheck AS COL2 FROM CTE INNER JOIN CTE2 ON
CTE.ROW=CTE2.ROWAND
CTE.ID=CTE2.ID
ORDER BY
COL1
Regards,
Mitesh OSwal
+918698619998
May 5, 2010 at 6:21 pm
@Mitesh
Can you tell me what exactly you did. I see some joins n unions there .
Like whats the logic ? (Not syntactically)
n by the by the code shows some syntax errors too
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]
May 6, 2010 at 12:25 am
hi,
the syntax error might be cause of table is not exists please create table which is commented,
Logic: in first cte I find out the chain depends upon the col1.
and cte2 i tried to find maximum chain which is exist against col1.
Regards,
Mitesh OSwal
+918698619998
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply