Put numbers for each rows in STRING_AGG in SQL Server

Question

I have to put some columns and group them together by STRING_AGG also I want to put number first of each rows

What I have:

Name	Cake	Coca	ice-cream
Same	one	five	six
Sara	one	one	NULL
John	two	two	NULL

I want the output be something like this:

Name	Description
Sam	1.two 2.five 3. six
Sara	1.one 2.one
John	1.two 2.two

My Code:

SELECT Name, STRIN_AGG(CONCAT(Cake, ' ,', Coca,' ,', ice-cream))
FROM FoodTable

but I do not know how to consider numbers first of each rows in STRING_AGG

Answer 1

You don't need string_agg():

select name,
       concat('1.' + cake, ' 2.' + coca, ' 3.' + ice_cream)
from t;

Note that + returns NULL if any value is NULL. However, concat() simply ignores NULL values.

If you really, really wanted to use string_agg() you could:

select t.name, v.all_together
from t cross apply
     (select string_agg(v.n + v.val, ' ') within group (order by v.n) as all_together
      from (values ('1.', t.cake),
                   ('2.', t.coca),
                   ('3.', t.ice_cream)
           ) v(n, val)
     ) v;

Answer 2

Here is a complete dynamic sql approach. No need to serialize and deserialize the data using XML or JSON. In this case the list of food items is contained in a temporary table so it reads the column names from tempdb.sys.columns.

The query uses CROSS APPLY to unpivot the columns (of food items) and assigns a ROW_NUMBER() to each non NULL item value. Something like this

drop table if exists #FoodTable;
go
create table #FoodTable(
  [Name]            varchar(100) not null,
  Cake              varchar(100) null,
  Coca              varchar(100) null,
  [ice-cream]       varchar(100) null);
--select * from dbo.test_actuals

insert #FoodTable values
('Sam', 'one', 'five', 'six'),
('Sara', 'one', 'one', null),
('Jon', 'two', 'two', null);

;with unpvt_cte([Name], item, val, rn) as (
    select f.[Name], v.*, row_number() over (partition by [Name] order by (select null))
    from #FoodTable f
         cross apply (values ('Cake', Cake),
                             ('Coca', Coca),
                             ('IceCream', [ice-cream])) v(item, val)
    where v.val is not null)
select [Name], string_agg(concat(rn, '.', val), ' ') within group (order by rn) answer
from unpvt_cte
group by [Name];

Name        answer
Jon         1.two 2.two
Sam         1.one 2.five 3.six
Sara        1.one 2.one

to make the query dynamic

declare @food_list              nvarchar(max);

select @food_list=string_agg(quotename(concat_ws(',', quotename(sysc.[name], ''''), 
                             quotename(sysc.[name], '[]')), '()'), ',')
from   tempdb.sys.columns sysc
where  object_id = Object_id('tempdb..#FoodTable')
       and [name]<>'Name'; 

declare 
  @sql_prefix             nvarchar(max)=N'
;with unpvt_cte([Name], item, val, rn) as (
    select f.[Name], v.*, row_number() over (partition by [Name] order by (select null))
    from #FoodTable f
         cross apply (values ',
  @sql_suffix             nvarchar(max)=N'
         ) v(item, val)
    where v.val is not null)
select [Name], string_agg(concat(rn, ''.'', val), '' '') within group (order by rn) answer
from unpvt_cte
group by [Name];';
declare
  @sql                    nvarchar(max)=concat(@sql_prefix, @food_list, @sql_suffix);

print(@sql);
exec sp_executesql @sql;

The print statement outputs the following

;with unpvt_cte([Name], item, val, rn) as (
    select f.[Name], v.*, row_number() over (partition by [Name] order by (select null))
    from #FoodTable f
         cross apply (values ('Cake',[Cake]),('Coca',[Coca]),('ice-cream',[ice-cream])
         ) v(item, val)
    where v.val is not null)
select [Name], string_agg(concat(rn, '.', val), ' ') within group (order by rn) answer
from unpvt_cte
group by [Name];

Answer 3

You may use a union to acquire the numbers for each column. Here I've used a cte but you could have used a subquery. Each query in the union renames the food type column to food and adds a column num that will be used in the final query. In the final query the where clause filters NULL foods and a group by with the string_agg and concat is used to retrieve the data in the desired format. I've included a working fiddle below:

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 Description
FROM
    FoodTableNums
WHERE
    food IS NOT NULL
GROUP BY
    Name
    

Name	Description
John	1.two,2.two
Sam	1.one,2.five,3.six
Sara	1.one,2.one

db<>fiddle here

Let me know if this works for you.

Answer 4

Here is an option that is a bit more dynamic. You only have to Exclude certain columns ... in this case NAME

We use a bit of JSON to dynamically UNPIVOT the row, and then string_agg() to consolidate.

Example or dbFiddle

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

Results

Name    NewValue
Same    1.one 2.five 3.six
Sara    1.one 2.one
John    1.two 2.two

Answer 5

Although I agree with those saying that it would be much better to normalise your tables, but if you can't do that then this proposal makes GGordon's solution dynamic, building an SQL statement that retrieves all the columns on your FoodTable. No matter if they are 3 or 100 food columns.

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);

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;

You can see it working here : Fiddle

Answer 6

Here is s generic way regardless of how many columns in a table.

It is based on XML/XQuery.

No need to UNPIVOT the rows, and then STRING_AGG() to consolidate.

All data in each row stays in a row.

SQL

-- DDL and data population, start
DECLARE @tbl table  (
  [Name]            varchar(100) not NULL PRIMARY KEY,
  Cake              varchar(100) null,
  Coca              varchar(100) null,
  [ice-cream]       varchar(100) null);
INSERT @tbl VALUES
('Sam', 'one', 'five', 'six'),
('Sara', 'one', 'one', null),
('Jon', 'two', 'two', null);
-- DDL and data population, end

SELECT p.[Name] 
    , x.query('
    for $r in /root/*[local-name()!="Name"]/text()
    let $pos := count(root/*[. << $r]) - 1
    return concat(string($pos), ".", $r)').value('text()[1]', 'VARCHAR(MAX)') AS Result
FROM @tbl AS p
   CROSS APPLY (SELECT * FROM @tbl AS c
      WHERE c.[Name] = p.[Name]
      FOR XML PATH(''), TYPE, ROOT('root')) AS t(x);

Output

+------+--------------------+
| Name |       Result       |
+------+--------------------+
| Jon  | 1.two 2.two        |
| Sam  | 1.one 2.five 3.six |
| Sara | 1.one 2.one        |
+------+--------------------+