0

I have a single table that has the Client field and the Owner of the Client field, so for example "Amazon UK" and "Amazon.com, Inc." respectively. What also can happen is the Owner of the Client can have a Owner also so would appear in the Client field and this can occur again and again but will eventually stop.

Example:

Client          Owner of the Client
123             234
234             345
345             456
567             678
789             890

Therefore I am wondering is if recursive SQL is the best way for this? Is there also a way so it appears in separate columns for each e.g.

Client          Owner of Client          Owner of Client         Owner of Client
123             234                      345                     456
567             678                      NULL                    NULL
789             890                      NULL                    NULL

Thanks in advance for any input on this.

GSerg
  • 76,472
  • 17
  • 159
  • 346
Lewis Shaw
  • 1
  • 2
  • 1
    Does this answer your question? [SQL Server 2005 Pivot on Unknown Number of Columns](https://stackoverflow.com/questions/213702/sql-server-2005-pivot-on-unknown-number-of-columns) – GSerg Jan 04 '20 at 11:41

2 Answers2

0

It depends on the depth, how many owners can have another owner...

A simple LEFT JOIN solution is best if you want to show results on 1 line:

SELECT c1.* , c2.owner, c3.owner, c4.owner
FROM client_owner c1
left join client_owner c2 on c2.client = c1.owner
left join client_owner c3 on c3.client = c2.owner
left join client_owner c4 on c4.client = c3.owner

output:

client      owner       owner       owner       owner
----------- ----------- ----------- ----------- -----------
123         234         345         456         NULL
234         345         456         NULL        NULL
345         456         NULL        NULL        NULL
567         678         NULL        NULL        NULL
789         890         NULL        NULL        NULL
Luuk
  • 12,245
  • 5
  • 22
  • 33
  • Yeah that's how I'm currently doing it, it's just knowing when the depth ends. Obviously can go over the top with that to ensure we don't miss that but didn't know if there was a more cleaner/accurate way to do so. – Lewis Shaw Jan 04 '20 at 11:41
  • A SQL statement cannot have a dynamic number of columns, they must be defined, @LewisShaw . If there's no maximum, you'll need to use dynamic SQL, and could get "messy". – Thom A Jan 04 '20 at 11:49
0

If you need the values in separate columns, then joins or some type of dynamic SQL is a good approach. Unfortunately, SQL Server doesn't support arrays.

But you can put the values in a string (or into a JSON or XML object if you want). This looks like:

with cte as (
      select t.client, convert(varchar(max), t.owner) as owners, owner as last_owner, 1 as lev
      from t
      where not exists (select 1 from t t2 where t2.owner = t.client)
      union all
      select cte.client, concat(cte.owners, ',', t.owner), t.owner, lev + 1
      from cte join
           t
           on t.client = cte.last_owner
     )
select top (1) with ties client, owners
from cte
order by row_number() over (partition by client order by lev desc);

Here is a db<>fiddle.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Client `345` with it's owner `456` is missing, is this correct? – Luuk Jan 04 '20 at 13:19
  • @Luuk . . . I'm lost on your comment. `456` is in the owners list for `123` just as in the OPs desired results. – Gordon Linoff Jan 04 '20 at 17:53
  • I meant to say that `345` is a client of owner `456`, and that combination is missing. I don't know if that is correct, or not. – Luuk Jan 04 '20 at 18:03
  • with cte as ( select PR.SubjectPartyId, convert(varchar(max), PR.ObjectPartyId) as 'GUO', PR.ObjectPartyId as last_owner, PR.PartyRelationshipTypeId, 1 as lev from rpt.vwPartyRelationship PR where not exists (select 1 from rpt.vwPartyRelationship PR2 WHERE PR2.ObjectPartyId = PR.SubjectPartyId) union all select cte.SubjectPartyId, concat(cte.GUO, ',', PR.ObjectPartyId), PR.ObjectPartyId, PR.PartyRelationshipTypeId, lev + 1 from cte join rpt.vwPartyRelationship PR on PR.SubjectPartyId = cte.last_owner ) – Lewis Shaw Jan 04 '20 at 22:16
  • I tired what you said @GordonLinoff replacing client with subjectpartyid and owner with objectpartyid but it doesn't seem to be working. Have I messed up somewhere in my code? – Lewis Shaw Jan 04 '20 at 22:17
  • @LewisShaw . . . You can check the db<>fiddle which works and is based exactly on the information you provided in the question. – Gordon Linoff Jan 04 '20 at 22:51