How to write SQL 'case' Command?

Question

I have a grid view, and It has type one and type two columns which is bind to type1 and type2.

Then I add a cheek box to it.

I want to bind the data, but my query data is duplicated.

This is my query

SELECT DISTINCT 
    userId, 
    MAX(CASE WHEN ApproverTypeID = 1 THEN ApproverTypeID END) AS Type1, 
    MAX(CASE WHEN ApproverTypeID = 2 THEN ApproverTypeID END) AS Type2
FROM         
    table1
GROUP BY 
    ApproverTypeID, userId

My original table looks like this..

Name        Type
---------
    Name1     1
    Name2     1
    Name1     0

But I want to get output like this...

Name       Type1    Type2
----------------
    Name1    1       0
    Name2    1       null

How can I do it?

Answer 1

You just need to remove ApproverTypeID of GROUP BY clause because ApproverTypeID is used in MAX() function. After that you can also remove excessive DISTINCT clause. And you will recieve what you want

SELECT userId, 
       MAX(CASE WHEN ApproverTypeID = 1 THEN ApproverTypeID END) AS Type1, 
       MAX(CASE WHEN ApproverTypeID = 2 THEN ApproverTypeID END) AS Type2
FROM table1
GROUP BY userId

Answer 2

since you didn't mentioned the database server you are using, the query below will work on almost all RDBMS (but not all)

SELECT  Name,
        MAX(CASE WHEN Type = 1 THEN Type END) Type1,
        MAX(CASE WHEN Type = 2 THEN Type END) Type2
FROM    tableName
GROUP   By Name

• SQLFiddle Demo