Un-SUM a quantity

[Former Member]

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.

[Former Member]

It will bu much easy to help, if you prepare and post to forum test DML and data.

[Breck_Carter · Accepted solution]

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

[VolkerBarth · Accepted solution]

"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;

[Former Member · Accepted solution]

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.