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!
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!
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!
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.