learner999 learner999 - 1 month ago 6
SQL Question

SQL query to merge the data based on boolean column

I am trying to merge the data based on a Boolean column

Original data:

FName LName Status
------------------
x y 1
x y 0
a b 1
t t 1
u e 1
t t 1
a b 0


End result I want to achieve

FName LName Yes No
-------------------
x y 1 1
a b 1 1
t t 2 0
u e 1 0


This is what I tried but it didn't get me what I wanted -

SELECT
[FirstName], [LastName], COUNT(*) AS No
FROM
[dbo].[tablename]
GROUP BY
[FirstName], [LastName], [Status]
HAVING
COUNT(*) > 1 AND Status = 0

UNION

SELECT
[FirstName], [LastName], COUNT(*) AS Yes
FROM
[dbo].[tablename]
GROUP BY
[FirstName], [LastName], [Status]
HAVING
COUNT(*) > 1 AND Status = 1


I am sure there must be an easy way to achieve this

Answer

You just need SUM and a CASE expression:

SELECT  FName,
        LName,
        SUM(CASE WHEN [Status] = 1 THEN 1 ELSE 0 END) Yes,
        SUM(CASE WHEN [Status] = 0 THEN 1 ELSE 0 END) No
FROM YourTable
GROUP BY FName,
         LName;