| Author |
 Topic  |
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3451 Posts |
 Posted - 03/11/2009 : 11:57:54
|
Announcing the cool but useless SQL competition:
My entry:
IF OBJECT_ID('tempdb..#results') IS NOT NULL DROP TABLE #results
CREATE TABLE #results ([x] INT, [y] INT , [value] INT)
DECLARE @x TABLE ([value] INT)
DECLARE @y TABLE ([value] INT)
DECLARE @heap TABLE ([a] BIT)
DECLARE @sql NVARCHAR(MAX)
INSERT @heap
SELECT 0
UNION ALL SELECT 0
UNION ALL SELECT 0
INSERT @x (
[value]
)
SELECT
ROW_NUMBER() OVER(ORDER BY a.[a]) - 1
FROM
@heap a
CROSS JOIN @heap b
CROSS JOIN @heap c
CROSS JOIN @heap d
-- CROSS JOIN @heap e
-- CROSS JOIN @heap f
INSERT @y
SELECT [value]
FROM @x
INSERT #results ([x], [y], [value])
SELECT
x.[value]
, y.[value]
, x.[value] & y.[value]
FROM
@x x
CROSS JOIN @y y
SET @sql = N'
SELECT
pvt.[x]
'
SELECT
@sql = @sql + ' , CASE [' + CAST(g.[y] AS VARCHAR(50)) + '] WHEN 0 THEN ''*'' ELSE '''' END AS [' + CAST(g.[y] AS VARCHAR(50)) + ']
'
FROM
( SELECT DISTINCT [y] FROM #results ) g
ORDER BY
[y] ASC
SET @sql = @sql + N'
FROM
(SELECT [x], [y], [value] FROM #results) g
PIVOT (
SUM ([value]) FOR [y] IN ('
SELECT
@sql = @sql + N'
[' + CAST(g.[y] AS VARCHAR(50)) + '],'
FROM
( SELECT DISTINCT [y] FROM #results) g
ORDER BY
[y] ASC
SET @Sql = LEFT(@Sql, LEN(@Sql) - 1) + N')
) pvt'
-- PRINT @sql
EXEC (@sql)
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
rohitkumar
Constraint Violating Yak Guru
USA
472 Posts |
Posted - 03/11/2009 : 17:25:27
|
| nice piece of art |
 |
|
|
Arnold Fribble
Yak-finder General
United Kingdom
1961 Posts |
Posted - 03/12/2009 : 04:25:06
|
Triangles, eh? I can do triangles too! But these ones aren't so predictable.
-- best viewed in a fixed-width font with sufficient output width:
WITH Rule110 AS (
SELECT 0 AS gen, CAST(REPLICATE(' ', 1000) + '@' + REPLICATE(' ', 2) AS varchar(max)) AS s
UNION ALL
SELECT gen + 1, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(s,
' @', ' *@'), '@ @', '@*@'), '@ @', '@*@'), '@@@', '@-@'), '-@@', '--@'),
'-@@', '--@'), '-@-', '---'), '-', ' '), '*', '@')
FROM Rule110
)
SELECT TOP 1000 gen, REVERSE(s) AS s
FROM Rule110
OPTION (MAXRECURSION 0)
|
Edited by - Arnold Fribble on 03/12/2009 04:29:21 |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2884 Posts |
Posted - 03/12/2009 : 12:27:40
|
neat! what's the hausdorff dimension? :)
elsasoft.org |
|
|
|
Arnold Fribble
Yak-finder General
United Kingdom
1961 Posts |
Posted - 03/12/2009 : 12:47:39
|
quote:
Originally posted by jezemine
neat! what's the hausdorff dimension? :)
Well, if rule 102 is ln(3)/ln(2) then clearly rule 110 must be a bit bigger!
|
Edited by - Arnold Fribble on 03/12/2009 14:09:05 |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2869 Posts |
Posted - 03/12/2009 : 14:41:07
|
I think I orginally copied this from this site, in a similar type thread.
Jim
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[AnalogTime] AS
SET NOCOUNT ON
CREATE TABLE #ClockTimeDeg(Radius INT, ClockTimeHRS DECIMAL(18, 3), ClockTimeMin INT, Deg DECIMAL(18, 8), XCoOrd DECIMAL(18, 8), YCoOrd DECIMAL(18, 8), DISPLAY VARCHAR(2));
DECLARE @Mins INT
DECLARE @Hrs DECIMAL(18, 3)
DECLARE @Deg DECIMAL(18,8)
DECLARE @XCoOrd INT
DECLARE @YCoOrd INT
DECLARE @CurXCoOrd INT
DECLARE @CurYCoOrd INT
DECLARE @PrevYCoOrd INT
DECLARE @Radius DECIMAL(18, 8)
DECLARE @InsString VARCHAR(1000)
DECLARE @Display VARCHAR(2)
DECLARE @CurrKey INT
DECLARE @PrevKey INT
DECLARE @CurrTime DATETIME
DECLARE @HrsVal DECIMAL(18, 8)
DECLARE @MinsVal INT
SET @CurrTime = GETDATE()
SET @MinsVal = DATEPART(MINUTE, @CurrTime)
SET @HrsVal = CAST(DATEPART(HOUR, @CurrTime) AS DECIMAL(18, 8)) + (CAST((@MinsVal / 12) AS DECIMAL(18, 2)) * 0.2)
IF @HrsVal >= 13
BEGIN
SET @HrsVal = @HrsVal - 12
END
SET @Radius = 1
WHILE @Radius <= 24
BEGIN
--******Store the x, y co-ords of the clocks hrs and minutes using trig functions
--******Clock Radius = 24
--****** The length of the Y, coords is halved because it seems like the results panel provides more height to each character than width.
SET @Deg = 90
SET @Hrs = 12
SET @Mins = 0
WHILE @Mins < 60
BEGIN
INSERT INTO #ClockTimeDeg
SELECT @Radius, @Hrs, @Mins, @Deg,
CAST(24 + (@Radius * (CASE WHEN TAN(RADIANS(@Deg)) = 0 THEN -24 ELSE ((SIN(RADIANS(@Deg))) / TAN(RADIANS(@Deg))) END)) + 0.499999999 AS INT),
(48 - CAST(24 + @Radius * (SIN(RADIANS(@Deg))) + 0.499999999 AS INT) / 2),
CASE WHEN (@HrsVal = @Hrs AND @Radius < 11) OR (@MinsVal = @Mins AND @Radius < 21) THEN '*'
ELSE CASE WHEN @Radius = 23 AND @Hrs = CAST(@Hrs as INT) THEN LTRIM(STR(@HRS, 2)) ELSE ' ' END END
SET @Hrs = @Hrs + 0.2
IF @Hrs = 13
BEGIN
SET @HRS = 1
END
SET @Mins = @Mins + 1
IF @Deg > 353
BEGIN
SET @Deg = 0
END
ELSE
BEGIN
SET @Deg = @Deg + 6
END
END
SET @Radius = @Radius + 1
END
DECLARE DispCur
CURSOR FOR
SELECT CASE WHEN XCoOrd <= 0 THEN 48 ELSE XCoOrd END, YCoOrd, Display
FROM #ClockTimeDeg
WHERE Display<> ' '
ORDER BY 2, 1 DESC
CREATE TABLE #Results(RowNo INT, AnalogClock VARCHAR(1000))
OPEN DispCur
FETCH NEXT FROM DispCur INTO @CurXCoOrd, @CurYCoOrd, @Display
SET @PrevYCoOrd = 0
SET @PrevKey = 0
SET @XCoOrd = 1
SET @InsString = ''
WHILE @@FETCH_STATUS = 0
BEGIN
IF @CurYCoOrd <> @PrevYCoOrd
BEGIN
INSERT INTO #Results
SELECT @CurYCoOrd, @InsString
SET @InsString = ''
SET @PrevYCoOrd = @CurYCoOrd
SET @XCoOrd = 50
END
WHILE @XCoOrd > @CurXCoOrd
BEGIN
SET @InsString = @InsString + ' '
SET @XCoOrd = @XCoOrd - 1
END
SET @InsString = @InsString + @Display
SET @XCoOrd = @XCoOrd - 1
FETCH NEXT FROM DispCur INTO @CurXCoOrd, @CurYCoOrd, @Display
END
CLOSE DispCur
DEALLOCATE DispCur
INSERT INTO #Results
SELECT @CurYCoOrd, @InsString
SELECT AnalogClock
FROM #Results
ORDER BY RowNo
DROP TABLE #ClockTimeDeg
DROP TABLE #Results
|
|
|
|
sakets_2000
Flowing Fount of Yak Knowledge
India
1472 Posts |
Posted - 03/12/2009 : 18:55:38
|
Hopeless :)
Run after turning 'result in text'
declare @var50 varchar(1000) ; set @var50 = ' r7 XW@WWW@WWB0ZZaZZaa2XXr;r;i;;;r;;7Sa rBii MMB ' ;
declare @var51 varchar(1000) ; set @var51 = ' 7MMZMar WWB08808888ZZa2SSX;;;;ii;;iiii;7S8S Sr7ZMMMMB ' ;
declare @var52 varchar(1000) ; set @var52 = ' WX8B2S .MB888Z888ZZaXX7;;::ii;iii:::ir227 .7 @MMMMM ' ;
declare @var53 varchar(1000) ; set @var53 = ' ,:7S,W0. :: M0888ZZZZaa2SXXr77;r;iii,:ii7Sa XS;. r;S ' ;
declare @var54 varchar(1000) ; set @var54 = ' ,88X .; M08888ZZZZaaSXr7rrriii::,:,. , ,;, 7 X ' ;
declare @var55 varchar(1000) ; set @var55 = ' .rri7iX;., MM888888ZZaSX;riiiii::::,:,.. : . iZMB8X ' ;
declare @var56 varchar(1000) ; set @var56 = ' , MM@088Z888ZZa2S7r;;;ri;i:::::irXSa;;,: XXarX ' ;
declare @var57 varchar(1000) ; set @var57 = ' ZMMMMMM87Z.8MMMMMMMM088ZaZZ80Z2a2S7Xrr;iii;ii,,.., . r,:;r,r ' ;
declare @var58 varchar(1000) ; set @var58 = ' @MMWMWBWBBMMMMMM. :MMM8aZaaaaaa882XXX7;:::ii,,,.... i7SirX ZX0@,77 ' ;
declare @var59 varchar(1000) ; set @var59 = ' .SM@0M0SSS0W8W@: ,8 7aaa2a222Z882X7r;i,:::i::::,,i7ZBX i 8WXWSXX: ' ;
declare @var60 varchar(1000) ; set @var60 = ' .MMMW8BaW8XSM2:aMM:, :rMMM@088ZZZ8888Z2Xriiiii;7;:,::iir;:. MMMMMMaZaSXX , X ' ;
declare @var61 varchar(1000) ; set @var61 = ' 7Z2SiX;airi:a::7W@ ,X 00Sr;;;;;r;;iii:..,::i::i;i:,:iii:::i2BWS7 .;irSr, .i;; ' ;
declare @var39 varchar(1000) ; set @var39 = ' .MBa222a2a0WMMWa, X8aXSSXXX7XSS2aSSWW2; a@0, . ' ;
declare @var40 varchar(1000) ; set @var40 = ' ,M0ZZZZZ2SXXr. iS2rZ:irrrrr7SS2a2aBZZ@MMM2S7 ' ;
declare @var41 varchar(1000) ; set @var41 = ' ;Ma7r78WBB000ZaZBMMMMMM@i i::ir;r72a2a2aa8MMMa i ,X:. ' ;
declare @var42 varchar(1000) ; set @var42 = ' ;MZ7i;S@WB0BWM@Zri:22 .i;r7XXXSSS2aaZ2aBr @0: ' ;
declare @var43 varchar(1000) ; set @var43 = ' W8X7rSW0Sr;ii,:ZBZ:.:::i;77XXSS2aZZaSSZBaZB8 77 ' ;
declare @var44 varchar(1000) ; set @var44 = ' ,XMBZX720WWBWWMM8;::iii;i;ir7SSaZ882SSSa022XMM27 ;;;. ' ;
declare @var45 varchar(1000) ; set @var45 = ' 8rBZXS2ZZZZXi:. .,,::,:;r7XSZ80Z2SXXX2a0MMM2. ;Bi. ' ;
declare @var46 varchar(1000) ; set @var46 = ' . :S88MWZXi ..,i;r7SZ88ZaXXSXXXSXXM ;7 ii. ' ;
declare @var47 varchar(1000) ; set @var47 = ' : :ZrS07,. .,i7SaZ888aSXXXX777XXrr 7, ,2X ' ;
declare @var48 varchar(1000) ; set @var48 = ' ;rrMXW@8S;:,, .,,irSZ80888ZaXX7;r7XX7X7XS0M ' ;
declare @var49 varchar(1000) ; set @var49 = ' ;a ZraaBW8ZZZaZ2a8BB0Z8Zaa2X77rrX7rr7rrrXa X . ' ;
declare @var26 varchar(1000) ; set @var26 = ' .. . : ,MMBBMMMM@Bar. MM.:7BM87, ..., .,i7777X7rSBB0BWa287a. ' ;
declare @var27 varchar(1000) ; set @var27 = ' ... . iMMWZaa8B@MMM0: ia :iX7i;XZB@@W@@W0Z2SX777r7X7XB00@M ;:ir ' ;
declare @var28 varchar(1000) ; set @var28 = ' ........ . ;Z@Z2XS2aaaZBZX:. :;7SSS0MMMMB@MMMMWZX:;rrrX7XBB0MX ;r ; ' ;
declare @var29 varchar(1000) ; set @var29 = ' .. :MBWMM@@0MM@WWS. .iSS2BMW. M@@W:MMZ;:,:ir7X7X0B@M0W:,r : ' ;
declare @var30 varchar(1000) ; set @var30 = ' . ... MWW0,Z@8M;SMWW, .;7..M8a, :X7rXS; ..:;XSXSZ007 ,r r, ' ;
declare @var31 varchar(1000) ; set @var31 = ' ... @BBBr.aS7 ;2Z@. ,;i XBMMBSX;i ..,::rSSS2Z8S ; 7, ' ;
declare @var32 varchar(1000) ; set @var32 = ' r@8WB8ZXXXi.28 .,, .i;;;:. .:i;r22S28ZZX.7, i: ' ;
declare @var33 varchar(1000) ; set @var33 = ' .M2288aX;. ,0S ., .,:;72aS2BZ2 ;. Wi ' ;
declare @var34 varchar(1000) ; set @var34 = ' MX,.. .. r@7 ,ii:. ...:irXZaSa8ai Sii;X ' ;
declare @var35 varchar(1000) ; set @var35 = ' MZ;::.., .2W, ;i .:iirSaSS2ai ,. , Z . ' ;
declare @var36 varchar(1000) ; set @var36 = ' MZ7;i,...,ZZ .. .,:ii. ,:;X2a222SS,iZ ;@i ' ;
declare @var37 varchar(1000) ; set @var37 = ' 2MX;i,....20 :X0aXS,,i7i.....,,:;XSS2S22SMMM8 ,i . ' ;
declare @var38 varchar(1000) ; set @var38 = ' MBS;ri:i;SWW272BB8Sr: .;SX;;;;rr7XSSSSaaSXW8 ..Z ' ;
declare @var1 varchar(1000) ; set @var1 = ' ,i:iiiiiiiiii;;iii:iiiii:ii:::ii::::,i::::.,:,::,iiiiiiiii:iii:i:i::ii::ii::i::: ' ;
declare @var2 varchar(1000) ; set @var2 = ' :i::::i:ii:iiiiiiii:i:ii;iiiii::,.. ,,.::::ii:::::::i::i:::ii:::, ' ;
declare @var3 varchar(1000) ; set @var3 = ' ,i:::iiiiiii:iiiiiiiii:i:::::. SiX8MMMMMMMMMM0X, ..::,:::i:::,:,,,,,:i,,,,, ' ;
declare @var4 varchar(1000) ; set @var4 = ' ,::::::::i::iii::i:::::::,. ;BMMMMMM@WWWWWWWW@MMMMW; ...:,,:::,:,::,:,,::,,, ' ;
declare @var5 varchar(1000) ; set @var5 = ' ,::::::::,::::::::i::i,. ,BMMMM@B0000000000000000BWMMM0. .::,.:.,,:,::,::::,,, ' ;
declare @var6 varchar(1000) ; set @var6 = ' ,,:i:::::::i::ii:::,, ;MMM@B00000000000000000000000BWMMM; ,,:,::,,,:,.,,,,,. ' ;
declare @var7 varchar(1000) ; set @var7 = ' ::,,i:::,:::i::::,:. ZMMMB00000880000000000000000000000BMMM ..,..,,,,,,,..,,. ' ;
declare @var8 varchar(1000) ; set @var8 = ' .::,::,:,,,,,,:,,, 2MM@B000000800000000000000000000000000BMM7 ...,.,.. :,,,... ' ;
declare @var9 varchar(1000) ; set @var9 = ' ,:::,.,,,::::,,.. @M@B00000BBZ00000000000000000000800000000@MB ...,.,.. . ' ;
declare @var10 varchar(1000) ; set @var10 = ' .,,,:::::::,,:, 7MMB00000000Za000000000000000000000000000000WM@ ..,...... ., ' ;
declare @var16 varchar(1000) ; set @var16 = ' .,,,,,,,,:, iM@0000BWWWW@WBXi7X22aZZXXS2800B0000000000000000000000MM . ' ;
declare @var17 varchar(1000) ; set @var17 = ' .,,,,:,:,, MM000BBB0B0aa2X,r7;i;r:. :7ZB@@WB000000000000000000WM . ' ;
declare @var18 varchar(1000) ; set @var18 = ' .....:.,:: iMW000B0ZSi.:;;;,,:,. :XaW@@@WB000000000000000Ma ' ;
declare @var19 varchar(1000) ; set @var19 = ' ..,..,,,,. 0M000B0a7::.,,,i:,.. ... iXZ0BWWBB000000000000MM ' ;
declare @var20 varchar(1000) ; set @var20 = ' .....,,,,, MM0000Z2X;ii::,:,:.... . ..,:,. .r2B@BB00000000000MM ' ;
declare @var21 varchar(1000) ; set @var21 = ' ., ,,.. , M@00B8ZZS7iii:::,:::,..... . .,,.:,...,rZ@BB0000000000WM ' ;
declare @var22 varchar(1000) ; set @var22 = ' .,.. .,.:, MM0088ZZ2Xrr::::::::,.,. . ,:i:i2BWB000000000WM . ' ;
declare @var23 varchar(1000) ; set @var23 = ' ......., SMB08ZZ2X7r7:i,,.::,:... :2aS: ;;;iX0@W00000000BMi ' ;
declare @var24 varchar(1000) ; set @var24 = ' .,.. ..... MWBZZaSXri:,:.. ... :ZMMMMMMMMMMa;;7;;rZWWB0000W@MMa . ' ;
declare @var25 varchar(1000) ; set @var25 = ' ... . .. ,. @MB8aSi, : .2MMM@X. iaZS777rrX0B00BWWZXS8 ' ;
declare @var11 varchar(1000) ; set @var11 = ' ,:::,,,,,,:,,,. aM@00000000B0780000000000000000000000000000000WMM. ....... . .. ' ;
declare @var12 varchar(1000) ; set @var12 = ' ,,,,:,::,,,,,: :M@000000000B77000080000000000000000000000000000BMM . ......... ' ;
declare @var13 varchar(1000) ; set @var13 = ' ...,,,,:.:.,,. MMB0000000008;aB008000000000000000000000000000000WMa . . ' ;
declare @var14 varchar(1000) ; set @var14 = ' .,:.,,,:.::: aMB00000000B02;ZBBBBBBBB000BB0000000000000000000000WM .. . ' ;
declare @var15 varchar(1000) ; set @var15 = ' ,:,.,.:,,:. XMW000000BBBB0X:2BWBBBBWMMMWWWBBB0B00000000000000000BMS . . .. ' ;
print @var1 ; WAITFOR DELAY '00:00:00:18';
print @var2 ; WAITFOR DELAY '00:00:00:18';
print @var3 ; WAITFOR DELAY '00:00:00:18';
print @var4 ; WAITFOR DELAY '00:00:00:18';
print @var5 ; WAITFOR DELAY '00:00:00:18';
print @var6 ; WAITFOR DELAY '00:00:00:18';
print @var7 ; WAITFOR DELAY '00:00:00:18';
print @var8 ; WAITFOR DELAY '00:00:00:18';
print @var9 ; WAITFOR DELAY '00:00:00:18';
print @var10 ; WAITFOR DELAY '00:00:00:18';
print @var11 ; WAITFOR DELAY '00:00:00:18';
print @var12 ; WAITFOR DELAY '00:00:00:18';
print @var13 ; WAITFOR DELAY '00:00:00:18';
print @var14 ; WAITFOR DELAY '00:00:00:18';
print @var15 ; WAITFOR DELAY '00:00:00:18';
print @var16 ; WAITFOR DELAY '00:00:00:18';
print @var17 ; WAITFOR DELAY '00:00:00:18';
print @var18 ; WAITFOR DELAY '00:00:00:18';
print @var19 ; WAITFOR DELAY '00:00:00:18';
print @var20 ; WAITFOR DELAY '00:00:00:18';
print @var21 ; WAITFOR DELAY '00:00:00:18';
print @var22 ; WAITFOR DELAY '00:00:00:18';
print @var23 ; WAITFOR DELAY '00:00:00:18';
print @var24 ; WAITFOR DELAY '00:00:00:18';
print @var25 ; WAITFOR DELAY '00:00:00:18';
print @var26 ; WAITFOR DELAY '00:00:00:18';
print @var27 ; WAITFOR DELAY '00:00:00:18';
print @var28 ; WAITFOR DELAY '00:00:00:18';
print @var29 ; WAITFOR DELAY '00:00:00:18';
print @var30 ; WAITFOR DELAY '00:00:00:18';
print @var31 ; WAITFOR DELAY '00:00:00:18';
print @var32 ; WAITFOR DELAY '00:00:00:18';
print @var33 ; WAITFOR DELAY '00:00:00:18';
print @var34 ; WAITFOR DELAY '00:00:00:18';
print @var35 ; WAITFOR DELAY '00:00:00:18';
print @var36 ; WAITFOR DELAY '00:00:00:18';
print @var37 ; WAITFOR DELAY '00:00:00:18';
print @var38 ; WAITFOR DELAY '00:00:00:18';
print @var39 ; WAITFOR DELAY '00:00:00:18';
print @var40 ; WAITFOR DELAY '00:00:00:18';
print @var41 ; WAITFOR DELAY '00:00:00:18';
print @var42 ; WAITFOR DELAY '00:00:00:18';
print @var43 ; WAITFOR DELAY '00:00:00:18';
print @var44 ; WAITFOR DELAY '00:00:00:18';
print @var45 ; WAITFOR DELAY '00:00:00:18';
print @var46 ; WAITFOR DELAY '00:00:00:18';
print @var47 ; WAITFOR DELAY '00:00:00:18';
print @var48 ; WAITFOR DELAY '00:00:00:18';
print @var49 ; WAITFOR DELAY '00:00:00:18';
print @var50 ; WAITFOR DELAY '00:00:00:18';
print @var51 ; WAITFOR DELAY '00:00:00:18';
print @var52 ; WAITFOR DELAY '00:00:00:18';
print @var53 ; WAITFOR DELAY '00:00:00:18';
print @var54 ; WAITFOR DELAY '00:00:00:18';
print @var55 ; WAITFOR DELAY '00:00:00:18';
print @var56 ; WAITFOR DELAY '00:00:00:18';
print @var57 ; WAITFOR DELAY '00:00:00:18';
print @var58 ; WAITFOR DELAY '00:00:00:18';
print @var59 ; WAITFOR DELAY '00:00:00:18';
print @var60 ; WAITFOR DELAY '00:00:00:18';
print @var61 ; WAITFOR DELAY '00:00:00:18';
|
Edited by - sakets_2000 on 03/12/2009 19:02:59 |
|
|
|
jezemine
Flowing Fount of Yak Knowledge
USA
2884 Posts |
Posted - 03/13/2009 : 09:54:17
|
that would be a good one to post next time someone asks "how to store images in sql server"
elsasoft.org |
|
|
| |
Topic |
|
|
|
Useless SQL
