asked on

EXTRACT DATA FROM A COLUMN

Hi All,

I have below data :

MemberId      UplineLineage
A0000001
A0000002      /A0000001
A0000003      /A0000001
A0000004      /A0000001/A0000002
A0000005      /A0000001/A0000002
A0000006      /A0000001/A0000003
A0000007      /A0000001/A0000003

etc

I want query it into :

Upline Downline
A0000001      A0000002
A0000001      A0000003
A0000002      A0000004
A0000002      A0000005
A0000003      A0000006
A0000003      A0000007

etc

How could I do it ?

Is it possible by join itself ?

Thank you.

clamps

should Upline A0000001 also refer to A0000004, A0000005, A0000006, A0000007?
Since in your Table it appears in UplineLineage.... or always only the last value?
How many values can be in UplineLineage? 0,1,2 ? or more than that?

jonaska

This should do the trick, as long as the strings between (and after) the slashes are always 8 letters long:

SELECT RIGHT(UplineLineage, 8) AS Upline
,MemberId AS Downline
FROM #mytest
WHERE UplineLineage IS NOT NULL

Open in new window

Patrick Matthews

There's actually no need for a join:

SELECT REVERSE(LEFT(REVERSE(UplineLineage), CHARINDEX('/', REVERSE(UplineLineage)) - 1)) AS Upline, MemberID AS DownLine
FROM SomeTable
WHERE UplineLineage LIKE '/%'

Open in new window

Patrick Matthews

My suggestion does not assume that the IDs are any particular length. It just finds the "last" item in the UplineLineage.

emi_sastra

ASKER

Hi jonaska,

How many values can be in UplineLineage? 0,1,2 ? or more than that?
Could be more than that, depends on the level of a member.

Thank you.

emi_sastra

ASKER

Hi All,

I am so sorry. Below are the needed result :

I want query it into :

Upline Downline
A0000001 A0000002
A0000001 A0000003
A0000001 A0000004
A0000001 A0000005
A0000001 A0000006
A0000001 A0000007
A0000002 A0000004
A0000002 A0000005
A0000003 A0000006
A0000003 A0000007

etc

Patrick Matthews

Now a self-join is needed.

SELECT t1.MemberId AS Upline, t2.MemberId AS Downline
FROM SomeTable t1 INNER JOIN
    SomeTable t2 ON t2.MemberId + '/' LIKE '/' + t1.MemberId + '/'
ORDER BY t1.MemberId, t2.MemberId

Open in new window

Adding in the delimiter character in the join expression as above protects against false positives.

emi_sastra

ASKER

Hi matthew,

It produces no record.

Thank you.

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

emi_sastra

ASKER

Great, it works.

Thank you very much for your help.