Home / Questions / 262580
Accepted
Alberto Ortega
Asked: 2020-05-11 09:37:02 +0800 CST 2020-05-11 09:37:02 +0800 CST

Does anyone know how I can group this query in sql server?

  • 772

Hello everybody and thanks in advance.

I am doing a query on a pivot table that contains the relationship between two tables.

enter image description here

this result is caused by this query.

SELECT
    UserID,
    CASE 
        WHEN RespuestaID = 1 THEN Texto
    END ,
    CASE 
        WHEN RespuestaID = 2 THEN Texto
    end 
FROM Respuestas
WHERE RespuestaID in (1,2) AND UserID < 5

The result I expect is this.

enter image description here

In summary. What I'm trying to do is group by the UserID. But I don't know how to do it since in SQLserver it doesn't allow you to group by a single field.

The second query was made with multiple joins to the same table. But I want a more efficient query since the one I do now is very slow.

This is the example of the query that I do to get the grouped data.

SELECT 
    t1.UserID,
    t1.Texto as field1,
    t2.Texto as field2
FROM users u
JOIN RespuestasContactos t1
ON t1.UserID = u.id AND t1.RespuestaID = 1
JOIN RespuestasContactos t2
ON t2.UserID = u.id AND t2.RespuestaID = 2
WHERE u.id < 5
sql

1 Answers

  1. Best Answer

    Use an aggregate function. It can be MAX() or MIN()

    SELECT 
    t1.UserID,
    MAX( t1.Texto) as field1,
    MAX( t2.Texto) as field2
FROM users u
JOIN RespuestasContactos t1
ON t1.UserID = u.id AND t1.RespuestaID = 1
JOIN RespuestasContactos t2
ON t2.UserID = u.id AND t2.RespuestaID = 2
WHERE u.id < 5
GROUP BY t1.UserID;

    Better still would be to do the JOIN only once.

    SELECT 
    rc.UserID,
    MAX( CASE WHEN rc.RespuestaID = 1 THEN rc.Texto END) as field1,
    MAX( CASE WHEN rc.RespuestaID = 2 THEN rc.Texto END) as field2
FROM users u
JOIN RespuestasContactos rc
ON rc.UserID = u.id 
WHERE u.id < 5
GROUP BY rc.UserID;
    • 8