SQL Select Group by and String Concat

How to use GROUP BY to concatenate strings in SQL Server?

No CURSOR, WHILE loop, or User-Defined Function needed.

Just need to be creative with FOR XML and PATH.

[Note: This solution only works on SQL 2005 and later. Original question didn't specify the version in use.]

CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)

INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)

SELECT 
  [ID],
  STUFF((
    SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) 
    FROM #YourTable 
    WHERE (ID = Results.ID) 
    FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
  ,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID

DROP TABLE #YourTable

How to use GROUP BY to concatenate strings in MySQL?

SELECT id, GROUP_CONCAT(name SEPARATOR ' ') FROM table GROUP BY id;

https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat

From the link above, GROUP_CONCAT: This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values.

Sql select group by and string concat

this will work with sql-server 2008

SELECT p1.ID,
       ( SELECT NAME + ' and ' 
           FROM YourTable  p2
          WHERE p2.ID = p1.ID
          ORDER BY NAME
            FOR XML PATH('') ) AS Name,
        sum(Amount)
      FROM YourTable p1
      GROUP BY ID ;

Concatenate many rows into a single text string with grouping

try this -

SELECT DISTINCT
      fileid
    , STUFF((
        SELECT N', ' + CAST([filename] AS VARCHAR(255))
        FROM tblFile f2
        WHERE f1.fileid = f2.fileid ---- string with grouping by fileid
        FOR XML PATH (''), TYPE), 1, 2, '') AS FileNameString
FROM tblFile f1

MS SQL - group by concat string

In sql server you can use FOR XML PATH

select  SubscriberId,
categoriesAdded=Stuff((SELECT ',' + CAST(CategoryId as VARCHAR(255)) FROM catsToAdd t1 WHERE t1.SubscriberId=@categoriesToAdd.SubscriberId
 FOR XML PATH (''))
             , 1, 1, '' )
from @categoriesToAdd as catsToAdd
GROUP BY SubscriberId

How to concatenate strings of a string field in a PostgreSQL 'group by' query?

PostgreSQL 9.0 or later:

Modern Postgres (since 2010) has the string_agg(expression, delimiter) function which will do exactly what the asker was looking for:

SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;

Postgres 9 also added the ability to specify an ORDER BY clause in any aggregate expression; otherwise you have to order all your results or deal with an undefined order. So you can now write:

SELECT company_id, string_agg(employee, ', ' ORDER BY employee)
FROM mytable
GROUP BY company_id;

PostgreSQL 8.4.x:

PostgreSQL 8.4 (in 2009) introduced the aggregate function array_agg(expression) which collects the values in an array. Then array_to_string() can be used to give the desired result:

SELECT company_id, array_to_string(array_agg(employee), ', ')
FROM mytable
GROUP BY company_id;

PostgreSQL 8.3.x and older:

When this question was originally posed, there was no built-in aggregate function to concatenate strings. The simplest custom implementation (suggested by Vajda Gabo in this mailing list post, among many others) is to use the built-in textcat function (which lies behind the || operator):

CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);

Here is the CREATE AGGREGATE documentation.

This simply glues all the strings together, with no separator. In order to get a ", " inserted in between them without having it at the end, you might want to make your own concatenation function and substitute it for the "textcat" above. Here is one I put together and tested on 8.3.12:

CREATE FUNCTION commacat(acc text, instr text) RETURNS text AS $$
  BEGIN
    IF acc IS NULL OR acc = '' THEN
      RETURN instr;
    ELSE
      RETURN acc || ', ' || instr;
    END IF;
  END;
$$ LANGUAGE plpgsql;

This version will output a comma even if the value in the row is null or empty, so you get output like this:

a, b, c, , e, , g

If you would prefer to remove extra commas to output this:

a, b, c, e, g

Then add an ELSIF check to the function like this:

CREATE FUNCTION commacat_ignore_nulls(acc text, instr text) RETURNS text AS $$
  BEGIN
    IF acc IS NULL OR acc = '' THEN
      RETURN instr;
    ELSIF instr IS NULL OR instr = '' THEN
      RETURN acc;
    ELSE
      RETURN acc || ', ' || instr;
    END IF;
  END;
$$ LANGUAGE plpgsql;

How to use GROUP BY to concatenate strings while joining multiple tables?

You can specify a CTE – common table expression to store your temporary result :

with cteTbl ( CardID
            , TechName
            , problemReported ) as ( 
select j.CardID
     , p.ProblemReported
     , ( select TechnicianName
         from easy_tbltechnicianMaster
         where TechnicianID =  t.technicianID ) as TechName
from easy_tbljobcard as j 
join easy_technician as t on t.CardID = j.CardID  
left join easy_tblproblem as p  on p.CardID = t.CardID )

And then select from it and concatenate all column values with the same t.techName and t.CardID in one row with for xml path('') and after that replace the first comma , with stuff:

select t.CardID
     , t.TechName
     , stuff( ( select ', ' + ProblemReported
                from cteTbl
                where TechName = t.TechName
                order by ProblemReported
                for xml path('') ), 1, 1, '') AS ProblemReported
from cteTbl t
group by t.TechName
       , t.CardID

SQLFiddle

---

Related Topics