Turning rows into columns with a condition? SQL Server-CodePudding

I have table1 with the following data for example:

PersonID        Description        StartDate          EndDate
---------------------------------------------------------
ABC             Do not call        2000-05-10         NULL
ABC             Do not solicit     2005-10-15         NULL
XYZ             Do not email       2010-12-20         NULL
FGH             Do not contact     2020-10-11         2021-12-11

I am trying to turn each of the values in the Description column to be its own column and to show a "1" if the PersonID has this value in their row and as long as the EndDate is NULL for that Description, otherwise show "0" for example:

PersonID          Do Not Call       Do Not Solicit      Do not email        Do not contact
---------------------------------------------------------------------------------------------
ABC                   1                   1                 0                     0
XYZ                   0                   0                 1                     0
FGH                   0                   0                 0                     0

I've tried using PIVOT for example:

SELECT PersonID, [Do not call], [Do not call solicit], [Do not email], [Do not contact]
from table1 as SourceTable
PIVOT
(
COUNT(Description)
for Description IN ([Do not call], [Do not call solicit], [Do not email], [Do not contact])
) as PivotTable

But this gives me multiple rows of the same PersonID, whereas I just want one unique PersonID row, and then just value of 1 or 0 in the remaining columns of "Do not call", "Do not solicit", etc... Also with my query, I am unable to check whether the row has an EndDate or not, because if it does have an EndDate value, then I want it to display 0 instead of 1.

CodePudding user response：

Rather than a pivot I would choose to use conditional aggregation for this. Also, I would suggest not putting spaces in your column names as it just makes it harder to work with and provides no actual benefit.

select PersonID
    , DoNotCall = MAX(case when Description = 'Do Not Call' then 1 end)
    , DoNotSolicit = MAX(case when Description = 'Do Not Solicit' then 1 end)
    , DoNotEmail = MAX(case when Description = 'Do not email' then 1 end)
    , DoNotContact = MAX(case when Description = 'Do not contact' then 1 end)
from table1
group by PersonID
group by PersonID