行转列多个分组字段

__lhy 2015-01-09 11:04:17

CREATE TABLE [dbo].[tb](

    [Id] [int] IDENTITY(1,1) NOT NULL,

    [UserName] [nvarchar](50) NULL,

    [Sex] bit NULL,

    [Subject] [nvarchar](50) NULL,

    [Source] [numeric](18, 0) NULL

) ON [PRIMARY]

测试数据

INSERT INTO [tb] ([UserName],[Subject],[Source]) 

    SELECT N'张三',0,N'语文',60  UNION ALL

    SELECT N'李四',0,N'数学',70  UNION ALL

    SELECT N'王五',1,N'英语',80  UNION ALL

    SELECT N'王五',0,N'数学',75  UNION ALL

    SELECT N'王五',1,N'语文',57  UNION ALL

    SELECT N'李四',0,N'语文',80  UNION ALL

    SELECT N'张三',0,N'英语',100

经典的行转列

SELECT [UserName],

SUM(CASE [Subject] WHEN '数学' THEN [Source] ELSE 0 END) AS '[数学]',

SUM(CASE [Subject] WHEN '英语' THEN [Source] ELSE 0 END) AS '[英语]',

SUM(CASE [Subject] WHEN '语文' THEN [Source] ELSE 0 END) AS '[语文]'     

FROM [tb]

GROUP BY [UserName]

但是我需要多条分组字段应该怎么查询
查询效果：
Id Name Sex 数学语文英语
1 张三 0 0 60 100
..

...全文

215 1 打赏收藏转发到动态举报

写回复

用AI写文章

1 条回复

切换为时间正序

请发表友善的回复…

发表回复

还在加载中灬 2015-01-09

打赏
举报

SELECT [id],[UserName],[sex],
SUM(CASE [Subject] WHEN '数学' THEN [Source] ELSE 0 END) AS '[数学]',
SUM(CASE [Subject] WHEN '英语' THEN [Source] ELSE 0 END) AS '[英语]',
SUM(CASE [Subject] WHEN '语文' THEN [Source] ELSE 0 END) AS '[语文]'     
FROM [tb]
GROUP BY [id],[UserName],[sex]

放到GROUP BY 那边去