SQL Server - Get a new columns with name of the columns have the values X base on rows

Question

I want to get a new columns on table and this new columns need to contain another columns name separate by - example: (field_1-field_2) where the values = X

I can not hard code the column names because each time these column name are different depending on the projects.

|  id  |  field_1 | field_2  | field_3 |  field_4 |  field_5  | new_column              |
|  1   |     X    |  NULL    |      X  |  NULL    |     X     | field_1-field_3-field_5 |
|  2   |  NULL    |     X    |      X  |  NULL    |  NULL     | field_2-field_3         |
|  3   |  NULL    |  NULL    |   NULL  |     X    |  NULL     | field_4                 |

Answer 1

If you don't know the table structure, one possible approach is to generate and execute a dynamic statement. Use the information from sys.columns and sys.tables views to generate the statement.

Table:

CREATE TABLE Data (
    id int,
    field_1 varchar(1),
    field_2 varchar(1),
    field_3 varchar(1),
    field_4 varchar(1),
    field_5 varchar(1)
)
INSERT INTO Data
    (id, field_1, field_2, field_3, field_4, field_5)
VALUES
    (1, 'X',  NULL, 'X',  NULL, 'X'), 
    (2, NULL, 'X',  'X',  NULL, NULL),
    (3, NULL, NULL, NULL, 'X',  NULL)

Statement:

-- Declaration
DECLARE @stm nvarchar(max) = N''

-- Columns names
SELECT @stm = CONCAT(@stm, ', CASE WHEN ', col.[name], ' = ''X'' THEN ''', col.[name], '-'' ELSE '''' END')
FROM sys.columns col
JOIN sys.tables tab ON col.object_id = tab.object_id
WHERE (tab.[name] = 'Data') AND (col.[name] LIKE 'field_%')

-- While statement
SET @stm = CONCAT(
    N'SELECT *, CONCAT(',
    STUFF(@stm, 1, 1, N''), 
    N') AS NewColumn FROM Data'
)

-- Execution
PRINT @stm
EXEC sp_executesql @stm

Output:

-------------------------------------------------------------------
id  field_1 field_2 field_3 field_4 field_5 NewColumn
-------------------------------------------------------------------
1   X       NULL    X       NULL    X       field_1-field_3-field_5-
2   NULL    X       X       NULL    NULL    field_2-field_3-
3   NULL    NULL    NULL    X       NULL    field_4-