-3

I have a table with following fields and data.

id  type value
1   1    1
2   2    1
3   2    2
4   2    3

After running the query I should get result in temp table like below

1,1
1,2
1,3

and if i have multiple value in type 1 then it should do the same

so if the data is

 id  type value
 1   1    1
 2   1    2
 3   2    1
 4   2    2
 5   2    3

I should get result

1,1
1,2
1,3
2,1
2,2
2,3

Appreciate any help on this.

atif
  • 1,137
  • 7
  • 22
  • 35
  • 3
    Unclear!!! what is logic behind output – Amritpal Singh Mar 16 '12 at 22:29
  • Can you guess this sequence: 15, 16, 17, 23, 25, 26, 27, 28, 29, 30 https://oeis.org/A090461 – Glenn Mar 16 '12 at 22:33
  • based on Type, I want to repeate the result set. So if I need all the combination of type 1 with type 2. (value of type 1 are (1,2) and values of type 2 are (1,2,3) i am looking to get 1,1 1,2 1,3 2,1 2,2 2,3). – atif Mar 16 '12 at 22:42
  • BTW Amritpal and glenn fyi, this is called Cartesian Product. So glenn no need to guess 15, 16, 17, 23, 25, 26, 27, 28, 29, 30... – atif Mar 17 '12 at 05:28
  • Here are couple of links which explain it in detail http://blogs.msdn.com/b/ericlippert/archive/2010/06/28/computing-a-cartesian-product-with-linq.aspx http://stackoverflow.com/questions/710670/c-sharp-permutation-of-an-array-of-arraylists – atif Mar 17 '12 at 05:33

2 Answers2

1

That looks like:

SELECT t1.value, t2.value
  FROM table t1
  JOIN table t2
    ON t1.type = 1
   AND t2.type = 2
;

which can also be written as:

SELECT t1.value, t2.value
  FROM ( SELECT value FROM table WHERE type = 1 ) t1
 CROSS
  JOIN ( SELECT value FROM table WHERE type = 2 ) t2
;

(The former is simpler, but the latter makes clearer that this is semantically a CROSS JOIN, since the join doesn't actually depend on any relationship between the joined records.)

ruakh
  • 175,680
  • 26
  • 273
  • 307
0
SELECT ISNULL(CAST([type] as varchar(20)), '')
     + ','
     + ISNULL(CAST([value] as varchar(20)), '')
     AS CommaSeparated 
FROM types
Anthony Faull
  • 17,549
  • 5
  • 55
  • 73