Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

T-sql query to extract dependency results Expand / Collapse
Author
Message
Posted Wednesday, January 27, 2010 5:02 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, October 9, 2012 10:45 AM
Points: 77, 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
Post #854833
Posted Wednesday, January 27, 2010 11:32 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 12:51 AM
Points: 790, Visits: 637
--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
Post #854943
Posted Wednesday, May 5, 2010 6:21 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 17, 2014 3:54 PM
Points: 211, Visits: 520
@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
Post #916599
Posted Thursday, May 6, 2010 12:25 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 12:51 AM
Points: 790, Visits: 637
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
Post #916683
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse