Dynamic Pivot with Concatenation

How should SQL be modified to concatenate text "Name" into column header and 2 name fields for pivoted data?

I have table like:

UmpID	FirstN	LastN	Age
1	A	H	J
2	B	I	S
3	C	J	A
4	D	K	J
5	E	L	S
6	F	M	J
7	G	N	J

Query:

SET NoCount ON;

DECLARE @cols AS NVARCHAR(MAX), @sql AS NVARCHAR(MAX);

WITH GS AS(
SELECT LastN, Age, QUOTENAME(Row_Number() OVER(PARTITION BY Age ORDER BY Age, LastN)) As GrpSeq FROM Umpires),
DS AS
(SELECT DISTINCT GrpSeq FROM GS),
CS AS
(SELECT STRING_AGG(GrpSeq,',') AS G FROM DS)

SELECT @cols = G FROM CS

SELECT @sql = 'SELECT *
            FROM (SELECT LastN, Age, Row_Number() OVER(PARTITION BY Age ORDER BY Age, LastN) As GrpSeq FROM Umpires) AS Q
            PIVOT(Min(LastN) FOR GrpSeq IN(' + @cols + ')) AS P;';

EXEC sp_executesql @sql;

Produces output:

Age	1	2	3	4
A	J
J	H	K	M	N
S	I	L

I would like to produce:

Age	Name1	Name2	Name3	Name4
A	J, C
J	H, A	K, D	M, F	N, G
S	I, B	L, E

Attempt at simple concatenation of fields in inner SELECT and referencing that alias field in PIVOT for building @sql string generated error:

select statement that assigns a value to a variable must not be combined with data-retrieval operations

Attempts at concatenating 'Name' string just triggers:

Incorrect syntax near 'Name'

As side note, this is easily accomplished in Access query, although might be horribly slow with very large dataset because of DCount() domain aggregate function:

TRANSFORM Max([LastN] & ", " & [FirstN]) AS FullName
SELECT Umpires.Age
FROM Umpires
GROUP BY Umpires.Age
PIVOT "Name" & DCount("*","Umpires","Age='" & [Age] & "' AND UmpID<=" & [UmpID]);

I can also produce in a static SQLServer pivot with known number of column header values:

WITH DS AS(
SELECT Age, LastN + ', ' + FirstN AS FN, 'Name' + CAST(Row_Number() OVER(PARTITION BY Age ORDER BY Age, LastN) AS VARCHAR) As GrpSeq FROM Umpires)

SELECT * FROM (SELECT Age, FN, GrpSeq FROM DS) AS Q PIVOT(Max(FN) FOR GrpSeq IN([Name1],[Name2],[Name3],[Name4])) P;

Solution

Something like this mayhaps:

DECLARE @cols AS NVARCHAR(MAX), @cols2 NVARCHAR(MAX), @sql AS NVARCHAR(MAX);

SELECT  *
INTO    #data
FROM    (
    VALUES  (1, N'A', N'H', N'J')
    ,   (2, N'B', N'I', N'S')
    ,   (3, N'C', N'J', N'A')
    ,   (4, N'D', N'K', N'J')
    ,   (5, N'E', N'L', N'S')
    ,   (6, N'F', N'M', N'J')
    ,   (7, N'G', N'N', N'J')
) t (UmpID,FirstN,LastN,Age)

;WITH GS AS(
    SELECT  LastN, Age
    ,   Row_Number() OVER(PARTITION BY Age ORDER BY Age, LastN) AS GrpSeq
    FROM    #data
    )
,DS AS (
    SELECT  DISTINCT GrpSeq
    FROM    GS
    )
,CS AS (
    SELECT  STRING_AGG(QUOTENAME(GrpSeq),',') WITHIN GROUP(ORDER BY GrpSeq) AS G
    ,   STRING_AGG(CONCAT(QUOTENAME(GrpSeq), ' AS Name', GrpSeq),',') WITHIN GROUP(ORDER BY GrpSeq) AS G2
    FROM    DS
    )
SELECT  @cols = G
,   @cols2 = G2
FROM    CS

SELECT  @sql = 'SELECT Age, ' + @cols2 + N'
            FROM (SELECT CONCAT(LastN, '','', FirstN) AS LastN, Age, Row_Number() OVER(PARTITION BY Age ORDER BY Age, LastN) As GrpSeq FROM #data) AS Q
            PIVOT(Min(LastN) FOR GrpSeq IN(' + @cols + ')) AS P;';

EXEC    sp_executesql @sql;

I created a second variable called @cols2 that does the [1] AS Name1, ... [N] AS NameN select string, concated CONCAT(LastN, '','', FirstN) to create first + last name combo, and then did what you already did.

I also added WITHIN GROUP thing to STRING_AGG to ensure correctly ordered columns.

The conditional aggregation version (some code incorporated from above):

;WITH GS AS(
    SELECT  LastN, Age
    ,   Row_Number() OVER(PARTITION BY Age ORDER BY Age, LastN) AS GrpSeq
    FROM    #data
    )
,DS AS (
    SELECT  DISTINCT GrpSeq
    FROM    GS
    )
,CS AS (
    SELECT  STRING_AGG(CONCAT('MAX(CASE WHEN GrpSeq = ', GrpSeq, ' THEN LastN END)', ' AS Name', GrpSeq),',') WITHIN GROUP(ORDER BY GrpSeq) AS G3
    FROM    DS
    )
SELECT  @cols = G3
FROM    CS

SELECT  @sql = '
   SELECT Age, ' + @cols + N'
   FROM (SELECT CONCAT(LastN, '','', FirstN) AS LastN, Age, Row_Number() OVER(PARTITION BY Age ORDER BY Age, LastN) As GrpSeq FROM #data) AS Q
   GROUP BY Age
'