• Status: Solved
• Priority: Medium
• Security: Public
• Views: 300

# 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.
0
emi_sastra
1 Solution

Commented:
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?
0

Commented:
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
``````
0

Commented:
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 '/%'
``````
0

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

Author Commented:

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.
0

Author Commented:
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
0

Commented:
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
``````

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

Author Commented:
Hi matthew,

It produces no record.

Thank you.
0

Commented:
Sorry, try this:

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

It worked for me, as this test script demonstrates:

``````CREATE TABLE #SomeTable (MemberId varchar(100), UplineLineage varchar(100))

INSERT INTO #SomeTable (MemberId, UplineLineage)
SELECT 'A0000001', NULL UNION ALL
SELECT 'A0000002', '/A0000001' UNION ALL
SELECT 'A0000003', '/A0000001' UNION ALL
SELECT 'A0000004', '/A0000001/A0000002' UNION ALL
SELECT 'A0000005', '/A0000001/A0000002' UNION ALL
SELECT 'A0000006', '/A0000001/A0000003' UNION ALL
SELECT 'A0000007', '/A0000001/A0000003'

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

DROP TABLE #SomeTable
``````
``````
``````
0

Author Commented:
Great, it works.

Thank you very much for your help.
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.