Can I PIVOT this without resorting to CASE statements?

Here's a nasty way using dynamic sql

declare @cols NVARCHAR(2000)
declare @formattedcols NVARCHAR(2000)

select @formattedcols = COALESCE(@formattedcols + ',isnull([' + Name + '],'''') as [' + Name + ']',
                         'isnull([' + Name + '],'''') as [' + Name + ']'),
       @cols = COALESCE(@cols + ',[' + Name + ']',
                         '[' + Name + ']')                  
from sys.database_principals
where
    type = 'R'

declare @qry as NVARCHAR(4000)

set @qry = '
select
    username, '+ @formattedcols +'
from
(
SELECT 
    dp1.name as Username,
    dp.name as Rolename,
    case when drm.role_principal_id is not null then ''X'' else '''' end as userID

FROM 
    sys.database_role_members [drm]
JOIN sys.database_principals [dp] ON drm.role_principal_id = dp.principal_id
full JOIN sys.database_principals [dp1] on drm.member_principal_id = dp1.principal_id
where    dp1.name not like ''db_%''
) as sourcetable
PIVOT
(
    max(userid)
FOR rolename in ('+ @cols +')
) as PIVOTTABLE
'

execute (@qry)

You might actually want to reconsider using the CASE statements of a classic Cross-tab. Please see the following article for why. link text

For a small study on how to quickly and accurately write dynamic cross-tabs, please see the following article. link text

You would have to know which roles you're concerned with before hand (which is always the case with PIVOT), but this should get you there:

SELECT [User], db_datareader, db_datawriter, db_owner
FROM (SELECT dp1.name [User],
    dp.name [Role]
FROM sys.database_role_members [drm]
JOIN sys.database_principals [dp] ON drm.role_principal_id = dp.principal_id
JOIN sys.database_principals [dp1] on drm.member_principal_id = dp1.principal_id ) as roles
PIVOT ( count(Role)
FOR Role
IN ( db_datareader
, db_datawriter
, db_owner ) ) as role_table