## Two Variable Combination Permutation Calculation

 Author Message jfanning Forum Newbie Group: General Forum Members Points: 5 Visits: 10 Hello-I have a table, DEV_QRY_QC_HCC_131. It has two columns qry_id and hic_number (a member ID). The table has 12,702 records. There are 16 distinct qry_id values (e.g. qry_1,qry_2,etc...) and 7,090 distinct hic_number values (e.g. 123456789A, 987654321A, 456789123B, etc...) I would like to create a subset table with two columns qry_id_2combo and hic_number, in which qry_id_2combo is two distinct qry_id values, like this:qry_id_2combo hic_numberqry_1&2 123456789Aqry_1&2 987654321Aqry_1&3 456789123BI'm thinking I'll need to use cursors and loops, which really isn't TSQL is it? It's PL/SQL... SwePeso SSCrazy Group: General Forum Members Points: 2913 Visits: 3431 `SELECT q1.qry_id, q2.qry_id, h.hic_numberFROM ( SELECT DISTINCT qry_id FROM dbo.DEV_QRY_QC_HCC_131 ) AS q1INNER JOIN ( SELECT DISTINCT qry_id FROM dbo.DEV_QRY_QC_HCC_131 ) AS q2 ON q2.qry_id > q1.qry_idCROSS JOIN ( SELECT DISTINCT hic_number FROM dbo.DEV_QRY_QC_HCC_131 ) AS h` N 56°04'39.16"E 12°55'05.25" mister.magoo SSCrazy Group: General Forum Members Points: 2867 Visits: 7854 Do you mean that out of the 16 qry_id values in the table, you just want rows that match two of those qry_id values.e.g. For table DEV_QRY_QC_HCC_131 containing these rows, you only want to select out where qry_id is 1 or 3 for example?`qry_id hic_number1 12345461 435676756A2 7643213433 794674hkf4 213126786638712`giving these results:`qry_id hic_number1 12345461 435676756A3 794674hkf` MM`select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);`Forum Etiquette: How to post Reporting Services problemsForum Etiquette: How to post data/code on a forum to get the best help - by Jeff ModenHow to Post Performance Problems - by Gail Shaw jfanning Forum Newbie Group: General Forum Members Points: 5 Visits: 10 Thanks, I'll try this... jfanning Forum Newbie Group: General Forum Members Points: 5 Visits: 10 mister.magoo (2/16/2011)Do you mean that out of the 16 qry_id values in the table, you just want rows that match two of those qry_id values.e.g. For table DEV_QRY_QC_HCC_131 containing these rows, you only want to select out where qry_id is 1 or 3 for example?`qry_id hic_number1 12345461 435676756A2 7643213433 794674hkf4 213126786638712`giving these results:`qry_id hic_number1 12345461 435676756A3 794674hkf`No, I'm looking for two-way qry_id combinations, by hic_number.... SwePeso SSCrazy Group: General Forum Members Points: 2913 Visits: 3431 SELECT MIN(qry_id), MAX(qry_id), hic_numberFROM Table1GROUP BY hic_numberHAVING COUNT(*) = 2 N 56°04'39.16"E 12°55'05.25" Evil Kraig F SSCertifiable Group: General Forum Members Points: 6797 Visits: 7660 Can you post the ddl and some sample data that would provide us the background for what you're looking to generate:`qry_id_2combo hic_numberqry_1&2 123456789Aqry_1&2 987654321Aqry_1&3 456789123B`We're guessing as to what the source data looks like exactly and that's not helping. See the first link in my sig if you'd like some assistance with that. - Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake. For better assistance in answering your questions | Forum NetiquetteFor index/tuning help, follow these directions. |Tally TablesTwitter: @AnyWayDBA jfanning Forum Newbie Group: General Forum Members Points: 5 Visits: 10 Craig Farrell (2/17/2011)Can you post the ddl and some sample data that would provide us the background for what you're looking to generate:`qry_id_2combo hic_numberqry_1&2 123456789Aqry_1&2 987654321Aqry_1&3 456789123B`We're guessing as to what the source data looks like exactly and that's not helping. See the first link in my sig if you'd like some assistance with that.Sample starting data:hic_number qry_id00005A 913081300005A 913085000010A 913081300010A 913085000010A 91521600010A 91541600021A 913081300021A 9130850The end result of this sample would be:hic_number qry_2combo_id 00005A 9130813+9130850 00010A 9130813+9130850 00010A 9130813+915216 00010A 9130813+915416 00010A 9130850+915216 00010A 9130850+915416 00010A 915216+91541600021A 9130813+9130850 SwePeso SSCrazy Group: General Forum Members Points: 2913 Visits: 3431 `DECLARE @Sample TABLE ( hic CHAR(6) NOT NULL, qry VARCHAR(7) NOT NULL )INSERT @SampleSELECT '00005A', '9130813' UNION ALLSELECT '00005A', '9130850' UNION ALLSELECT '00010A', '9130813' UNION ALLSELECT '00010A', '9130850' UNION ALLSELECT '00010A', '915216' UNION ALLSELECT '00010A', '915416' UNION ALLSELECT '00021A', '9130813' UNION ALLSELECT '00021A', '9130850'SELECT s.hic, s.qry, f.qryFROM @Sample AS sCROSS APPLY ( SELECT x.qry FROM @Sample AS x WHERE x.hic = s.hic AND x.qry > s.qry ) AS f` N 56°04'39.16"E 12°55'05.25" jfanning Forum Newbie Group: General Forum Members Points: 5 Visits: 10 SwePeso (2/17/2011)`DECLARE @Sample TABLE ( hic CHAR(6) NOT NULL, qry VARCHAR(7) NOT NULL )INSERT @SampleSELECT '00005A', '9130813' UNION ALLSELECT '00005A', '9130850' UNION ALLSELECT '00010A', '9130813' UNION ALLSELECT '00010A', '9130850' UNION ALLSELECT '00010A', '915216' UNION ALLSELECT '00010A', '915416' UNION ALLSELECT '00021A', '9130813' UNION ALLSELECT '00021A', '9130850'SELECT s.hic, s.qry, f.qryFROM @Sample AS sCROSS APPLY ( SELECT x.qry FROM @Sample AS x WHERE x.hic = s.hic AND x.qry > s.qry ) AS f`Thanks! Great stuff!