Ungroup records based on column qty

Transact-SQL
1

I have a table that has purchases in it, and the quantity they were bought at, with the price.
I need to break them out into 1 row per item, with the cost of that row.

declare @tableA table

(
Qty Integer,
Name nvarchar(50),
Price decimal(18,2)
)

insert into @tableA (Qty, Name, Price)
values
(5, 'Apples', 1.20),
(2, 'Apples', 1.15),
(3, 'Apples', 1.16),
(6, 'Banana', 2.99),
(2, 'Banana', 2.75),
(1, 'Banana', 3.00)

select * from @tableA

Current result
Qty Name Price
5 Apples 1.20
2 Apples 1.15
3 Apples 1.16
6 Banana 2.99
2 Banana 2.75
1 Banana 3.00

This is what I need returned from the table.
Qty Name Price
1 Apples 1.20
1 Apples 1.20
1 Apples 1.20
1 Apples 1.20
1 Apples 1.20
1 Apples 1.15
1 Apples 1.15
1 Apples 1.16
1 Apples 1.16
1 Apples 1.16

2

Hi

This is possible one way using
Tally table

Please check this below SQL
Just wrote it using my mind
I don't have access to my computer

;With tallyCte as
(
Select n=1
Union all
Select n+1 from tallyCte
Where n<=100
)
Select 1, b.Name,b.Price
From tallyCte a
Join
@tableA b
On
B.qty <= a.n;

3

Is the SQL okay

Does it solve ? Requirement

Please let me know

4

I won't be back at work until tomorrow to try it... thanks in adv,

5

ok now i have access to my computer
very Small fix was needed

Solved it like below
Please let me know your thoughts :slight_smile:

drop create data 
drop table #tableA
go 

create table #tableA
(
Qty Integer,
Name nvarchar(50),
Price decimal(18,2)
)
go

insert into #tableA (Qty, Name, Price)
values
(5, 'Apples', 1.20),
(2, 'Apples', 1.15),
(3, 'Apples', 1.16),
(6, 'Banana', 2.99),
(2, 'Banana', 2.75),
(1, 'Banana', 3.00)
go 

select * from #tableA
go
SQL 
;WITH tallycte 
     AS (SELECT n=1 
         UNION ALL 
         SELECT n + 1 
         FROM   tallycte 
         WHERE  n <= 100) 
SELECT 1, 
       b.NAME, 
       b.price 
FROM   tallycte a 
       JOIN #tablea b 
         ON a.n <= b.qty 
ORDER  BY b.price
Result

image
image.png694×1102 11.3 KB

6

No, this doesn't work. As it returns 100 rows for each record. I only want the number of rows to be the Qty.
For example, Qty 5, Apples at $1.20 should return back 5 new rows, then move onto the next record.
Qty 2 Apple at $1.15 ... this would repeat 2 times.

Run this example ... you will see it is incorrect

declare @tableA table
(
Qty Integer,
PurchaseDate Date,
Name nvarchar(50),
Price decimal(18,2)
)

insert into @tableA (Qty, PurchaseDate, Name, Price)
values
(5, '2018-10-18', 'Apples', 1.20),
(2, '2018-10-16', 'Apples', 1.15),
(3, '2018-10-02', 'Apples', 1.16),
(6, '2018-10-17', 'Banana', 2.99),
(2, '2018-10-15', 'Banana', 2.75),
(1, '2018-10-02', 'Banana', 3.00)

--select * from @tableA

;With tallyCte as
(
Select n=1
Union all
Select n+1 from tallyCte
Where n<=100
)
Select a.n, b.qty, b.PurchaseDate, b.Name,b.Price
From tallyCte a
join @tableA b On b.qty <= a.n

order by PurchaseDate desc

7

Hi

Please check my code

I made a small correction to the code

It should be
a.n <= b.qty

Here
From tallyCte a
join @tableA b On b.qty <= a.n

1 Like
8

Thanks - I missed that correction. All is working great.