/*
=============================================================================================
CREATE DATE: 03/06/2010
 CREATED BY: SETH PHELABAUM
    PURPOSE: TSQL Challenge 21 - Reading, recognizing and processing graphical representation of digits.
     ISSUES: 
      NOTES:	
Revision History
Date	 By		Change Made
-------- ---  	-------------------------------------
=============================================================================================
*/
--   ' _ ','   ',' _ ',' _ ','   ',' _ ',' _ ',' _ ',' _ ',' _ '
--   '| |','  |',' _|',' _|','|_|','|_ ','|_ ','  |','|_|','|_|'
--   '|_|','  |','|_ ',' _|','  |',' _|','|_|','  |','|_|',' _|'

; WITH 
	-- Tally table Gen
		t1 AS (SELECT 1 N UNION ALL SELECT 1 N), 
		t2 AS (SELECT 1 N FROM t1 x, t1 y),        
		t3 AS (SELECT 1 N FROM t2 x, t2 y),        
		Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N FROM t3 x, t3 y),
	CRS AS (
		SELECT	Id, ScanNumber, 
				CHARINDEX(CHAR(10), ScanNumber, CHARINDEX(CHAR(10), ScanNumber)+1) CR2,
				CHARINDEX(CHAR(10), ScanNumber) CR
		FROM @t),
	Lines AS (
		SELECT Id, CR, SUBSTRING(ScanNumber, 1, CR) L1, SUBSTRING(ScanNumber, CR+1, CR) L2, SUBSTRING(ScanNumber, CR2+1, CR) L3
		FROM CRS),
	Matches (Digit, Code)
		 AS ( 	SELECT	'0', ' _ | ||_|' UNION ALL
				SELECT	'1', '     |  |' UNION ALL
				SELECT	'2', ' _  _||_ ' UNION ALL
				SELECT	'3', ' _  _| _|' UNION ALL
				SELECT	'4', '   |_|  |' UNION ALL
				SELECT	'5', ' _ |_  _|' UNION ALL
				SELECT	'6', ' _ |_ |_|' UNION ALL
				SELECT	'7', ' _   |  |' UNION ALL
				SELECT	'8', ' _ |_||_|' UNION ALL
				SELECT	'9', ' _ |_| _|' UNION ALL
				SELECT  'S', '         '
				),
	LSS (Id, N, L1, L2, L3, Digits, RN)
		AS (	SELECT Id, ((3*N) - 3), L1, L2, L3, 
						SUBSTRING(L1,((3*N) - 2), 3) + SUBSTRING(L2,((3*N) - 2), 3) + SUBSTRING(L3,((3*N) - 2), 3),
						ROW_NUMBER() OVER (PARTITION BY Id ORDER BY N)
				FROM Lines, Tally
				WHERE N <= Lines.CR/3),
	Matchup (ID, RN, MatchDigit) 
		AS (	SELECT Id, RN, ISNULL(Digit,'X')
				FROM LSS L
					LEFT JOIN Matches M ON L.Digits = M.Code)
					
SELECT ID, REPLACE((SELECT '' + MatchDigit 
			FROM Matchup M 
			WHERE M.Id = T.ID
			ORDER BY RN
			FOR XML PATH('')),'S',' ') Value
FROM @t T