Multiple Rows into a Single Row and Combine Column SQL

SQL Server: combining multiple rows into one row

There are several methods.

If you want just the consolidated string value returned, this is a good quick and easy approach

DECLARE @combinedString VARCHAR(MAX)
SELECT @combinedString = COALESCE(@combinedString + ', ', '') + stringvalue
FROM jira.customfieldValue
WHERE customfield = 12534
    AND ISSUE = 19602

SELECT @combinedString as StringValue 

Which will return your combined string.

You can also try one of the XML methods e.g.

SELECT DISTINCT Issue, Customfield, StringValues
FROM Jira.customfieldvalue v1
CROSS APPLY ( SELECT StringValues + ',' 
              FROM jira.customfieldvalue v2
              WHERE v2.Customfield = v1.Customfield 
                  AND v2.Issue = v1.issue 
              ORDER BY ID 
                  FOR XML PATH('') )  D ( StringValues )
WHERE customfield = 12534
    AND ISSUE = 19602

Combine multiple rows into single row in SQL View (without group by or with CTE?)

Your "magic function" would appear to be STRING_AGG().

The code would then look like this:

CREATE VIEW MyView AS 
    SELECT Staff.Id,
           STRING_AGG(CONCAT(OtherStaff.Name, ', ', OtherStaff.Value), '; ')  WITHIN GROUP (ORDER BY OtherStaff.Name),
           SomeTable.SomeVal   
    FROM dbo.[Staff] Staff JOIN
         dbo.[OtherStaff] OtherStaff
         ON OtherStaff.StaffId = Staff.Id JOIN
         dbo.[SomeTable] SomeTable
         ON SomeTable.StaffId = Staff.Id
    GROUP BY Staff.Id, SomeTable.SomeVal;

Listing unaggregated columns in both the GROUP BY and SELECT should not be too troublesome. After all, you can just cut-and-paste them.

How to concatenate text from multiple rows into a single text string in SQL Server

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH (''), TYPE
            ).value('text()[1]','nvarchar(max)') [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2

SQL: Combine multiple rows into a single row with more columns

This would be the ideal way to do it

SELECT *
FROM TableName
PIVOT(MAX([Feedback value])
      FOR [Feedback type] IN ([Overall rating], [Duration], [Representative])
     ) AS PVTTable

However, user is not able to use pivot, so this will work as well

SELECT [ID], 
       MAX(CASE WHEN [Feedback type] = 'Overall rating' 
                THEN [Feedback value] 
                ELSE '' END) AS 'Overall Rating',
       MAX(CASE WHEN [Feedback type] = 'Duration' 
                THEN [Feedback value] 
                ELSE '' END) AS 'Duration',
       MAX(CASE WHEN [Feedback type] = 'Representative' 
                THEN [Feedback value] 
                ELSE '' END) AS 'Representative'
FROM TableName
GROUP BY [ID]

Combine Multiple Rows And Columns Into A Single Row In SQL

Try the following:

SELECT
  OrderID,
  Max(XboxLive)
  Max(iTunes),
  Max(XboxDate),
  Max(iTunesDate)
  Serial
FROM SampleTable
Group by
  OrderID,
  Serial

SQL Server: Combine multiple rows into one row from a join table?

Use a GROUP BY query with STRING_AGG:

SELECT
    t1.ID,
    t1.Data1,
    STRING_AGG(t2.DataFromTable2, ',') AS data
FROM Table1 t1
LEFT JOIN Table2 t2
    ON t1.ID = t2.ID
GROUP BY
    t1.ID,
    t1.Data1
WHERE
    t1.ID = 1;

I am assuming you are using SQL Server 2017 or later. For earlier versions of SQL Server:

SELECT
    t1.ID,
    t1.Data1,
    data = STUFF((
      SELECT ',' + t2.DataFromTable2
      FROM Table2 t2
      WHERE t2.ID = t1.ID
      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM Table1 t1;

combine multiple rows into one row based on column value

I believe this article contains solutions to your question -

SQL Query to concatenate column values from multiple rows in Oracle

---

Related Topics