Voting

Category

real language

Bookmarking

Del.icio.us Digg Diigo DZone Earthlink Google Kick.ie
Windows Live LookLater Ma.gnolia Reddit Rojo StumbleUpon Technorati

Language SQL

(MS SQL 2005)

Date:09/28/09
Author:Johan ┼hlÚn
URL:http://blogical.se/blogs/jahlen
Comments:1
Info:n/a
Score: (2.87 in 15 votes)
---------------------------------------------------------------
-- 99 BOTTLES OF BEER
-- SQL Server 2005 version. Using a CTE recursive query.
-- 
-- (C) Johan ┼hlÚn, 2009
-- Blog: http://blogical.se/blogs/jahlen
-- This is BEERWARE. If you like it - send the author a beer! ;-)
---------------------------------------------------------------


WITH Song AS
(
	-- Start from the last verse...
	SELECT
		0 AS BeerCount,
		CAST('No more bottles' AS VARCHAR) AS WhatWeHave,
		CAST('Go to the store and buy some more, ' AS VARCHAR(50)) AS WhatToDo,
		CAST('99 bottles' AS VARCHAR) AS WhatWeHaveNext
		
	UNION ALL
	
	-- Continue with earlier verses
	SELECT
		BeerCount + 1,
		CAST(LTRIM(STR(BeerCount + 1)) + (CASE WHEN BeerCount > 0 
			THEN ' bottles' ELSE ' bottle' END) AS VARCHAR),
		CAST('Take one down and pass it around, ' AS VARCHAR(50)),
		LOWER(WhatWeHave)
	FROM Song
	WHERE BeerCount < 99 -- Recurse until we have 99 bottles...
)

SELECT (
	WhatWeHave + ' of beer on the wall, '
	+ 
	LOWER(WhatWeHave) + ' of beer. ' -- repeat in lower caps
	+ 
	WhatToDo
	+
	WhatWeHaveNext
	+ ' of beer on the wall.'
) AS Lyrics
FROM Song
ORDER BY BeerCount DESC -- Reverse the verses

Download Source | Write Comment

Alternative Versions

VersionAuthorDateCommentsRate
Oracle 9i+ hierarchical queryChristoph Linder11/21/070
DB2 / ANSIKent Olsen11/29/051
MS SQL 2000David Teviotdale09/07/054
MS-Transact-SQL 8.00Karl07/06/050
Self contained Oracle 9i+ VersionChris Farmer11/10/050
CROSS JOIN - UNPIVOT - SQL SERVER 2008Nick Jacobsen09/18/110
standard versionRob van de Pol04/20/050
Oracle, DBA VersionThomas Rein02/03/070
DB2, no redundancy, with recursionJoachim Banzhaf05/17/060
Produces the exact lyrics, ORACLEErnst Madsen09/23/050

Comments

>>  James said on 01/25/10 11:35:21

James This is the best explanation of CTEs.
Thank you.

Download Source | Write Comment

Add Comment

Please provide a value for the fields Name, Comment and Security Code.
This is a gravatar-friendly website.
E-mail addresses will never be shown.
Enter your e-mail address to use your gravatar.

Please don't post large portions of code here! Use the form to submit new examples or updates instead!

Name:

eMail:

URL:

Security Code:
  
Comment: