|
I have a row that has a quantity summed up by a primary key. Example: quantity primary key ----------- ----------- 16 999383023 Is there a way to "un-sum" this quantity so that I'll have 16 rows of quantity = 1 and primary key remaining the same? Purpose is to join this data with other tables that stratify this quantity further than the rolled up quantity of 16. Using SQL Anywhere 11. Appreciate the help. |
|
For fans of the truly obscure, we bring you the LATERAL join operator... CREATE TABLE t (
primary_key INTEGER NOT NULL PRIMARY KEY,
quantity INTEGER NOT NULL );
INSERT t VALUES ( 999383023, 16 );
INSERT t VALUES ( 123456789, 5 );
COMMIT;
SELECT t.primary_key,
1 AS quantity
FROM t,
LATERAL ( sa_rowgenerator ( 1, t.quantity ) ) AS unsum
ORDER BY t.primary_key;
primary_key,quantity
123456789,1
123456789,1
123456789,1
123456789,1
123456789,1
999383023,1
999383023,1
999383023,1
999383023,1
999383023,1
999383023,1
999383023,1
999383023,1
999383023,1
999383023,1
999383023,1
999383023,1
999383023,1
999383023,1
999383023,1
999383023,1
"CROSS APPLY" might make it a little bit less obscure (but it's not standard SQL): SELECT t.primary_key, 1 AS quantity FROM t CROSS APPLY sa_rowgenerator(1, t.quantity) AS unsum ORDER BY t.primary_key;
(11 Dec '11, 14:01)
Volker Barth
I am a fan of the obscure, truly obscure, and whatever gets the job done. Both answers get the job done. Beautiful. I prefer Volker's approach. Exactly what I was looking for. Thank you both.
(12 Dec '11, 01:10)
zippidydo
|
It will bu much easy to help, if you prepare and post to forum test DML and data.