SQL Server - 'Transpose' binary columns into a single row if binary value=1. Use column na-CodePudding

I have the following dbo.Sales table in SQL Server:

item	United Kingdom	Germany	France	Spain
X1	1	1	1	0
X2	0	0	1	1
X3	1	0	0	0

I want to prepare the dataset so that the binary columns are converted into a single row with comma separated values where the binary value = 1. However, I want the column names to be used rather than the binary values.

item	Countries
X1	United Kingdom, Germany, France
X2	France, Spain
X3	United Kingdom

I have tried using pivot and unpivot without any success. Could anyone advise the best approach please.

CodePudding user response：

Assuming your columns are fixed, you can use a condition case expression in a values clause in conjunction with string_agg - requires SQL Server 2017

select item, String_Agg(Countries, ', ') Countries
from t
cross apply(values
    (case when [united kingdom] = 1 then 'United Kingdom' end),
    (case when Germany = 1 then 'Germany' end),
    (case when France = 1 then 'France' end),
    (case when Spain = 1 then 'Spain' end)
)c(Countries)
group by Item

CodePudding user response：

You can use UNPIVOT, your query would be (SQL server 2017 or above):

SELECT item
    ,STRING_AGG(countries, ',')
FROM (
    SELECT item
        ,countries
        ,value
    FROM (
        SELECT item
            ,[United Kingdom]
            ,Germany
            ,France
            ,Spain
        FROM dbo.Sales
        ) p
    UNPIVOT(value FOR countries IN (
                [United Kingdom]
                ,Germany
                ,France
                ,Spain
                )) AS unpvt
    WHERE VALUE = 1
    ) a
GROUP BY item;

CodePudding user response：

If your columns are fixed:

SELECT item, STRING_AGG(countries, ',')
FROM (
    SELECT item, countries, value
    FROM sales p
    UNPIVOT(value FOR countries IN ([United Kingdom],Germany,France,Spain)) AS unpvt
    WHERE VALUE = 1
    ) a
GROUP BY item

if not:

declare @columns varchar(max)
select @columns = STUFF((select ',[' name ']' 
                         from sys.columns 
                         where  object_id = Object_id('sales') and 
                                name <>'item' FOR XML PATH('')), 1, 1, '')

exec('
SELECT item, STRING_AGG(countries, '','')
FROM (
    SELECT item, countries, value
    FROM sales p
    UNPIVOT(value FOR countries IN (' @columns ')) AS unpvt
    WHERE VALUE = 1
    ) a
GROUP BY item')