0

There's a dropdown listing every possible combination of a specific column. I'm trying to imitate the dropdown so I can get it in the right order with the same key values. I believe its using bit counting.

Example

CategoryID, Name
----        ----
1           Brody
2           Chad
3           Stacy
4           Jessica

I want it outputted like

Brody
Chad
Brody Chad
Stacy
Stacy Brody
Stacy Chad
Stacy Brody Chad
Jessica
Jessica Brody
Jessica Chad
Jessica Brody Chad
Jessica Stacy
Jessica Brody Stacy
Jessica Chad Stacy
Jessica Brody Chad Stacy

I tried doing something similar to Pavel Urbančík answer.

SQL - Find all possible combination

KamSami
  • 387
  • 1
  • 4
  • 14

1 Answers1

1

This is a strange requirement and I don't know of a way to make this simple. You could make this dynamic based on the number of rows in the table but the row count will get overwhelming very quickly.

Here is one way you could tackle this. It isn't very efficient but the requirements here are sufficiently against the norm for relational data. If this is a persistent list I would store this in another table and regenerate it on an as need basis instead of running this kind of thing over and over.

This at least returns the results you stated you want although the names are not in the same order as I didn't really understand the logic of how those names were ordered within each row.

declare @Something table (CategoryID int, Name varchar(10))
insert @Something values
(1, 'Brody')
, (2, 'Chad')
, (3, 'Stacy')
, (4, 'Jessica')

select Name1 = s.Name
    , Name2 = null
    , Name3 = null
    , Name4 = null
from @Something s

UNION ALL

select s.Name
    , s2.Name
    , null
    , null
from @Something s
cross join @Something s2
where s.Name < s2.Name

UNION ALL

select s.Name
    , s2.Name
    , s3.Name
    , null
from @Something s
cross join @Something s2
cross join @Something s3
where s.Name < s2.Name
    and s.Name < s3.Name
    and s2.Name < s3.Name

UNION ALL

select s.Name
    , s2.Name
    , s3.Name
    , s4.Name
from @Something s
cross join @Something s2
cross join @Something s3
cross join @Something s4
where s.Name < s2.Name
    and s.Name < s3.Name
    and s2.Name < s3.Name
    and s.Name < s4.Name
    and s2.Name < s4.Name
    and s3.Name < s4.Name
Sean Lange
  • 33,028
  • 3
  • 25
  • 40
  • I wonder if this is something that could use a recursive CTE. You'd have to keep track of the number of levels, where the count of levels is the number of rows to begin with. (yuck). – TechGnome Aug 20 '19 at 19:01
  • @TechGnome I don't see how you could do this without increasing the number of joins for each new level. Not sure how you would utilize an rCTE for that. I pondered that myself but nothing seemed to make sense there. – Sean Lange Aug 20 '19 at 19:03
  • yeah, I'll admit, I didn't spend more than 5 minutes thinking about it before the cramp started setting in... I just don't like all the unions, so I figure there's got to be a better way. I know I've done some funkay stuff with rCTEs before so I was musing out loud if it might work. Unfortunately I don't have access to SQL Server where I could try it out. – TechGnome Aug 20 '19 at 19:16
  • @TechGnome it could probably be done a with a few triangular joins which is kind of what I am replicating now that I think about it. Not sure it would result in a better execution plan but at least there would be a ton less code. – Sean Lange Aug 20 '19 at 19:31