Solved

# EXTRACT DATA FROM A COLUMN

Posted on 2011-10-26
297 Views
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
Question by:emi_sastra

LVL 3

Expert Comment

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

LVL 6

Expert Comment

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

LVL 92

Expert Comment

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

LVL 92

Expert Comment

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

LVL 1

Author Comment

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

LVL 1

Author Comment

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

LVL 92

Expert Comment

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

LVL 1

Author Comment

Hi matthew,

It produces no record.

Thank you.
0

LVL 92

Accepted Solution

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

LVL 1

Author Comment

Great, it works.

Thank you very much for your help.
0

## Featured Post

### Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.