This query populate result for SQL server login with role by column.
WITH cte_srm AS
(
SELECT
srm.member_principal_id
,MAX(CASE WHEN srm.role_principal_id = 3 THEN 1 END) AS sysadmin
,MAX(CASE WHEN srm.role_principal_id = 4 THEN 1 END) AS securityadmin
,MAX(CASE WHEN srm.role_principal_id = 5 THEN 1 END) AS serveradmin
,MAX(CASE WHEN srm.role_principal_id = 6 THEN 1 END) AS setupadmin
,MAX(CASE WHEN srm.role_principal_id = 7 THEN 1 END) AS processadmin
,MAX(CASE WHEN srm.role_principal_id = 8 THEN 1 END) AS diskadmin
,MAX(CASE WHEN srm.role_principal_id = 9 THEN 1 END) AS dbcreator
,MAX(CASE WHEN srm.role_principal_id = 10 THEN 1 END) AS bulkadmin
FROM sys.server_principals AS sp
JOIN sys.server_role_members AS srm ON sp.principal_id = srm.role_principal_id
WHERE sp.[type] = 'R'
GROUP BY srm.member_principal_id
)
SELECT
pr.[sid]
,CAST(NULL AS SMALLINT) AS [status]
,pr.create_date
,pr.modify_date AS updatedate
,pr.create_date AS accdate
,0 AS totcpu
,0 AS totio
,0 AS spacelimit
,0 AS timelimit
,0 AS resultlimit
,pr.[name]
,pr.default_database_name AS dbname
,CAST(NULL AS SYSNAME) [password]
,pr.default_language_name AS [language]
,CAST(CASE WHEN pe.state = 'D' THEN 1 ELSE 0 END AS INT) AS denylogin
,CAST(CASE WHEN pe.state = 'G' THEN 1 ELSE 0 END AS INT) AS hasaccess
,CAST(CASE WHEN pr.[type] in ('U','G') THEN 1 ELSE 0 END AS INT) AS isntname
,CAST(CASE WHEN pr.[type] = 'G' THEN 1 ELSE 0 END AS INT) AS isntgroup
,CAST(CASE WHEN pr.[type] = 'U' THEN 1 ELSE 0 END AS INT) AS isntuser
,ISNULL(cte_srm.sysadmin, 0) AS sysadmin
,ISNULL(cte_srm.securityadmin, 0) AS securityadmin
,ISNULL(cte_srm.serveradmin, 0) AS serveradmin
,ISNULL(cte_srm.setupadmin, 0) AS setupadmin
,ISNULL(cte_srm.processadmin, 0) AS processadmin
,ISNULL(cte_srm.diskadmin, 0) AS diskadmin
,ISNULL(cte_srm.dbcreator, 0) AS dbcreator
,ISNULL(cte_srm.bulkadmin, 0) AS bulkadmin
,pr.[name] AS loginname
FROM sys.server_principals AS pr
LEFT OUTER JOIN sys.server_permissions AS pe ON pr.principal_id = pe.grantee_principal_id AND pe.[type] = 'COSQ'
LEFT OUTER JOIN cte_srm ON pr.principal_id = cte_srm.principal_id
WHERE pr.[type] <> 'R'
GO
