Multiple Case Statements to one row

Question

In my SQL Server database one customer could have many products.

When casing using this:

CASE
    WHEN br.Ptype# = 'LE' THEN 'Y'
    ELSE 'N'
END AS [Legal],
CASE
    WHEN br.Ptype# = 'BR' THEN 'Y'
    ELSE 'N'
END AS [BR],
CASE
    WHEN br.Ptype# = 'WS' THEN 'Y'
    ELSE 'N'
END AS [Screen],
CASE
    WHEN br.Ptype# = 'TW' THEN 'Y'
    ELSE 'N'
END AS [Van]

It returns:

Title   FirstName   LastName  Email                 Legal  BR  Screen  Van
Mr      Aaaa        Aaaa      test.email@test.com   N      Y   N       N
Mr      Aaaa        Aaaa      test.email@test.com   Y      N   N       N

How can I get the returned results to be a single row with the Legal as Y and the BR as Y as this customer has these 2 policy types? As I know this customer has 2 insurance types from my specified 4 I'm searching for I'd like it to return the results like:

Title  FirstName  LastName  Email               Legal  BR  Screen  Van
Mr     Aaaa       Aaaa      test.email@test.com Y      Y   N       N

Thanks in advance for any help.

SELECT DISTINCT --TOP 1000
dbo.PARSE_NAME_UDF(yy.Name#,'H') AS [Title],
dbo.PARSE_NAME_UDF(yy.Name#,'F') AS [FirstName],  
dbo.PARSE_NAME_UDF(yy.Name#,'L') AS [LastName],
yy.Email# AS [Email],
--br.PolRef@ AS [PolicyReference],
--bc.Datecreated# AS [Date Sold],
--br.Idat# AS [PolicyStartDate],
CASE
WHEN br.Ptype# = 'LE' THEN 'Y'
ELSE 'N'
END AS [Legal],
CASE
WHEN br.Ptype# = 'BR' THEN 'Y'
ELSE 'N'
END AS [BR],
CASE
WHEN br.Ptype# = 'WS' THEN 'Y'
ELSE 'N'
END AS [Screen],
CASE
WHEN br.Ptype# = 'TW' THEN 'Y'
ELSE 'N'
END AS [Van]

FROM brpolicy AS br
LEFT JOIN yyclient AS yy ON br.Ref@ = yy.Ref@ AND br.B@ = yy.B@
LEFT JOIN brcledger AS bc ON br.PolRef@ = bc.PolRef@ AND br.B@ = bc.B@

WHERE
br.Ref@ = 'AAAA06'
AND (br.Ptype# = 'WS'
OR br.Ptype# = 'LE'
OR br.Ptype# = 'BR'
OR br.Ptype# = 'TW'
)

Please ignore my br.Ref@ from the where clause as that's my test customer record

You might want to filter for Legal='Y' OR BR='Y'. A distinct select where you do NOT include these columns could be what you want... — Shnugo, Jul 14 '15 at 14:25

score 1 · Answer 1 · answered Jul 14 '15 at 14:15

You can use MAX:

SELECT  Title,
        FirstName,
        LastName,
        MAX(CASE
                WHEN br.Ptype# = 'LE' THEN 'Y'
                ELSE 'N'
            END) AS [Legal],
        MAX(CASE
                WHEN br.Ptype# = 'BR' THEN 'Y'
                ELSE 'N'
            END) AS [BR],
        CASE
            WHEN br.Ptype# = 'WS' THEN 'Y'
            ELSE 'N'
        END AS [Screen],
        MAX(CASE
                WHEN br.Ptype# = 'TW' THEN 'Y'
                ELSE 'N'
            END) AS [Van]
FROM dbo.YourTable
GROUP BY Title,
         FirstName,
         LastName

score -1 · Answer 2 · answered Jul 14 '15 at 14:26

Try this one

Select * from (SELECT  Title,
        FirstName,
        LastName,
        CASE
            WHEN br.Ptype# = 'LE' THEN 'Y'
            ELSE 'N'
        END AS [Legal],
        CASE
            WHEN br.Ptype# = 'BR' THEN 'Y'
            ELSE 'N'
        END AS [BR],
        CASE
            WHEN br.Ptype# = 'WS' THEN 'Y'
            ELSE 'N'
        END AS [Screen],
        CASE
            WHEN br.Ptype# = 'TW' THEN 'Y'
            ELSE 'N'
        END AS [Van]
FROM dbo.YourTable
GROUP BY Title,
         FirstName,
         LastName) as tProduct where Legal = 'Y' and BR = 'Y'

Multiple Case Statements to one row

2 Answers2