Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


T-sql query to extract dependency results


T-sql query to extract dependency results

Author
Message
saidwarak01
saidwarak01
SSC Journeyman
SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)SSC Journeyman (81 reputation)

Group: General Forum Members
Points: 81 Visits: 274
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

COL1 COL2
5.N.1 5.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.4 5.N.5 , 5.N.6 , 5.N.9
5.N.5 5.N.6 , 5.N.9
5.N.6 5.N.9
5.N.10 5.N.11 , 5.N.12
5.N.11 5.N.12
5.N.13 5.N.11 , 5.N.12
5.N.14 5.N.15
Mitesh Oswal
Mitesh Oswal
SSC Eights!
SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)

Group: General Forum Members
Points: 830 Visits: 653
--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
ABC T2
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
CTE T2
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.ROW AND
CTE.ID = CTE2.ID
ORDER BY
COL1







Regards,
Mitesh OSwal
+918698619998
Minnesota - Viking
Minnesota - Viking
SSC Veteran
SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)SSC Veteran (257 reputation)

Group: General Forum Members
Points: 257 Visits: 564
@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


Today is the tomorrow you worried about yesterday:-)

Mitesh Oswal
Mitesh Oswal
SSC Eights!
SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)SSC Eights! (830 reputation)

Group: General Forum Members
Points: 830 Visits: 653
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search