SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Two Variable Combination Permutation Calculation


Two Variable Combination Permutation Calculation

Author
Message
jfanning
jfanning
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

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_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...
SwePeso
SwePeso
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15201 Visits: 3433

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"
mister.magoo
mister.magoo
SSCoach
SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

Group: General Forum Members
Points: 18004 Visits: 7928
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


select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);




  • 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

  • jfanning
    jfanning
    Forum Newbie
    Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

    Group: General Forum Members
    Points: 5 Visits: 10
    Thanks, I'll try this...
    jfanning
    jfanning
    Forum Newbie
    Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

    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_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....
    SwePeso
    SwePeso
    SSCoach
    SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

    Group: General Forum Members
    Points: 15201 Visits: 3433
    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"
    Evil Kraig F
    Evil Kraig F
    SSC-Dedicated
    SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)SSC-Dedicated (34K reputation)

    Group: General Forum Members
    Points: 34957 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_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
    jfanning
    jfanning
    Forum Newbie
    Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

    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_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
    SwePeso
    SwePeso
    SSCoach
    SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

    Group: General Forum Members
    Points: 15201 Visits: 3433

    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"
    jfanning
    jfanning
    Forum Newbie
    Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

    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 @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!
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search