CODEWITHSUNDEEP
sql-servert-sql
John
John

Reputation: 273

merge multiple rows to one in sql

I have the following result set:

ID          P1Score P2Score P3Score
===================================
22117617    NULL    50      NULL
22117617    1       NULL    NULL    
22117617    NULL    NULL    40

What I want to do is, merge these rows into one.

I want to place value of P1Score in the P1score column, same with P2score and P3score.

How would one achieve this?

Upvotes: 1

Views: 61

Answers (3)

gofr1
gofr1

Reputation: 15977

If you are using SQL Server 2012 or up, you can use MAX with OVER:

SELECT DISTINCT ID,
                MAX(P1Score) OVER (ORDER BY ID) P1Score,
                MAX(P2Score) OVER (ORDER BY ID) P2Score,
                MAX(P3Score) OVER (ORDER BY ID) P3Score
FROM YourTable

Output:

ID          P1Score P2Score P3Score
22117617    1       50      40

Or even pivoting:

SELECT *
FROM YourTable
UNPIVOT (
    [Values] FOR PScores IN (P1Score, P2Score, P3Score)
) unp
PIVOT (
    MAX([Values]) FOR PScores IN (P1Score, P2Score, P3Score)
) piv

Upvotes: 1

StackUser
StackUser

Reputation: 5398

You can directly use group by with sum() function since suppose if you have multiple scores for single Id then sum would be appropriate.

SELECT ID
    ,sum(P1Score) AS P1Score
    ,sum(P2Score) AS P2Score
    ,sum(P3Score) AS P3Score
FROM [Score]
GROUP BY ID

Upvotes: 1

Yan Katia
Yan Katia

Reputation: 46

select ID,
max(ISNULL(P1Score,0)),
max(ISNULL(P2Score,0)),
max(ISNULL(P3Score,0))
from [Score] group by ID

Upvotes: 3

Related Questions