February 16, 2011 at 3:06 pm
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...
February 16, 2011 at 3:38 pm
SELECTq1.qry_id,
q2.qry_id,
h.hic_number
FROM(
SELECTDISTINCT
qry_id
FROMdbo.DEV_QRY_QC_HCC_131
) AS q1
INNER JOIN(
SELECTDISTINCT
qry_id
FROMdbo.DEV_QRY_QC_HCC_131
) AS q2 ON q2.qry_id > q1.qry_id
CROSS JOIN(
SELECTDISTINCT
hic_number
FROMdbo.DEV_QRY_QC_HCC_131
) AS h
N 56°04'39.16"
E 12°55'05.25"
February 16, 2011 at 5:13 pm
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);February 17, 2011 at 6:55 am
Thanks, I'll try this...
February 17, 2011 at 6:57 am
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 1234546
1 435676756A
2 764321343
3 794674hkf
4 213126786638712
giving these results:
qry_id hic_number1 1234546
1 435676756A
3 794674hkf
No, I'm looking for two-way qry_id combinations, by hic_number....
February 17, 2011 at 7:16 am
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"
February 17, 2011 at 10:44 am
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.
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 17, 2011 at 11:49 am
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 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_numberqry_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
February 17, 2011 at 12:51 pm
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'
SELECTs.hic,
s.qry,
f.qry
FROM@Sample AS s
CROSS APPLY(
SELECTx.qry
FROM@Sample AS x
WHEREx.hic = s.hic
AND x.qry > s.qry
) AS f
N 56°04'39.16"
E 12°55'05.25"
February 17, 2011 at 1:24 pm
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'
SELECTs.hic,
s.qry,
f.qry
FROM@Sample AS s
CROSS APPLY(
SELECTx.qry
FROM@Sample AS x
WHEREx.hic = s.hic
AND x.qry > s.qry
) AS f
Thanks! Great stuff!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply