Create a variable containing all possible combinations, depending whether the associated variable is 1 or 0

Question

I am creating the new variable Subsector, which should contain the names of all the parties, divided by a dash. All combinations are possible, as each option can either be 0 or 1. So I can have in the Subsector for Brokers / Custodian alone the combos below ( and that is also how Subsector should look like in the end:

Of course I can continue writing When clauses for all possible combos. I am just wondering, is there something more clever and elegant? Because I know, in SAS you can just write this as:

else Subsector=catx(' - ',Broker_Custodian,CrowdFunding_provider,Derivative_Issuer,DIMS_provider ...);

I thought that CONCAT was the equivalent in SQL. But in this case it just adds all the options into one line. It does not calculate, which ones are 0 or 1. Any ideas?

SELECT 
* ,

CASE
    WHEN (('Broker_Custodian' ='0' ) 
    AND  ('CrowdFunding_provider' ='0' )
    AND  ('Derivative_Issuer' ='0' )
    AND  ('DIMS_provider' ='0' )
    AND  ('Financial_Adviser' ='0' )
    AND  ('Fund_Manager' ='0' )
    AND  ('Issuer_of_Securities' ='0' )
    AND  ('Peer_2_Peer' ='0' )
    AND  ('Broker_Custodian' ='0' )
    AND  ('Other_Sector' ='0' )
    AND  ('Trustee_Licensed_supervisor') ='0' )
     THEN 'None'   ELSE
     CONCAT(' - ','Brokers / Custodians', 'Derivatives issuer' , 'DIMS provider' , 'Equity crowdfunding services',
    'Financial adviser', 'Fund manager' , 'Issuer_Of_Securities', 'Peer-to-peer lending provider' ,
                        'Trustee corporation / Licensed supervisor' ,'Other') END AS Subsector  FROM #TEST

correct equivalent will be `STRING_AGG` if all you data to be pushed in to sub-sector is in single column across multiple rows — DhruvJoshi, Sep 06 '20 at 06:23

DhruvJoshi · Answer 1 · 2020-09-06T08:02:29.000

SQL server can do STRING_AGG which can be done over the unpivoted data set like below

SELECT 
    someIDcolumn, 
    STRING_AGG(CASE WHEN ConcatValue=0 THEN ConcatString END,' - ') WITHIN GROUP (ORDER BY ConcatString ASC) AS Subsector
CROSS APPLY(
    VALUES
        ('Brokers / Custodians',Broker_Custodian),
        ('Equity crowdfunding services',CrowdFunding_provider),
        ('Derivatives issuer',Derivative_Issuer),
        ('DIMS provider' ,DIMS_provider),
        ('Financial adviser',Financial_Adviser),
        ('Fund manager' ,Fund_Manager),
        ('Issuer_Of_Securities',Issuer_of_Secrocurities),
        ('Peer-to-peer lending provider' ,'Peer_2_Peer'),
        ('Other',Other_Sector),
        ('Trustee corporation / Licensed supervisor' ,Trustee_Licensed_supervisor)
    )ConcatTable(ConcatString,ConcatValue)
GROUP BY someIDcolumn

Solution using FOR XML PATH

; WITH CTE AS
(        
    SELECT 
        someIDcolumn, ConcatString,ConcatValue
    FROM yourtable t2
    CROSS APPLY(
        VALUES
            ('Brokers / Custodians',Broker_Custodian),
            ('Equity crowdfunding services',CrowdFunding_provider),
            ('Derivatives issuer',Derivative_Issuer),
            ('DIMS provider' ,DIMS_provider),
            ('Financial adviser',Financial_Adviser),
            ('Fund manager' ,Fund_Manager),
            ('Issuer_Of_Securities',Issuer_of_Secrocurities),
            ('Peer-to-peer lending provider' ,'Peer_2_Peer'),
            ('Other',Other_Sector),
            ('Trustee corporation / Licensed supervisor' ,Trustee_Licensed_supervisor)
        )ConcatTable(ConcatString,ConcatValue)

)

SELECT
    someIDcolumn,
    Subsector = STUFF(
             (SELECT '-' + ConcatString 
              FROM CTE t1
              WHERE t1.someIDcolumn = t2.someIDcolumn AND ConcatValue=0
              FOR XML PATH (''))
             , 1, 1, '') 
FROM CTE t2
GROUP BY someIDcolumn

Thank you for rewriting the query quickly - I wasn't that fast in providing a dummy data set. However, I don't think STRING_AGG is compatible with SQL Server 17 unfortunately. Itr complains: ''STRING_AGG' is not a recognized built-in function name.' — Anna, Sep 06 '20 at 07:38
@Anna in that case you can use FOR XML PATH approach as detailed in this answer https://stackoverflow.com/a/31212041/1123226 — DhruvJoshi, Sep 06 '20 at 07:56
@Anna added the for xml path solution. Please upvote/mark as correct answer if you feel this helped you — DhruvJoshi, Sep 06 '20 at 08:03
what is the CTE in WITH CTE AS - I am having problems to get that part working — Anna, Sep 06 '20 at 20:36
https://www.essentialsql.com/introduction-common-table-expressions-ctes/ — DhruvJoshi, Sep 07 '20 at 06:42

Create a variable containing all possible combinations, depending whether the associated variable is 1 or 0

1 Answers1