March 16, 2026 at 12:00 am
Comments posted to this topic are about the item SQL Art 2: St Patrick’s Day in SSMS (Shamrock + Pint + Pixel Text)
March 16, 2026 at 7:57 pm
running on SQL 2025 build 17.0.1105.2 throws an error for the union and collection aggregates, but finishes without a problem.
Could not find method 'UnionAggregate' for type 'Microsoft.SqlServer.Types.SqlGeometry' in assembly 'Microsoft.SqlServer.Types'.
March 16, 2026 at 8:36 pm
Any pointers on changing the shamrock colour to dark green?
March 16, 2026 at 8:59 pm
you can run this to check colours
USE tempdb;
GO
DROP TABLE IF EXISTS #test;
CREATE TABLE #test (shape geometry, colour varchar(20));
INSERT INTO #test VALUES
(geometry::Point(0, 0, 0).STBuffer(0.5).MakeValid(), 'slot1'),
(geometry::Point(2, 0, 0).STBuffer(0.5).MakeValid(), 'slot2'),
(geometry::Point(4, 0, 0).STBuffer(0.5).MakeValid(), 'slot3'),
(geometry::Point(6, 0, 0).STBuffer(0.5).MakeValid(), 'slot4'),
(geometry::Point(8, 0, 0).STBuffer(0.5).MakeValid(), 'slot5'),
(geometry::Point(10, 0, 0).STBuffer(0.5).MakeValid(), 'slot6'),
(geometry::Point(12, 0, 0).STBuffer(0.5).MakeValid(), 'slot7'),
(geometry::Point(14, 0, 0).STBuffer(0.5).MakeValid(), 'slot8');
SELECT colour, shape FROM #test
ORDER BY colour;
DROP TABLE #test;
you can try adding these dummy shapes
(geometry::Point(-10, 0, 0).STBuffer(0.01).MakeValid(), 'd1'),
(geometry::Point(-11, 0, 0).STBuffer(0.01).MakeValid(), 'd2'),
(geometry::Point(-12, 0, 0).STBuffer(0.01).MakeValid(), 'd3'),
(geometry::Point(-13, 0, 0).STBuffer(0.01).MakeValid(), 'd4'),
(geometry::Point(-14, 0, 0).STBuffer(0.01).MakeValid(), 'd5'),
(geometry::Point(-15, 0, 0).STBuffer(0.01).MakeValid(), 'd6'),
then replace the order by at the bottom
ORDER BY CASE colour
WHEN 'd1' THEN 1
WHEN 'gold' THEN 2
WHEN 'd2' THEN 3
WHEN 'd3' THEN 4
WHEN 'black' THEN 5
WHEN 'd4' THEN 6
WHEN 'd5' THEN 7
WHEN 'darkgreen' THEN 8
WHEN 'lightgray' THEN 9
WHEN 'white' THEN 10
ELSE 11
END;
March 16, 2026 at 9:00 pm
replace the select statement with this SELECT colour, shape
FROM #stPatrick
WHERE shape IS NOT NULL
AND shape.STIsValid() = 1
ORDER BY CASE colour
WHEN 'd1' THEN 1
WHEN 'gold' THEN 2
WHEN 'd2' THEN 3
WHEN 'd3' THEN 4
WHEN 'black' THEN 5
WHEN 'd4' THEN 6
WHEN 'd5' THEN 7
WHEN 'darkgreen' THEN 8
WHEN 'lightgray' THEN 9
WHEN 'white' THEN 10
ELSE 11
END;
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply