Brain teaser - 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)

Brain teaser

Author

Topic

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2008-07-09 : 07:01:27

In the following query:
select 'John' as theName, 2
union
select 'Paul' as theName, 3
union
select 'Elvis' as theName, 4

How do I extend the sql to interpret the number in the second column as an instruction as to how many times I want that row repeated? Without using a loop; just one set-based select statement.
So the result would be:

John 2
John 2
Paul 3
Paul 3
Paul 3
Elvis 4
Elvis 4
Elvis 4
Elvis 4

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-09 : 07:15:32

[code]SELECT theName, cnt
FROM
(
SELECT 'John' AS theName, 2 AS cnt
UNION
SELECT 'Paul' AS theName, 3
UNION
SELECT 'Elvis' AS theName, 4
) d
CROSS apply dbo.F_TABLE_NUMBER_RANGE (1, cnt) n
ORDER BYcnt
/*
theName cnt
------- -----------
John 2
John 2
Paul 3
Paul 3
Paul 3
Elvis 4
Elvis 4
Elvis 4
Elvis 4

(9 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2008-07-09 : 07:30:50

thanks! i knew it's been done before. just couldnt seem to find the right keywords to search on to get that thread

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-09 : 07:38:07

you can do like this also:-

SELECT	theName, cnt
FROM
(
	SELECT 'John' AS theName, 2 AS cnt
	UNION
	SELECT 'Paul' AS theName, 3
	UNION
	SELECT 'Elvis' AS theName, 4
) d
CROSS JOIN master..spt_values n
where n.number<d.cnt
and n.type='p'
ORDER BY cnt

theName cnt
------- -----------
John    2
John    2
Paul    3
Paul    3
Paul    3
Elvis   4
Elvis   4
Elvis   4
Elvis   4

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources