Voting
Category
real language
Bookmarking
Support Us
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: |  (3.00 in 11 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
| Version | Author | Date | Comments | Rate |
|---|---|---|---|---|
| MS-Transact-SQL 8.00 | Karl | 07/06/05 | 0 | |
| Self contained Oracle 9i+ Version | Chris Farmer | 11/10/05 | 0 | |
| MS SQL 2000 | David Teviotdale | 09/07/05 | 4 | |
| DB2 / ANSI | Kent Olsen | 11/29/05 | 1 | |
| Oracle, DBA Version | Thomas Rein | 02/03/07 | 0 | |
| standard version | Rob van de Pol | 04/20/05 | 0 | |
| DB2, no redundancy, with recursion | Joachim Banzhaf | 05/17/06 | 0 | |
| Produces the exact lyrics, ORACLE | Ernst Madsen | 09/23/05 | 0 | |
| Oracle 9i+ hierarchical query | Christoph Linder | 11/21/07 | 0 |
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!
Comments
Thank you.