Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Two Variable Combination Permutation Calculation Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, February 16, 2011 3:06 PM
 Forum Newbie Group: General Forum Members Last Login: Monday, June 04, 2012 3:46 PM 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...
Post #1065328
 Posted Wednesday, February 16, 2011 3:38 PM
 SSCrazy Group: General Forum Members Last Login: Monday, January 27, 2014 5:48 AM Points: 2,382, Visits: 3,364
 `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"
Post #1065346
 Posted Wednesday, February 16, 2011 5:13 PM
 SSCommitted Group: General Forum Members Last Login: Today @ 5:01 PM Points: 1,629, Visits: 5,112
 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`
Post #1065376
 Posted Thursday, February 17, 2011 6:55 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, June 04, 2012 3:46 PM Points: 5, Visits: 10
 Thanks, I'll try this...
Post #1065625
 Posted Thursday, February 17, 2011 6:57 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, June 04, 2012 3:46 PM 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....
Post #1065628
 Posted Thursday, February 17, 2011 7:16 AM
 SSCrazy Group: General Forum Members Last Login: Monday, January 27, 2014 5:48 AM Points: 2,382, Visits: 3,364
 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"
Post #1065645
 Posted Thursday, February 17, 2011 10:44 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 6:03 PM Points: 5,980, Visits: 6,905
 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
Post #1065857
 Posted Thursday, February 17, 2011 11:49 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, June 04, 2012 3:46 PM 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
Post #1065894
 Posted Thursday, February 17, 2011 12:51 PM
 SSCrazy Group: General Forum Members Last Login: Monday, January 27, 2014 5:48 AM Points: 2,382, Visits: 3,364
 `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"
Post #1065944
 Posted Thursday, February 17, 2011 1:24 PM
 Forum Newbie Group: General Forum Members Last Login: Monday, June 04, 2012 3:46 PM 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!
Post #1065967

 Permissions