Loop help - SQL Server Forums

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)

Loop help

Author

Topic

chapo
Starting Member

39 Posts

Posted - 2008-04-11 : 16:07:40

I get the following results on a view.

Job | Qty | Desc
06-182 | 1 | B1011
06-324 | 2 | A1102
99-999 | 4 | AB839

What I would like is the following.

Job | Qty | Desc
06-182 | 1 | B1011
06-324 | 1 | A1102
06-324 | 1 | A1102
99-999 | 1 | AB839
99-999 | 1 | AB839
99-999 | 1 | AB839
99-999 | 1 | AB839

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-11 : 16:14:01

[code]DECLARE @Yak TABLE (Job VARCHAR(10), Qty INT, [Desc] VARCHAR(5))

INSERT @Yak
SELECT '06-182', 1, 'B1011'
UNION ALL SELECT '06-324', 2, 'A1102'
UNION ALL SELECT '99-999', 4, 'AB839'

SELECT
A.Job,
1 AS Qty,
A.[Desc]
FROM
@Yak AS A
INNER JOIN
(SELECT 1 AS Num UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) AS B
ON A.Qty >= B.Num[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 16:18:43

For a more generic solution, try this

DECLARE	@Sample TABLE (Job VARCHAR(6),  Qty INT, [Desc] VARCHAR(5))

INSERT	@Sample
SELECT	'06-182', 1, 'B1011' UNION ALL
SELECT	'06-324', 2, 'A1102' UNION ALL
SELECT	'99-999', 4, 'AB839'

SELECT		s.Job,
		1 AS Qty,
		s.[Desc]
FROM		@Sample AS s
CROSS APPLY	(
			SELECT	Number
			FROM	master..spt_values
			WHERE	[Type] = 'p'
				AND Number < s.Qty
		) AS x

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources