-1

Good Day

I Have a Table Called POs and it looks like this:

ID | ITEM | Description | Quantity | Amount |
----------------------------------------------
1  | A123 | Item 1      |    2     |  500   |
2  | A676 | Item 2      |    3     |  300   |

Is there a way to duplicate the line (as View) according to the Quantity amount and Number it ie.

ID  | ITEM | Description | Quantity | Amount | Number |
-------------------------------------------------------    
1   | A123 | Item1       |    2     |  500   |   1    |
1   | A123 | Item1       |    2     |  500   |   2    |
2   | A676 | Item2       |    3     |  300   |   1    |
2   | A676 | Item2       |    3     |  300   |   2    |
2   | A676 | Item2       |    3     |  300   |   3    |

Thanks

Jorge Campos
  • 22,647
  • 7
  • 56
  • 87
Ruan du Preez
  • 91
  • 1
  • 3
  • 12
  • Why you need this anyway. I'm asking because maybe you may need some other thing that could be done with some aggregate function. – Jorge Campos Dec 09 '13 at 11:00
  • And your example I don't think that could be implemented as there has to be an unique key – Dan Ovidiu Boncut Dec 09 '13 at 11:01
  • I use this for the sales guys, I have created a booking system where they can book stock out. On our Purchase Order it comes as a one liner but for this to work I need to brake it up as shown. I have done this from excel and the data does push to MySQL I was just hoping that I can get past the excel step. We don't have to wory about the Unique for now... – Ruan du Preez Dec 09 '13 at 11:04

1 Answers1

1

You can do it this way:

select
t.*,
numbers.n
from
t
inner join 
(
 select aa.a + 10 * bb.b + 100 * cc.c + 1000 * dd.d as n from
 (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) aa,
 (select 0 as b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) bb,
 (select 0 as c union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) cc,
 (select 0 as d union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) dd
) numbers
on t.Quantity >= numbers.n
where numbers.n != 0
order by t.id, numbers.n

It seems just a bit complicated because I create a table with numbers from 0 to 99 on the fly. It's a good idea to have a table for as much numbers as you need and join to this one.

fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • Oh yeah. If OP will have `quantity=1000` he will be surprised. It's a common problem about sequence generation and it has (in general) two different solution in MySQL, but not as you've described. – Alma Do Dec 09 '13 at 11:16
  • Why will he be surprised? What are you talking about? Works perfectly with 11, too: http://sqlfiddle.com/#!2/aa56a/1 – fancyPants Dec 09 '13 at 11:18
  • Typo, that was 1000 (you're multiplying `10*x+y`, where `max(x)=9`, `max(y)=9` - so max will be 90+9, but I've just typed 1000) – Alma Do Dec 09 '13 at 11:19
  • Still don't understand...Yes, when he needs more than 99 he will have to create more numbers on the fly or like I said generate a table with numbers anyway which makes life easier. So? What's the point. And my sequence generation works fine. Or can you just describe in detail what's wrong with it? – fancyPants Dec 09 '13 at 11:21
  • And of course there are other ways like shift operations and so on which are shorter but harder to understand for beginners. I picked this one.... – fancyPants Dec 09 '13 at 11:21
  • The wrong is to assume that quantity will be restricted with some maximum. You can't "re-write" SQL, it's application's structure thing. I think it's a task to application, not to MySQL (but - yes, that's because MySQL has no sequences supported) – Alma Do Dec 09 '13 at 11:22
  • Yes, that's a good point. But my solution, which is btw more like a proof of concept, still is applicable when the OP might know that he will never have a higher quantity than a certain amount. – fancyPants Dec 09 '13 at 11:25
  • If it's so important to generate any meaningful number in MySQL, there's [common solution](http://stackoverflow.com/questions/9751318/creating-a-numbers-table-in-mysql) to that. – Alma Do Dec 09 '13 at 11:28
  • Looks Great and does what i need it to do, only problem is when I want to save the query as a view I get this error: **View's SELECT contains a subquery in the FROM clause** – Ruan du Preez Dec 09 '13 at 11:32
  • Then create a table like in the link that Alma Do posted in the comment above yours and join to it instead of having the subquery. – fancyPants Dec 09 '13 at 11:33
  • Hi FancyPants, Sorry I twook so long to get back about this. I had a look at your code again and I got it to work great in SQL Server. I have one question tough. I had a look at your code and cannot see what I need to change for it to go past 99. I want to change it to MAX 2500 – Ruan du Preez Jan 24 '14 at 20:55
  • @RuanduPreez I modified the query so the MAX is 9999 – fancyPants Jan 26 '14 at 12:16