Solved

Combinations from SQL

Posted on 2012-04-12
1
100 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

752 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