StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here's one way to do it without the split function and without the loop. Note that this doesn't turn out in the exact order of your expected output.</p> <pre><code>DECLARE @pos TABLE ( PositionId INT, PositionName VARCHAR(32) ); INSERT @pos SELECT 1, 'Defensive End' UNION ALL SELECT 2, 'Quarterback' UNION ALL SELECT 3, 'Pitcher' UNION ALL SELECT 4, 'Catcher' UNION ALL SELECT 5, 'First Base'; DECLARE @row TABLE(pList VARCHAR(32)); INSERT @row SELECT '1,2,3,' UNION ALL SELECT '1,0,0,' UNION ALL SELECT '0,0,0,'; ;WITH cte AS ( SELECT x.pList, p.PositionName FROM @row AS x LEFT OUTER JOIN @pos AS p ON ',' + x.pList LIKE '%,' + CONVERT(VARCHAR(12), p.PositionID) + ',%' ) SELECT COALESCE(NULLIF(STUFF(( SELECT ', ' + PositionName FROM cte AS cte2 WHERE cte.pList = cte2.pList FOR XML PATH(''), TYPE).value('.[1]','varchar(max)'), 1, 2, ''), ''), 'No position selected' ) FROM cte GROUP BY pList; </code></pre> <p><strong>EDIT</strong></p> <p>Here is a slightly different version that seems to obey the order in the original list and relies on a numbers table...</p> <pre><code>CREATE TABLE dbo.Numbers(n INT PRIMARY KEY); INSERT dbo.Numbers(n) SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY s1.[object_id]) FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2; </code></pre> <p>...and the <code>OPTION (FORCE ORDER)</code> hint:</p> <pre><code>;WITH cte AS ( SELECT *, r = ROW_NUMBER() OVER (PARTITION BY pList ORDER BY n.n) FROM @row AS r LEFT OUTER JOIN @pos AS p ON ',' + r.pList + ',' LIKE '%,' + RTRIM(p.PositionId) + ',%' LEFT OUTER JOIN dbo.Numbers AS n ON n.n = CHARINDEX(',' + RTRIM(p.PositionId) + ',', ',' + r.pList + ',') ) SELECT DISTINCT pList, Position = COALESCE(STUFF((SELECT ',' + PositionName FROM cte AS cte2 INNER JOIN dbo.Numbers AS n ON n.n = cte2.r WHERE cte2.pList = cte.pList FOR XML PATH(''), TYPE ).value('.[1]', 'nvarchar(max)'), 1, 1, ''), 'No position selected') FROM cte OPTION (FORCE ORDER); </code></pre> <p>This relies on the assumption that the optimizer will choose the clustered primary key on the numbers table. To enforce this further it may make sense to ensure that that index is used by naming it using the <code>WITH (INDEX)</code> hint on both of the joins to <code>dbo.Numbers</code> (which means you should probably name the primary key constraint vs. doing it the lazy way like I did above).</p> <p>Sorry it took me a bit to come back to this.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload