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

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
Asked:
emi_sastra
1 Solution
 
clampsCommented:
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
 
jonaskaCommented:
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

0
 
Patrick MatthewsCommented:
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

0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
Patrick MatthewsCommented:
My suggestion does not assume that the IDs are any particular length.  It just finds the "last" item in the UplineLineage.
0
 
emi_sastraAuthor Commented:
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.
0
 
emi_sastraAuthor 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
 
Patrick MatthewsCommented:
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.
0
 
emi_sastraAuthor Commented:
Hi matthew,

It produces no record.

Thank you.
0
 
Patrick MatthewsCommented:
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

Open in new window



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

Open in new window


Open in new window

0
 
emi_sastraAuthor Commented:
Great, it works.

Thank you very much for your help.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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