By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Declare @YourTable Table ([Name] varchar(50),[Cake] varchar(50),[Coca] varchar(50),[ice-cream] varchar(50)) Insert Into @YourTable Values
('Same','one','five','six')
,('Sara','one','one',NULL)
,('John','two','two',NULL)
Select A.Name
,B.NewValue
From @YourTable A
Cross Apply (
Select NewValue=STRING_AGG(concat(Seq,'.',Value),' ') within group (order by Seq)
From (
Select [Key]
,[Value]
,[Seq] = row_number() over (order by @@spid)
From OpenJson( (Select A.* For JSON Path,Without_Array_Wrapper ) )
Where [Key] not in ('Name')
) B1
) B
| Name | NewValue |
|---|---|
| Same | 1.one 2.five 3.six |
| Sara | 1.one 2.one |
| John | 1.two 2.two |