deeejay

SQL - Generate combinations of two columns

Mar 11th, 2014
351
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. WITH a AS (
  2.   SELECT CODE_VALUE AS CODE, DISPLAY AS DESCRIPTION, CODE_SET FROM CODE_VALUE
  3.   WHERE CODE_SET = 69 AND ACTIVE_IND = 1
  4.   UNION ALL
  5.   SELECT CODE_VALUE AS CODE, DISPLAY AS DESCRIPTION, CODE_SET FROM CODE_VALUE
  6.   WHERE CODE_SET = 71 AND ACTIVE_IND = 1
  7.   )
  8.   SELECT C1.CODE, C1.DESCRIPTION, C2.CODE, C2.DESCRIPTION
  9.   FROM a C1
  10.   CROSS JOIN a C2
  11.   WHERE C1.CODE_SET = 69 AND C2.CODE_SET = 71
  12.   --Alternate Combo: WHERE C1.CODE < C2.CODE
  13.   ORDER BY C1.CODE, C2.CODE;
Advertisement