ExternalUse ExternalUse - 7 months ago 16
SQL Question

Flattening T-SQL bitmask from enumeration table

I'm trying to flatten a list of functions that are stored in a bitmask, and that are "explained" in an enumeration table like this:

CREATE TABLE #enum([ID] [int] NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[Display] [varchar] (50) NOT NULL,
[DataValue] [int] NOT NULL);

INSERT INTO [#enum] ([Display], [DataValue])
SELECT 'Function A', 1
UNION SELECT 'Function B', 2
UNION SELECT 'Function C', 4
UNION SELECT 'Function D', 8
UNION SELECT 'Function E', 16
UNION SELECT 'Function F', 32
UNION SELECT 'Function G', 64;


My data table looks like this (obviously much simplified):

CREATE TABLE #people ([ID] [int] NOT NULL IDENTITY(1, 1) PRIMARY KEY,
[PersonName] [varchar] (50) NOT NULL,
[Functions] [int] NOT NULL);

INSERT INTO [#people] ([PersonName], [Functions])
SELECT'Ken Hurt', 8
UNION SELECT 'Justin Case', 33
UNION SELECT 'Bill Board', 73;


Now I would like to return a comma-separated list of functions when querying the #people table; result like this:

ID Name Functions
1 Ken Hurt Function D
2 Justin Case Function A, Function F
3 Bill Board Function A, Function D, Function G


I've been looking at Dynamic SQL,
PIVOT
,
STUFF()
and
FOR XML PATH()
for quite some time now but I'm apparently too stupid to digest the concept in its beauty. Could you please help? Going through the list with WHILE or even worse Cursors seems like taking a sledgehammer to crack a nut. Thanks!

Answer

Correct way:

Schema is "broken". Normalize it.


Workaround:

There is no need for WHILE/CURSOR/PIVOT/Dynamic-SQL. Simple bitwise operation will do the job:

SELECT *
FROM #people p
CROSS APPLY(SELECT STUFF((SELECT ',' + e.Display
                          FROM #enum e
                          WHERE p.Functions & DataValue = DataValue
                          ORDER BY e.DataValue
                          FOR XML PATH('')), 1, 1, '')
           ) AS sub(result);

LiveDemo

Output:

╔════╦═════════════╦═══════════╦══════════════════════════════════╗
║ ID ║ PersonName  ║ Functions ║              result              ║
╠════╬═════════════╬═══════════╬══════════════════════════════════╣
║  1 ║ Bill Board  ║        73 ║ Function A,Function D,Function G ║
║  2 ║ Justin Case ║        33 ║ Function A,Function F            ║
║  3 ║ Ken Hurt    ║         8 ║ Function D                       ║
╚════╩═════════════╩═══════════╩══════════════════════════════════╝