Get 1 record per QTY field in table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Get 1 record per QTY field in table

Author

Topic

godestalbin
Starting Member

2 Posts

Posted - 2008-10-09 : 23:24:50

I need to print labels. In the table there is a field QTY to hold the quantity to be printed. The data are send to a report, so I need one record per quantity to print.
DATA QTY
A 2
B 3
I would like to get in the result of my query
A
A
B
B
B

How could I achieve this ?

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-10-10 : 00:04:12

Hi,
U need a Numbers\Tally table to get this


DECLARE @T TABLE (DATA VARCHAR(10), QTY INT)

INSERT INTO @T
SELECT 'A', 2 UNION ALL
SELECT 'B', 3

SELECT T.DATA 
FROM @T T
INNER JOIN Master..spt_values N ON N.Type = 'P' AND N.Number <= T.QTY
	AND N.Number <> 0

renu
Starting Member

47 Posts

Posted - 2008-10-10 : 00:38:18

CREATE TABLE TEST(C1 VARCHAR(3),C2 INT)
INSERT INTO TEST
VALUES('A',2)
INSERT INTO TEST
VALUES('B',3)

alter table test
add c3 int identity(1,1)

SELECT * FROM TEST

DECLARE @C1 INT
,@C2 INT
,@C3 VARCHAR(4)
,@C4 INT
,@C5 INT

set @c1 = 1

SELECT @C4 = COUNT(*) FROM TEST

WHILE @C1 <= @C4

BEGIN
SELECT @c2 = c2 from test where c3 = @C1

SELECT @c3 = c1 from test where c3 = @C1

SET @C5 = 1
WHILE @C5 <= @C2
BEGIN
declare @table table (c1 VARCHAR(4))
insert into @table
values(@c3)
SET @C5= @C5+1
END
SET @C1 = @C1+1
END

SELECT * FROM @TABLE

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-10 : 01:24:52

you can get this with recursive CTE also

declare @test table
(
DATA varchar(5),
QTY int 
)
insert into @test
SELECT 'A', 2 union all
SELECT 'B', 3 union all
SELECT 'C',5 union all
SELECT 'D',18


;With CTE(data,qty,level)
AS
(
SELECT DATA,QTY,1
FROM @test
UNION ALL
SELECT data,qty,level+1
FROM CTE
WHERE level+1<=qty
)

SELECT level,data FROM CTE order by data,level

output
-------------------------------------------
level	data
1	A
2	A
1	B
2	B
3	B
1	C
2	C
3	C
4	C
5	C
1	D
2	D
3	D
4	D
5	D
6	D
7	D
8	D
9	D
10	D
11	D
12	D
13	D
14	D
15	D
16	D
17	D
18	D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources