By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
| (No column name) |
|---|
| Microsoft SQL Server 2019 (RTM-CU16) (KB5011644) - 15.0.4223.1 (X64) Apr 11 2022 16:24:07 Copyright (C) 2019 Microsoft Corporation Express Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor) |
CREATE TABLE FoodTable (
Name VARCHAR(4),
Cake VARCHAR(3),
Coca VARCHAR(4),
icecream VARCHAR(4)
);
INSERT INTO FoodTable
("Name", "Cake", "Coca", "icecream")
VALUES
('Sam', 'one', 'five', 'six'),
('Sara', 'one', 'one', NULL),
('John', 'two', 'two', NULL);
3 rows affected
declare @SQL nvarchar(max);
WITH Food As (
SELECT ORDINAL_POSITION - 1 AS Num, COLUMN_NAME AS Food
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'FoodTable' AND COLUMN_NAME <> 'Name'
)
SELECT @SQL = N'WITH FoodTableNums AS ( ' +
string_agg('SELECT Name, ' + Food + ' as Food, ' + convert(varchar(20), Num) + ' as Num FROM FoodTable', ' UNION ALL ') +
') SELECT Name, STRING_AGG(CONCAT(num,''.'', food),'','' ) WITHIN GROUP( ORDER BY num asc) as Description FROM FoodTableNums WHERE food IS NOT NULL GROUP BY Name'
FROM Food;
execute sp_ExecuteSQL @SQL;
| Name | Description |
|---|---|
| John | 1.two,2.two |
| Sam | 1.one,2.five,3.six |
| Sara | 1.one,2.one |