Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Combinations from SQL

Posted on 2012-04-12
1
Medium Priority
?
103 Views
Last Modified: 2014-02-21
Hi,

I am looking to work out all combinations of a given numeric field in a table.  Let’s assume that I have 3 different numbers in this data set.   To find all permutations I have this piece of code, which joins the same table in twice to account for the 3 different numbers:

select             c.item,
            d.item,
            e.item
from            dbo.tbl_MyItems as c
inner join                       dbo.tbl_MyItems as d
on            c.ID = d.ID
inner join                  dbo.tbl_MyItems as e
on            c.ID = e.ID
where            c.id = '1'
group by                        c.item,
            d.item,
            e.item

It returns 27 rows (all permutations).  However, I am not concerned with the order of the numbers just that I have the number once even where it is repeated.  So my evaluated output would look like the following, giving me 7 unique rows:

1:        1      1      1            1
2:        1      1      2            1,2
3:        1      1      3            1,3
4:        1      2      1            Same as row 2
5:        1      2      2            Same as row 2
6:        1      2      3            1,2,3
7:        1      3      1            same as row 3
8:        1      3      2            same as row 6
9:        1      3      3            same as row 3
10:        2      1      1            same as row 2
11:        2      1      2            same as row 2
12:        2      1      3            same as row 6
13:        2      2      1            same as row 2
14:        2      2      2            2
15:        2      2      3            2,3
16:        2      3      1            same as row 6
17:        2      3      2            same as row 15
18:        2      3      3            same as row 15
19:        3      1      1            same as row 3
20:        3      1      2            same as row 6
21:        3      1      3            same as row 3
22:        3      2      1            same as row 6
23:        3      2      2            same as row 15
24:        3      2      3            same as row 15
25:        3      3      1            same as row 3
26:        3      3      2            same as row 15
27:        3      3      3            3

I appreciate that I have gone one step further than asking for combinations here (of which there are 10) and have 'normalised' (for want of a better word!) the set, but is there any way of asking SQL to output this (just the combinations) rather than full list of permutations?

Many thanks!!
0
Comment
Question by:TheDV
1 Comment
 
LVL 23

Accepted Solution

by:
wdosanjos earned 2000 total points
ID: 37841848
Please try this:
declare @tbl_MyItems table (item int)

insert into @tbl_MyItems values (1)
insert into @tbl_MyItems values (2)
insert into @tbl_MyItems values (3)

select  distinct
        c.item,
        case when e.item > c.item and e.item > d.item then e.item else null end as item,
        case when d.item > c.item and d.item < e.item then d.item else null end as item
  from  @tbl_MyItems as c,
        @tbl_MyItems as d,
        @tbl_MyItems as e
 
/* Output

item        item        item
----------- ----------- -----------
1           NULL        NULL
1           2           NULL
1           3           NULL
1           3           2
2           NULL        NULL
2           3           NULL
3           NULL        NULL

*/

Open in new window

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question