Combining Multiple Rows in SQL Server into One

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

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;

SQL Server : merge multiple rows into 1 row

I would try with aggregation:

select Id, 
       max(case when seq = 1 then code end) as code1,
       max(case when seq = 1 then name end) as name1,
       max(case when seq = 2 then code end) as code2,
       max(case when seq = 2 then name end) as name2,
       max(case when seq = 3 then code end) as code3,
       max(case when seq = 3 then name end) as name3
from (select d.*,
             row_number() over (partition by code order by order) as seq
      from data d
     ) d
group by Id;

EDIT : If you have a already sequence (i.e. Order) then only aggregation is enough :

select Id, 
       max(case when [order] = 1 then code end) as code1,
       max(case when [order] = 1 then name end) as name1,
       max(case when [order] = 2 then code end) as code2,
       max(case when [order] = 2 then name end) as name2,
       max(case when [order] = 3 then code end) as code3,
       max(case when [order] = 3 then name end) as name3
from data d
group by Id;

EDIT : Thanks to JohnLBevan for Demo.

Here is SQL Fiddle Example.

How to merge multiple rows into one row with filtering rules in SQL Server

You can try with conditional aggregation -

select MedicalCardId, 
       max(case when DiagnosisType='Main' then Symptom end) as MainSymptom,
       max(case when DiagnosisType='Secondary' and DiagnosisOrder=1 then Symptom end) as SecondarySymptom1,
       max(case when DiagnosisType='Secondary' and DiagnosisOrder=2 then Symptom end) as SecondarySymptom2,
       max(case when DiagnosisType='Secondary' and DiagnosisOrder=3 then Symptom end) as SecondarySymptom3
from tablename
group by MedicalCardId

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

Merging multiple rows with same column in SQL Server

Use This

SELECT category,segment,payment = STUFF((
                                SELECT '+' + CONVERT(VARCHAR,payment)
                                FROM TABLENAME t
                                WHERE t.category = TABLENAME.category AND t.segment = TABLENAME.segment                               
                                FOR XML PATH('')
                                ), 1, 1, '')
FROM TABLENAME 
GROUP BY category,segment

if you get payment with + sign then use stuff like this.

SELECT category,segment,SUM(payment) as payment
FROM TABLENAME 
GROUP BY category,segment

if you want sum then remove stuff and use Sum(payment) only.

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

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.

---

Related Topics