T-sql query to extract dependency results

  • 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

  • --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

  • @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]

  • 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