Solved

Combinations from SQL

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

809 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