--创建测试表

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TB]') AND type in (N'U'))

DROP TABLE [dbo].[TB1]

GO

CREATE TABLE [dbo].[TB1](

    [卡状态] [nvarchar] () NULL,

    [卡类型] [nvarchar]() NULL,

) ON [PRIMARY]

GO

--插入测试数据

INSERT INTO [TB1] ([卡状态],[卡类型])

    SELECT N'发卡',N'正式卡' UNION ALL

    SELECT N'发卡',N'正式卡' UNION ALL

    SELECT N'发卡',N'临式卡' UNION ALL

    SELECT N'制卡',N'临式卡' UNION ALL

    SELECT N'制卡',N'正式卡'

GO

SELECT * FROM TB1

SELECT  卡状态 + '数' ,

        COUNT() AS 总数 ,

        SUM(CASE WHEN 卡类型 = '正式卡' THEN

                 ELSE

            END) AS 正式卡 ,

        SUM(CASE WHEN 卡类型 = '临式卡' THEN

                 ELSE

            END) AS 临式卡

FROM    TB1

GROUP BY 卡状态