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.

asked 11 Dec '11, 04:58

zippidydo's gravatar image

zippidydo
377151521
accept rate: 0%

edited 11 Dec '11, 08:03

Graeme%20Perrow's gravatar image

Graeme Perrow
8.5k371109

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

(11 Dec '11, 05:22) AlexeyK77

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
permanent link

answered 11 Dec '11, 11:04

Breck%20Carter's gravatar image

Breck Carter
25.7k428592852
accept rate: 20%

edited 11 Dec '11, 11:06

"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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×1

question asked: 11 Dec '11, 04:58

question was seen: 546 times

last updated: 12 Dec '11, 01:10

Related questions