CODEWITHSUNDEEP
sqlsql-server
Jab B
Jab B

Reputation: 1

Return all possible combination of values on Rows in SQL

How do I return a list of all combinations of values in rows? The expected input and result would be in T-SQL as below,

   Id          Category        Value
  ----        --------      --------
Source1        Company        GreenChef
source1        Company        Chef-client
source1        Role           Master
source1        Level          key1
source1        Level          key2
source1        Level          key3

The expected result would be like this.

Id        Company     Level       Role 
--        -------     -----      ------
source1   GreenChef   Key1       Master 
source1   GreenChef   Key2       Master
source1   GreenChef   Key3       Master
Source1   Chef-client Key1       Master
Source1   Chef-client Key2       Master
Source1   Chef-client Key3       Master

Upvotes: 0

Views: 121

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If you know the values in category, then this is a Cartesian product. However, you presumably want this per id:

select i.id, c.company, r.role, l.level
from (select distinct id
      from t
     ) i left join
     (select distinct id, value as company
      from t
      where category = 'Company'
     ) c
     on c.id = i.id left join
     (select distinct id, value as role
      from t
      where category = 'Role'
     ) r 
     on r.id = i.id left join
     (select distinct id, value as level
      from t
      where category = 'Level'
     ) l
     on l.id = i.id;

This starts with a full list of ids, which might just be a convenience. It uses left join, so it will return all id, even those that are missing one of the roles.

If you do not know all the categories, the problem can still be solved using recursive CTEs. For that, I would suggest asking a new question.

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 94859

You need to self join the table:

with companies as (select * from mytable where category = 'Company')
   , roles     as (select * from mytable where category = 'Role')
   , levels    as (select * from mytable where category = 'Level')
select c.id, c.value as company, r.value as role, l.value as level
from companies c
join roles r on r.id = c.id
join levels l on l.id = c.id;

It would be more elegant to use USING instead of ON here, but SQL Server does not support it. This doesn't matter much with inner joins, but if any of the attributes can be missing, you'll need a full outer join. And this gets quite clumsy with more than two tables (derived ones in your case) on the same column without USING.

Upvotes: 0

Serg
Serg

Reputation: 22811

If the list of categories is fixed then

select t.id, t1.value as Company, t2.value as Level, t3.value as Role
from (select distinct id 
      from mytable 
) t
cross apply (
   select distinct Value 
   from mytable t1
   where Category ='Company'
   and t1.id = t.id) t1
cross apply (
   select distinct Value 
   from mytable t2
   where Category ='Level'
   and t2.id = t.id) t2
cross apply (
   select distinct Value 
   from mytable t3
   where Category ='Role'
   and t3.id = t.id) t3

Otherwise you may use dynamic sql.

Upvotes: 0

Related Questions