Let us play Chess with TSQL in this challenge.
The input table contains strings that represent the layout of chess boards filled with pieces using
Forsyth-Edwards Notation. Your job is to read the input string and generate a result set that represents
the position of pieces in a chess board.
Want to make SQL effortless?
With code-completion, SQL reformatting, script summaries and more, SQL Prompt 5 makes writing, editing, and exploring SQL effortless. Download a 14-day free trial now.
Each row in the input table represents the current position of a chess board filled with pieces at various positions. The sample input given above represents a chess board as given below.
Your job is to translate this into a result set that looks like the below illustration.
ID RowID a b c d e f g h
-- ----- ---- ---- ---- ---- ---- ---- ---- ----
1 8 r n b q k r
1 7 p p p p b p p p
1 6 n
1 5 p
1 4 B P
1 3 Q
1 2 P P P P P P P
1 1 R N B K N R
- The input table will have more than one row.
- The output should be a single result set with 10 columns. The first column should show the ID of the input table. and second column should be RowID in decending order.
- 8 rows should be generated for every row in the input table. The 8 rows representing each chess board should be ordered as specified in Algebraic Chess Notation. The first column of first row should represent cell “8a” and the first cell of the 8th row should represent cell “1a”.
- Chess boards should be first ordered by ID in ascending and then RowID in decending order.
Use the TSQL Script given below to generate the source tables and fill them with sample data.
IF OBJECT_ID('TC58','U') IS NOT NULL BEGIN
DROP TABLE TC58
CREATE TABLE TC58(
ID INT IDENTITY PRIMARY KEY,
INSERT INTO TC58(Layout)
SELECT * FROM TC58
- The solution should be a single query that starts with a "SELECT" or “;WITH”
- Read the Submission Guidelines and make sure that your solution follows them.
- If you would like to use a Tally Table, you can use the script given here. Your solution should not include the script to create and populate the tally table. You can assume that the tally table will be available in the database where the evaluation team will run your Code.
|SQL Source Control - now with static data support |
"By allowing static data to be versioned, SQL Source Control now becomes a complete source of truth for the application's data layer. I'm in." Troy Hunt, Software Architect.
More information and a free trial.