Database Challenges

--TSQLContest21Submission_JesseRoberge.sql
WITH
	N4           AS (SELECT 1 AS C UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1)
	,N16         AS (SELECT 1 AS C FROM N4 AS A CROSS JOIN N4 AS B)
	,N256        AS (SELECT 1 AS C FROM N16 AS A CROSS JOIN N16 AS B)
	,N65536      AS (SELECT 1 AS C FROM N256 AS A CROSS JOIN N256 AS B)
	,Numbers     AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS PK_CountID FROM N65536)
	,ScanNumberMap AS (
		SELECT NULL AS RowPos, NULL AS Digit, NULL AS ScanNumber WHERE 1=2 UNION ALL
		SELECT 1, 'S', CONVERT(Char(3), '###') UNION ALL
		SELECT 2, 'S', CONVERT(Char(3), '###') UNION ALL
		SELECT 3, 'S', CONVERT(Char(3), '###') UNION ALL
		SELECT 1, '0', CONVERT(Char(3), '#_#') UNION ALL
		SELECT 2, '0', CONVERT(Char(3), '|#|') UNION ALL
		SELECT 3, '0', CONVERT(Char(3), '|_|') UNION ALL
		SELECT 1, '1', CONVERT(Char(3), '###') UNION ALL
		SELECT 2, '1', CONVERT(Char(3), '##|') UNION ALL
		SELECT 3, '1', CONVERT(Char(3), '##|') UNION ALL
		SELECT 1, '2', CONVERT(Char(3), '#_#') UNION ALL
		SELECT 2, '2', CONVERT(Char(3), '#_|') UNION ALL
		SELECT 3, '2', CONVERT(Char(3), '|_#') UNION ALL
		SELECT 1, '3', CONVERT(Char(3), '#_#') UNION ALL
		SELECT 2, '3', CONVERT(Char(3), '#_|') UNION ALL
		SELECT 3, '3', CONVERT(Char(3), '#_|') UNION ALL
		SELECT 1, '4', CONVERT(Char(3), '###') UNION ALL
		SELECT 2, '4', CONVERT(Char(3), '|_|') UNION ALL
		SELECT 3, '4', CONVERT(Char(3), '##|') UNION ALL
		SELECT 1, '5', CONVERT(Char(3), '#_#') UNION ALL
		SELECT 2, '5', CONVERT(Char(3), '|_#') UNION ALL
		SELECT 3, '5', CONVERT(Char(3), '#_|') UNION ALL
		SELECT 1, '6', CONVERT(Char(3), '#_#') UNION ALL
		SELECT 2, '6', CONVERT(Char(3), '|_#') UNION ALL
		SELECT 3, '6', CONVERT(Char(3), '|_|') UNION ALL
		SELECT 1, '7', CONVERT(Char(3), '#_#') UNION ALL
		SELECT 2, '7', CONVERT(Char(3), '##|') UNION ALL
		SELECT 3, '7', CONVERT(Char(3), '##|') UNION ALL
		SELECT 1, '8', CONVERT(Char(3), '#_#') UNION ALL
		SELECT 2, '8', CONVERT(Char(3), '|_|') UNION ALL
		SELECT 3, '8', CONVERT(Char(3), '|_|') UNION ALL
		SELECT 1, '9', CONVERT(Char(3), '#_#') UNION ALL
		SELECT 2, '9', CONVERT(Char(3), '|_|') UNION ALL
		SELECT 3, '9', CONVERT(Char(3), '#_|')
	)
	,DSource  AS (
		SELECT ID, REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONVERT(VarChar(1000), ScanNumber), Char(13), ','), Char(10), '*'), ',*', ','), '*,', ','), '*', ','), ' ', '#') AS ScanNumber
		FROM @T AS DataSource
	),
	DataSource AS (
		SELECT ID, (LEN(SubString(ScanNumber, 1, CharIndex(',', ScanNumber)-1))+2)/3 AS DigitCount, ScanNumber
		FROM DSource
	)
	,SourceScanNumbers AS (
		SELECT
			DataSource.ID,
			--Cross Apply with one ID at a time to be able to use FOR XML PATH('') Concatenation
			(
				SELECT ISNULL(Number.Digit,'X')
				FROM
					--Generate placeholders (this has unfortunately caused a table spool to occur in the query plan)
					(
						SELECT Numbers.PK_CountID AS ColPos
						FROM Numbers
						WHERE Numbers.PK_CountID BETWEEN 1 AND DataSource.DigitCount
					) AS Placeholder
					--Parse the dgits
					LEFT OUTER JOIN (
						SELECT Number.ColPos, Number.Digit
						FROM
							(
								--Preprocess the digit so it is not repeat-processed in the GROUP BY
								SELECT SourceNumbers.ColPos, ISNULL(ScanNumberMap.Digit, 'X') AS Digit
								FROM
									--Separate the rows
									(
										SELECT
											ROW_NUMBER() OVER (ORDER BY PK_CountID) AS RowPos,
											SUBSTRING(DataSource.ScanNumber+',', PK_CountID, CHARINDEX(',', DataSource.ScanNumber+',', PK_CountID)-PK_CountID) AS ScanNumber
										FROM Numbers
										WHERE PK_CountID >0 AND PK_CountID<LEN(DataSource.ScanNumber)+LEN(',') AND SubString(',' + DataSource.ScanNumber + ',', PK_CountID, 1)=','
									) AS SourceScanNumbers
									--Separate the digits to a row-digit matrix
									OUTER APPLY (
										SELECT PK_CountID AS ColPos, SubString(SourceScanNumbers.ScanNumber, PK_CountID*3-2, 3) AS ScanNumberSingle
										FROM Numbers
										WHERE PK_CountID BETWEEN 1 AND DataSource.DigitCount
									) AS SourceNumbers
									--Cross-reference with mapping pseudo-temp-table (hardcoded constants formed by UNION ALL row constructors)
									LEFT OUTER JOIN ScanNumberMap ON SourceScanNumbers.RowPos=ScanNumberMap.RowPos AND SourceNumbers.ScanNumberSingle=ScanNumberMap.ScanNumber
								--First three rows only.
								WHERE
									SourceScanNumbers.RowPos<4 AND SourceNumbers.ColPos<=DataSource.DigitCount
							) AS Number
						GROUP BY Number.ColPos, Number.Digit
						HAVING COUNT(*)>2
					) AS Number ON PlaceHolder.ColPos=Number.ColPos
				ORDER BY PlaceHolder.ColPos, Digit
				FOR XML PATH ('')
			) AS Number

		FROM DataSource
	)
SELECT ID, REPLACE(Number, 'S', ' ') AS Number
FROM SourceScanNumbers
ORDER BY ID

Did you find something incorrect/wrong with this solution? Take a few seconds to Report It.

Did you understand how this solution work? If you find it difficult to understand, you can Request an Explanation or you can Write an explanation to help others better understand this solution.