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-CU6) (KB4563110) - 15.0.4053.23 (X64) Jul 25 2020 11:26:55 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
WITH FoodTableNums AS (
SELECT Name, Cake as food, 1 as num FROM FoodTable UNION ALL
SELECT Name, Coca as food, 2 as num FROM FoodTable UNION ALL
SELECT Name, icecream as food, 3 as num FROM FoodTable
)
SELECT
Name,
STRING_AGG(CONCAT(num,'.', food),',' ) WITHIN GROUP( ORDER BY num asc) as Decription
FROM
FoodTableNums
WHERE
food IS NOT NULL
GROUP BY
Name
| Name | Decription |
|---|---|
| John | 1.two,2.two |
| Sam | 1.one,2.five,3.six |
| Sara | 1.one,2.one |