Solved

Combinations from SQL

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL JOIN 6 39
GRANT, REVOKE, DENY 4 20
SQL query to summarize items per month 5 28
SQL Transaction logs 8 10
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…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

747 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

13 Experts available now in Live!

Get 1:1 Help Now