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;