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