Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Two Variable Combination Permutation Calculation Expand / Collapse
Author
Message
Posted Wednesday, February 16, 2011 3:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 4, 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_number
qry_1&2 123456789A
qry_1&2 987654321A
qry_1&3 456789123B

I'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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 3:35 PM
Points: 2,393, Visits: 3,399
SELECT		q1.qry_id,
q2.qry_id,
h.hic_number
FROM (
SELECT DISTINCT
qry_id
FROM dbo.DEV_QRY_QC_HCC_131
) AS q1
INNER JOIN (
SELECT DISTINCT
qry_id
FROM dbo.DEV_QRY_QC_HCC_131
) AS q2 ON q2.qry_id > q1.qry_id
CROSS 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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 5:53 PM
Points: 1,785, Visits: 5,676
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_number
1 1234546
1 435676756A
2 764321343
3 794674hkf
4 213126786638712


giving these results:

qry_id hic_number
1 1234546
1 435676756A
3 794674hkf



MM


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1065376
    Posted Thursday, February 17, 2011 6:55 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Monday, June 4, 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

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Monday, June 4, 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_number
    1 1234546
    1 435676756A
    2 764321343
    3 794674hkf
    4 213126786638712


    giving these results:

    qry_id hic_number
    1 1234546
    1 435676756A
    3 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

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Wednesday, July 23, 2014 3:35 PM
    Points: 2,393, Visits: 3,399
    SELECT MIN(qry_id), MAX(qry_id), hic_number
    FROM Table1
    GROUP BY hic_number
    HAVING COUNT(*) = 2



    N 56°04'39.16"
    E 12°55'05.25"
    Post #1065645
    Posted Thursday, February 17, 2011 10:44 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Today @ 5:51 PM
    Points: 6,158, Visits: 7,222
    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_number
    qry_1&2 123456789A
    qry_1&2 987654321A
    qry_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 Farrell

    Never 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 Netiquette
    For index/tuning help, follow these directions. |Tally Tables

    Twitter: @AnyWayDBA
    Post #1065857
    Posted Thursday, February 17, 2011 11:49 AM
    Forum Newbie

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Monday, June 4, 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_number
    qry_1&2 123456789A
    qry_1&2 987654321A
    qry_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_id
    00005A 9130813
    00005A 9130850
    00010A 9130813
    00010A 9130850
    00010A 915216
    00010A 915416
    00021A 9130813
    00021A 9130850


    The 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+915416
    00021A 9130813+9130850




    Post #1065894
    Posted Thursday, February 17, 2011 12:51 PM


    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Wednesday, July 23, 2014 3:35 PM
    Points: 2,393, Visits: 3,399
    DECLARE	@Sample TABLE
    (
    hic CHAR(6) NOT NULL,
    qry VARCHAR(7) NOT NULL
    )

    INSERT @Sample
    SELECT '00005A', '9130813' UNION ALL
    SELECT '00005A', '9130850' UNION ALL
    SELECT '00010A', '9130813' UNION ALL
    SELECT '00010A', '9130850' UNION ALL
    SELECT '00010A', '915216' UNION ALL
    SELECT '00010A', '915416' UNION ALL
    SELECT '00021A', '9130813' UNION ALL
    SELECT '00021A', '9130850'

    SELECT s.hic,
    s.qry,
    f.qry
    FROM @Sample AS s
    CROSS 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

    Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

    Group: General Forum Members
    Last Login: Monday, June 4, 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 @Sample
    SELECT '00005A', '9130813' UNION ALL
    SELECT '00005A', '9130850' UNION ALL
    SELECT '00010A', '9130813' UNION ALL
    SELECT '00010A', '9130850' UNION ALL
    SELECT '00010A', '915216' UNION ALL
    SELECT '00010A', '915416' UNION ALL
    SELECT '00021A', '9130813' UNION ALL
    SELECT '00021A', '9130850'

    SELECT s.hic,
    s.qry,
    f.qry
    FROM @Sample AS s
    CROSS 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
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse