Solved

Combinations from SQL

Posted on 2012-04-12
1
94 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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

863 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now