I am trying to take a single int value, and covert it into a string of comma separated text values based on these values
ATA = 1
Email = 2
Portal = 4
API = 8
Realtime = 16
For example, a ClientType = 1 would convert to just "ATA", whereas ClientType = 15 would covert to "ATA, Email, Portal, API"
So it would look something like:
| ClientType | Client Type(s) |
|---|---|
| 1 | ATA |
| 2 | |
| 3 | ATA, Email |
| 4 | Portal |
| 5 | ATA, Portal |
| 15 | ATA, Email, Portal, API |
and so on...
I did start off by making this list in a CASE Statement, but I feel like there is a better way of doing this especially when we add more values for other client types later on.
Select
CASE ClientType
When 0 then ''
When 1 then 'ATA'
When 2 then 'Email'
When 3 then 'ATA, Email'
When 4 then 'Portal'
When 5 then 'ATA, Portal'
When 6 then 'Email, Portal'
When 7 then 'ATA, Email, Portal'
When 8 then 'API'
When 9 then 'ATA, API'
When 10 then 'Email, API'
When 11 then 'ATA, Email, API'
When 12 then 'Portal, API'
When 13 then 'ATA, Portal, API'
When 14 then 'Email, Portal, API'
When 15 then 'ATA, Email, Portal, API'
When 16 then 'Realtime Client'
End 'Client Type(s)'
I was able to use this to make a separate column for each value if they had it, however that was a lot of empty columns if they only had a value or two.
DECLARE @Portal as int
DECLARE @Email as int
DECLARE @ATA as int
DECLARE @API as int
DECLARE @Realtime as int
SET @ATA = 0x00000001
SET @Email = 0x00000002
SET @Portal = 0x00000004
SET @API = 0x00000008
SET @Realtime = 0x00000016
Select
CASE WHEN NS_ACCOUNTS_TABLE.ClientType & @ATA = @ATA THEN 'ATA ' ELSE '' END 'ATA',
CASE WHEN NS_ACCOUNTS_TABLE.ClientType & @Email = @Email THEN 'Email' ELSE '' END 'Email',
CASE WHEN NS_ACCOUNTS_TABLE.ClientType & @Portal = @Portal THEN 'Portal' ELSE '' END 'Portal',
CASE WHEN NS_ACCOUNTS_TABLE.ClientType & @API = @API THEN 'API' ELSE '' END 'API',
CASE WHEN NS_ACCOUNTS_TABLE.ClientType & @Realtime = @Realtime THEN 'Realtime' ELSE '' END 'Realtime',
and would end with a result set like:
| ATA | Portal | API | |
|---|---|---|---|
| ATA | Portal | API | |
| Portal | |||
| API | |||
| API | |||
| ATA | Portal | ||
| Portal | API | ||
| API | |||
| ATA | Portal | ||
| ATA | Portal | API |
Thank you
1 Answer 1
CREATE OR ALTER VIEW dbo.ClientTypeFlags
WITH SCHEMABINDING
AS
SELECT FlagValue = 1, FlagName = 'ATA' UNION ALL
SELECT 2, 'Email' UNION ALL
SELECT 4, 'Portal' UNION ALL
SELECT 8, 'API' UNION ALL
SELECT 16, 'Realtime';
GO
DECLARE @T table (ClientType integer NOT NULL);
INSERT @T (ClientType)
VALUES (1), (2), (3), (4), (5), (15);
SELECT
T.ClientType,
ClientTypes =
STRING_AGG(CTF.FlagName, ', ')
WITHIN GROUP (ORDER BY CTF.FlagValue)
FROM @T AS T
JOIN dbo.ClientTypeFlags AS CTF
ON T.ClientType & CTF.FlagValue = CTF.FlagValue
GROUP BY
T.ClientType;
SQL Server 2017 db<>fiddle
For versions of SQL Server before 2017, use string concatenation via FOR XML PATH in place of STRING_AGG:
SELECT
T.ClientType,
ClientTypes =
(
SELECT STUFF
(
(
SELECT ', ' + CTF.FlagName
FROM dbo.ClientTypeFlags AS CTF
WHERE
T.ClientType & CTF.FlagValue = CTF.FlagValue
ORDER BY
CTF.FlagValue
FOR XML PATH (''), TYPE
)
.value('text()[1]', 'varchar(8000)'),
1, 2, ''
)
)
FROM @T AS T;
SQL Server 2014 db<>fiddle
You don't have to use a view to hold the enumeration, it's just an example implementation. You could use a function or permanent table instead. Inlining the values into queries would make maintenance harder.
As you can probably see, encoding multiple pieces of information in a single attribute is not easy to work with in a relational database. If you have the option, refactor the ClientType to a relational design.
ClientType = 15, you want the results to include a row for everyClientTypewith a bitwise combination<= 15?...or would a single row with the distinct list ofClientTypeswith a bitwise combination<= 15as a comma delimited string be sufficient? The latter I have a solution for, but if you want the former I'd have to think about it.