Combine multiple columns from database into one column?

Question

I have written the following SQL query:

SELECT DISTINCT i.IncidentReference Reference, s.Name Site, it.Description IncidentTypes
    , i.Description, FORMAT(i.CreatedOn,'d MMM yyyy', 'en-US' ) Logged 
FROM Incident i 
INNER JOIN TEST_USWM_TestAutomation.TEST_USWM.uvw_AvailaibleSites s ON i.SiteId = s.Id 
JOIN IncidentIncidentType iit ON i.Id = iit.IncidentId 
JOIN IncidentType it ON iit.IncidentTypeId = it.Id 
JOIN IncidentStatus ins ON i.IncidentStatusId = ins.Id 
WHERE s.Id = 1 /*:siteId */
AND ins.Name = 'Logged' 
AND i.CreatedOn BETWEEN DATEADD(DAY, -30, GETUTCDATE()) AND GETUTCDATE()

Which returns the following results:

Reference  Site  IncidentTypes                 Description    Logged
7032       0110  Refrigeration Outage          njfdgdf        25 Sep 2019
7095       0110  Fire/False Evacuation         testing        2 Oct 2019
7096       0110  Fire/False Evacuation         testing        2 Oct 2019
7097       0110  Fire/False Evacuation         test           2 Oct 2019
7097       0110  Flood                         test           2 Oct 2019
7097       0110  Gas Leak/Suspected Gas Leak   test           2 Oct 2019
7098       0110  Flood                         testing        2 Oct 2019
7168       0110  Fire/False Evacuation         test           7 Oct 2019
7330       0110  Refrigeration Outage          Test           15 Oct 2019

However, I am looking to combine all rows with the same Reference into one row. So for example, for Reference - 7097, I would want to see the column IncidentTypes look like - Gas Leak/Suspected Gas Leak, Fire/False Evacuation, Flood.

What would be the best way to do this?

EDIT: I have tried the query that @GMB posted below, but I am getting the error - The function 'STRING_AGG' may not have a WITHIN GROUP clause. This seems to be an issue with the version of SQL Server that I am using. Is there another way to get around this issue without upgrading the version ?

EDIT: Output for @Gordon Linoff's query -

Just a note, but you know that JOIN is the same as INNER JOIN? — jarlh
– jarlh, Commented Oct 22, 2019 at 9:16
Well you could use a groub by clause and appropriate aggregate functions for the columns (e.g. concatenate strings). What exactly you could use would depend on the database you're using though, because they all support different aggregate functions (there's a common set but most have their own additions). — Thomas
– Thomas, Commented Oct 22, 2019 at 9:20
There is no standard SQL syntax for this, so we need to know which database you're using, to see if that database has support for it. If you need generic cross-database code for this, you need to do it in Java code. — Andreas
– Andreas, Commented Oct 22, 2019 at 9:20
@Andreas: actually there is: listagg() is part of the SQL standard. — user330315
– user330315, Commented Oct 22, 2019 at 9:21

Gordon Linoff · Accepted Answer · 2019-10-22 11:07:10Z

1

In older versions of SQL Server, you can use:

SELECT i.IncidentReference as Reference, 
       s.Name as Site, 
       STUFF( (SELECT it.Description + ','
               FROM IncidentType it
               WHERE iit.IncidentTypeId = it.Id 
               ORDER BY it.Description
               FOR XML PATH ('')
              ), 1, 2, ''
             ) as IncidentTypes,
      i.Description,
      FORMAT(i.CreatedOn,'d MMM yyyy', 'en-US' ) as Logged 
FROM Incident i JOIN
     TEST_USWM_TestAutomation.TEST_USWM.uvw_AvailaibleSites s 
     ON i.SiteId = s.Id JOIN
     IncidentStatus ins
     ON i.IncidentStatusId = ins.Id 
WHERE s.Id = 1 /*:siteId */ AND
      ins.Name = 'Logged' AND
      i.CreatedOn BETWEEN DATEADD(DAY, -30, GETUTCDATE()) AND GETUTCDATE();

I don't think the outer GROUP BY is needed.

answered Oct 22, 2019 at 11:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jamiemca Over a year ago

This query has ordered the values correctly, but has not combined them into one single row. Please see output above

Bilal Siddiqui · Accepted Answer · 2019-10-22 09:22:28Z

1

In MySql you can use group_concat(it.Description) and then group by on reference

SELECT DISTINCT i.IncidentReference Reference, s.Name Site, group_concat(it.Description) IncidentTypes, 
i.Description, FORMAT(i.CreatedOn,'d MMM yyyy', 'en-US' ) Logged 
FROM Incident i 
INNER JOIN TEST_USWM_TestAutomation.TEST_USWM.uvw_AvailaibleSites s ON i.SiteId = s.Id 
JOIN IncidentIncidentType iit ON i.Id = iit.IncidentId 
JOIN IncidentType it ON iit.IncidentTypeId = it.Id 
JOIN IncidentStatus ins ON i.IncidentStatusId = ins.Id 
WHERE s.Id = 1 /*:siteId */
AND ins.Name = 'Logged' 
AND i.CreatedOn BETWEEN DATEADD(DAY, -30, GETUTCDATE()) AND GETUTCDATE() Group by reference;

answered Oct 22, 2019 at 9:22

Bilal Siddiqui

3,6491 gold badge16 silver badges22 bronze badges

Comments

GMB · Accepted Answer · 2019-10-22 09:26:38Z

0

You would need to use aggregation.

The use of GETUTCDATE() tend to indicate that you are using SQL Server. In this RDBMS, you can use function STRING_AGG() to aggregate the descriptions, as follows:

SELECT 
    i.IncidentReference Reference, 
    s.Name Site, 
    STRING_AGG(i.Description, ',') WITHIN GROUP(ORDER BY i.Description) IncidentTypes,
    i.Description,
    FORMAT(i.CreatedOn,'d MMM yyyy', 'en-US' ) Logged 
FROM 
    Incident i 
    JOIN TEST_USWM_TestAutomation.TEST_USWM.uvw_AvailaibleSites s ON i.SiteId = s.Id 
    JOIN IncidentIncidentType iit ON i.Id = iit.IncidentId 
    JOIN IncidentType it ON iit.IncidentTypeId = it.Id 
    JOIN IncidentStatus ins ON i.IncidentStatusId = ins.Id 
WHERE 
    s.Id = 1 /*:siteId */
    AND ins.Name = 'Logged' 
    AND i.CreatedOn BETWEEN DATEADD(DAY, -30, GETUTCDATE()) AND GETUTCDATE()
GROUP BY
    i.IncidentReference Reference, 
    s.Name Site, 
    i.Description,
    FORMAT(i.CreatedOn,'d MMM yyyy', 'en-US' ) Logged

answered Oct 22, 2019 at 9:26

GMB

224k25 gold badges103 silver badges151 bronze badges

1 Comment

jamiemca Over a year ago

When I try this query I am getting the following error - The function 'STRING_AGG' may not have a WITHIN GROUP clause.

Collectives™ on Stack Overflow

Combine multiple columns from database into one column?

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related