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 2000)

Date:09/07/05
Author:David Teviotdale
URL:n/a
Comments:4
Info:n/a
Score: (2.93 in 28 votes)
select 
    CASE (a.aa * 10 + b.bb)
        WHEN 0 THEN 'No more bottle of beer on the wall, no more bottles of beer. ' +
                   'Go to the store and buy some more, 99 bottles of beer on the wall.'
        WHEN 1 THEN '1 bottle of beer on the wall, 1 bottle of beer. ' +
                    'Take one down and pass it around, no more bottles of beer on the wall.'
        WHEN 2 THEN '2 bottles of beer on the wall, 2 bottles of beer. ' +
                    'Take one down and pass it around, 1 bottle of beer on the wall.'
        ELSE 
            cast((a.aa * 10 + b.bb) as varchar(2)) + ' bottles of beer on the wall, ' +
            cast((a.aa * 10 + b.bb) as varchar(2)) + ' bottles of beer. ' +
            'Take one down and pass it around, ' +
            cast((a.aa * 10 + b.bb)-1 as varchar(2)) + ' bottles of beer on the wall.'
    END
from 
    (select 0 as aa union select 1 union select 2 union select 3 union select 4
    union select 5 union select 6 union select 7 union select 8 union select 9) a
cross join 
    (select 0 as bb union select 1 union select 2 union select 3 union select 4
    union select 5 union select 6 union select 7 union select 8 union select 9) b
order by a.aa desc, b.bb desc

Download Source | Write Comment

Alternative Versions

VersionAuthorDateCommentsRate
Oracle 9i+ hierarchical queryChristoph Linder11/21/070
Self contained Oracle 9i+ VersionChris Farmer11/10/050
DB2 / ANSIKent Olsen11/29/051
MS-Transact-SQL 8.00Karl07/06/050
MS SQL 2005Johan ┼hlÚn09/28/091
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

>>  Ernst Madsen said on 11/30/05 16:24:54

Ernst Madsen Nice idea, but it wont work as is. It misses a lot of "from dual" or something.
It can't possible have been tested, unless some SQL dialect assumes "from dual" when no table is mentioned.

>>  Jerry said on 01/11/06 19:23:07

Jerry Works like a charm... just tried it myself on a SQL2000 box. Microsoft (as usual) don't do everything according to the industry standards.....

>>  Luis Carlos F. Dias said on 01/14/06 23:10:11

Luis Carlos F. Dias Yes, it works...MS-SQL does not need the 'from dual' statement. It works fine even in PostgreSQL changing only the concatenation operator '+' by '||'.

>>  MaS said on 08/21/06 19:55:50

MaS Doesn't print *TWO* lines for each bottle.

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: