Combine multiple SQL rows into a single row

Data and Databases
1

Hey everyone, I’m stuck with a SQL problem. I need to merge multiple rows into one. My current query looks like this:

SELECT e.FullName, pd.*, p1.Description AS OldPosition, p2.Description AS NewPosition, o.Description,
       STRING_AGG(pd.value, ',') AS combined_values
FROM assessment_main am
LEFT JOIN assessment_details pd ON am.AssessmentCode = pd.AssessmentMainCode
LEFT JOIN staff e ON am.EmployeeID = e.EmployeeID
LEFT JOIN PositionChanges pc ON am.AssessmentCode = pc.AssessmentCode
LEFT JOIN Positions p1 ON pc.PreviousPosition = p1.PositionID
LEFT JOIN Positions p2 ON pc.CurrentPosition = p2.PositionID
LEFT JOIN Branches o ON e.BranchCode = o.BranchCode

However, I’m encountering an error regarding ‘STRING_AGG’. I’m looking for a fix to combine the ‘value’ column into one row without triggering syntax issues. I ended up trying a PIVOT approach which seems to work. Check out my revised query:

SELECT * FROM 
(
    SELECT row_num, ad.Score, s.FullName, am.AssessmentID, b.Description 
    FROM assessment_main am 
    LEFT JOIN staff s ON am.EmployeeID = s.EmployeeID 
    LEFT JOIN branches b ON am.BranchID = b.BranchID
    LEFT JOIN (
        SELECT ROW_NUMBER() OVER(PARTITION BY AssessmentMainID ORDER BY DetailID DESC) AS row_num, 
               Score, AssessmentMainID 
        FROM assessment_details
    ) ad ON am.AssessmentID = ad.AssessmentMainID 
    WHERE am.BranchID LIKE '%%' AND Period LIKE '%%'
) scores 
PIVOT
(
    SUM(Score)
    FOR row_num IN ([1],[2],[3],[4],[5])
) AS pvt;

This updated query successfully aggregates the rows into a single row. Hope this helps anyone facing a similar issue!

2

yo, great job figuring out that pivot thing! thats pretty slick. have u thought about using a cte (common table expression) instead? might make it easier to read and tweak later. somethin like:

with numbered_scores as (
  select row_number() over(...) as rn, ...
  from assessment_details
)
select ...
from assessment_main am
join numbered_scores ns on ...

just an idea, might be worth a shot!

3

hmm, interesting solutions! have u considered using GROUP_CONCAT instead of STRING_AGG? it works in more sql flavors. like:

SELECT e.FullName, GROUP_CONCAT(pd.value SEPARATOR ',') AS combined_values
FROM assessment_main am
LEFT JOIN assessment_details pd ON am.AssessmentCode = pd.AssessmentMainCode
LEFT JOIN staff e ON am.EmployeeID = e.EmployeeID
GROUP BY e.FullName

what do u think? might be worth a try!

4

Great work on finding a solution with the PIVOT approach! It’s an effective way to handle row-to-column transformations in SQL. However, if you’re dealing with a variable number of rows or want a more flexible solution, you might consider using subqueries with MAX and CASE statements. This method can be more adaptable to different data scenarios:

SELECT e.FullName, am.AssessmentID, b.Description,
    MAX(CASE WHEN ad.row_num = 1 THEN ad.Score END) AS Score1,
    MAX(CASE WHEN ad.row_num = 2 THEN ad.Score END) AS Score2,
    MAX(CASE WHEN ad.row_num = 3 THEN ad.Score END) AS Score3,
    MAX(CASE WHEN ad.row_num = 4 THEN ad.Score END) AS Score4,
    MAX(CASE WHEN ad.row_num = 5 THEN ad.Score END) AS Score5
FROM assessment_main am
LEFT JOIN staff e ON am.EmployeeID = e.EmployeeID
LEFT JOIN branches b ON am.BranchID = b.BranchID
LEFT JOIN (
    SELECT ROW_NUMBER() OVER(PARTITION BY AssessmentMainID ORDER BY DetailID DESC) AS row_num,
           Score, AssessmentMainID
    FROM assessment_details
) ad ON am.AssessmentID = ad.AssessmentMainID
WHERE am.BranchID LIKE '%%' AND Period LIKE '%%'
GROUP BY e.FullName, am.AssessmentID, b.Description;

This approach offers similar results to your PIVOT solution but with added flexibility for future modifications.