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

(CROSS JOIN - UNPIVOT - SQL SERVER 2008)

Date:09/18/11
Author:Nick Jacobsen
URL:n/a
Comments:0
Info:n/a
Score: (2.86 in 7 votes)
SELECT 
	CASE tens*10+unit 
		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  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.'
		ELSE 
			convert(varchar(2),tens*10+unit )+ ' bottles of beer on the wall, ' + 
			convert(varchar(2),tens*10+unit ) + ' bottles of beer. Take one down pass it around. ' +
			convert(varchar(2),tens*10+unit -1)+ ' bottle' + 
				CASE WHEN convert(varchar(2),tens*10+unit -1) = 1 THEN 
						'' ELSE 
						's' END + ' of beer on the wall.' 
		END
	FROM 
	(SELECT  unit FROM 
		(SELECT  1 c1, 2 c2, 3 c3, 4 c4,5 c5,6 c6,7 c7,8 c8,9 c9, 0 c0) p
		UNPIVOT (unit FOR Number1 IN (c1,c2,c3,c4,c5,c6,c7,c8,c9,c0))AS unpvt) as unit
CROSS JOIN
	(SELECT  tens FROM 
		(SELECT  1 c1, 2 c2, 3 c3, 4 c4,5 c5,6 c6,7 c7,8 c8,9 c9, 0 c0) p
			UNPIVOT (tens FOR Number1 IN (c1,c2,c3,c4,c5,c6,c7,c8,c9,c0))AS unpvt) as tens
		ORDER BY tens*10+unit 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 SQL 2000David Teviotdale09/07/054
MS-Transact-SQL 8.00Karl07/06/050
MS SQL 2005Johan ┼hlÚn09/28/091
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

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: