Loop Function - 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

General SQL Server Forums

New to SQL Server Programming

Loop Function

Author

Topic

funk.phenomena
Posting Yak Master

121 Posts

Posted - 2013-01-17 : 15:35:10

Hi all - I'm ussing SSMS 2008 and have the following table (there's about 100 rows):


USER_ID BALLOT_COUNT
jsmith 5
mdoe   3
JRoberts 2

I'd like to output the usernames on multiple lines based on the ballot count. Any idea how I can acheive this?


jsmith 
jsmith 
jsmith 
jsmith 
jsmith 

mdoe
mdoe
mdoe

JRoberts
JRoberts


SELECT USER_ID, BALLOT_COUNT FROM TABLE1

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-17 : 15:49:42

DECLARE @Table TABLE (UserID varchar(10),Ballot_Count int)
INSERT INTO @Table
VALUES
('jsmith', 5),
('mdoe', 3),
('JRoberts', 2)

SELECT UserID
FROM @Table t
CROSS JOIN -- use a tally table here - table that has a lot of numbers in it, this is for demo purposes only
(
VALUES
(1),(2),(3),(4),(5)

) a(num)

WHERE t.Ballot_Count >= a.num

Jim

Everyday I learn something that somebody else already knew