0

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 Email
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 Email Portal API
ATA Email Portal API
Email Portal
API
Email API
ATA Portal
Portal API
API
ATA Portal
ATA Portal API

Thank you

4
  • Just to clarify what final results you're looking for: For example, if ClientType = 15, you want the results to include a row for every ClientType with a bitwise combination <= 15?...or would a single row with the distinct list of ClientTypes with a bitwise combination <= 15 as 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.
    – J.D.
    Sep 3, 2022 at 3:23
  • @J.D. Having it take the result set be a single row comma delimited is what I'm looking for. ClientType =15 converts to 'ATA, Email, Portal, API' based on their values adding up to 15
    – Derek A
    Sep 3, 2022 at 7:08
  • Ok cool, I have a fairly simple solution for that. One other question I meant to ask, is what version of SQL Server are you using?
    – J.D.
    Sep 3, 2022 at 12:12
  • @J.D. I believe it's sql server 2016. Could be newer.
    – Derek A
    Sep 4, 2022 at 7:45

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.