请教一个group by问题

Question

当然可以，只是有点小麻烦罢了：
CREATE TABLE #Test
(
[Date] DATETIME,
UserName NVARCHAR(200),
[TYPE] NVARCHAR(100),
cash1 INT,
cash2 INT,
cash3 INT
)
GOINSERT #Test([Date],UserName,[TYPE],cash1,cash2,cash3)
SELECT '2014-01-16',N'Test',N'aa',0,1,2 UNION ALL
SELECT '2014-01-16',N'Test',N'aa',1,1,1 UNION ALL
SELECT '2014-01-16',N'Test',N'bb',2,3,4 UNION ALL
SELECT '2014-01-16',N'Test',N'bb',2,2,2
GOSELECT
[Date],UserName,
SUM(CASE WHEN [Type] = 'aa' THEN cash1 ELSE 0 END) aacash1,
SUM(CASE WHEN [Type] = 'aa' THEN cash2 ELSE 0 END) aacash2,
SUM(CASE WHEN [Type] = 'aa' THEN cash3 ELSE 0 END) aacash3,
SUM(CASE WHEN [Type] = 'bb' THEN cash1 ELSE 0 END) bbcash1,
SUM(CASE WHEN [Type] = 'bb' THEN cash2 ELSE 0 END) bbcash2,
SUM(CASE WHEN [Type] = 'bb' THEN cash3 ELSE 0 END) bbcash3
FROM #Test
GROUP BY [Date],UserName
Go

Answer 1

动态的，Type在另一个表里有定义。

Answer 2

CREATE TABLE #Test
(
[Date] DATETIME,
UserName NVARCHAR(200),
[TYPE] NVARCHAR(100),
cash1 INT,
cash2 INT,
cash3 INT
)
GOINSERT #Test([Date],UserName,[TYPE],cash1,cash2,cash3)
SELECT '2014-01-16',N'Test',N'cc',0,1,2 UNION ALL
SELECT '2014-01-16',N'Test',N'cc',1,1,1 UNION ALL
SELECT '2014-01-16',N'Test',N'dd',2,3,4 UNION ALL
SELECT '2014-01-16',N'Test',N'ee',2,2,2DECLARE @sql NVARCHAR(4000)
SET @sql=N''
SELECT     @sql=@sql+','+[Type]+'cash1'+'=sum(case when [Type]='+quotename([Type],'''')+' then [cash1] else 0 end)'
+','+[Type]+'cash2'+'=sum(case when [Type]='+quotename([Type],'''')+' then [cash2] else 0 end)'
+','+[Type]+'cash2'+'=sum(case when [Type]='+quotename([Type],'''')+' then [cash3] else 0 end)'
FROM #Test GROUP BY [Date],[UserName],[TYPE]
EXEC('SELECT
[Date],UserName'+@sql+'
FROM #Test
GROUP BY [Date],UserName')

Answer 3

 /*
  Date UserName type1 type2 type3 type1 type2 type3
  2014-01-16 00:00:00.000 Test 1 2 3 4 5 6
  */
只能做到这了。

Answer 4

把4楼的修改了一下。  
  DECLARE @sql NVARCHAR(4000)
SET @sql=N''
SELECT     @sql=@sql+','+[Type]+'cash1'+'=max(case when [Type]='+quotename([Type],'''')+' then [cash1] else 0 end)'
                +','+[Type]+'cash2'+'=max(case when [Type]='+quotename([Type],'''')+' then [cash2] else 0 end)'
                +','+[Type]+'cash3'+'=max(case when [Type]='+quotename([Type],'''')+' then [cash3] else 0 end)'
FROM #Test GROUP BY [Date],[UserName],[TYPE]
EXEC('SELECT
    [Date],UserName'+@sql+'
FROM #Test
GROUP BY [Date],UserName')
/*
Date UserName aacash1 aacash2 aacash3 bbcash1 bbcash2 bbcash3
2014-01-16 00:00:00.000 Test 1 2 3 4 5 6
*/

Answer 5

这个错了，加一些数据，列名就重复了
drop table #Test
CREATE TABLE #Test
(
    [Date] DATETIME,
    UserName NVARCHAR(200),
    [TYPE] NVARCHAR(100),
    cash1 INT,
    cash2 INT,
    cash3 INT
)
GO
 
INSERT #Test([Date],UserName,[TYPE],cash1,cash2,cash3)
SELECT '2014-01-16',N'Test',N'cc',0,1,2 UNION ALL
SELECT '2014-01-16',N'Test',N'cc',1,1,1 UNION ALL
SELECT '2014-01-16',N'Testaa',N'dd',2,3,4 UNION ALL
SELECT '2014-01-16',N'Testbb',N'ee',2,2,2 UNION ALL
SELECT '2014-01-17',N'Testaa',N'dd',2,3,4 UNION ALL
SELECT '2014-01-19',N'Testbb',N'ee',2,2,2 
 
DECLARE @sql NVARCHAR(4000)
SET @sql=N''
SELECT     @sql=@sql+','+[Type]+'cash1'+'=sum(case when [Type]='+quotename([Type],'''')+' then [cash1] else 0 end)'
                +','+[Type]+'cash2'+'=sum(case when [Type]='+quotename([Type],'''')+' then [cash2] else 0 end)'
                +','+[Type]+'cash2'+'=sum(case when [Type]='+quotename([Type],'''')+' then [cash3] else 0 end)'
FROM #Test GROUP BY [Date],[UserName],[TYPE]
EXEC('SELECT
    [Date],UserName'+@sql+'
FROM #Test
GROUP BY [Date],UserName')

Answer 6

这个错了，加一些数据，列名就重复了
drop table #Test
CREATE TABLE #Test
(
    [Date] DATETIME,
    UserName NVARCHAR(200),
    [TYPE] NVARCHAR(100),
    cash1 INT,
    cash2 INT,
    cash3 INT
)
GO
 
INSERT #Test([Date],UserName,[TYPE],cash1,cash2,cash3)
SELECT '2014-01-16',N'Test',N'cc',0,1,2 UNION ALL
SELECT '2014-01-16',N'Test',N'cc',1,1,1 UNION ALL
SELECT '2014-01-16',N'Testaa',N'dd',2,3,4 UNION ALL
SELECT '2014-01-16',N'Testbb',N'ee',2,2,2 UNION ALL
SELECT '2014-01-17',N'Testaa',N'dd',2,3,4 UNION ALL
SELECT '2014-01-19',N'Testbb',N'ee',2,2,2 
 
DECLARE @sql NVARCHAR(4000)
SET @sql=N''
SELECT     @sql=@sql+','+[Type]+'cash1'+'=sum(case when [Type]='+quotename([Type],'''')+' then [cash1] else 0 end)'
                +','+[Type]+'cash2'+'=sum(case when [Type]='+quotename([Type],'''')+' then [cash2] else 0 end)'
                +','+[Type]+'cash2'+'=sum(case when [Type]='+quotename([Type],'''')+' then [cash3] else 0 end)'
FROM #Test GROUP BY [Date],[UserName],[TYPE]
EXEC('SELECT
    [Date],UserName'+@sql+'
FROM #Test
GROUP BY [Date],UserName')
嗯是错了
 
DECLARE @sql NVARCHAR(4000)
SET @sql=N''
SELECT     @sql=@sql+','+[Type]+'cash1'+'=sum(case when [Type]='+quotename([Type],'''')+' then [cash1] else 0 end)'
                +','+[Type]+'cash2'+'=sum(case when [Type]='+quotename([Type],'''')+' then [cash2] else 0 end)'
                +','+[Type]+'cash3'+'=sum(case when [Type]='+quotename([Type],'''')+' then [cash3] else 0 end)'
FROM #Test GROUP BY [TYPE]
EXEC('SELECT
    [Date],UserName'+@sql+'
FROM #Test
GROUP BY [Date],UserName')

Answer 7

这个错了，加一些数据，列名就重复了
drop table #Test
CREATE TABLE #Test
(
    [Date] DATETIME,
    UserName NVARCHAR(200),
    [TYPE] NVARCHAR(100),
    cash1 INT,
    cash2 INT,
    cash3 INT
)
GO
 
INSERT #Test([Date],UserName,[TYPE],cash1,cash2,cash3)
SELECT '2014-01-16',N'Test',N'cc',0,1,2 UNION ALL
SELECT '2014-01-16',N'Test',N'cc',1,1,1 UNION ALL
SELECT '2014-01-16',N'Testaa',N'dd',2,3,4 UNION ALL
SELECT '2014-01-16',N'Testbb',N'ee',2,2,2 UNION ALL
SELECT '2014-01-17',N'Testaa',N'dd',2,3,4 UNION ALL
SELECT '2014-01-19',N'Testbb',N'ee',2,2,2 
 
DECLARE @sql NVARCHAR(4000)
SET @sql=N''
SELECT     @sql=@sql+','+[Type]+'cash1'+'=sum(case when [Type]='+quotename([Type],'''')+' then [cash1] else 0 end)'
                +','+[Type]+'cash2'+'=sum(case when [Type]='+quotename([Type],'''')+' then [cash2] else 0 end)'
                +','+[Type]+'cash2'+'=sum(case when [Type]='+quotename([Type],'''')+' then [cash3] else 0 end)'
FROM #Test GROUP BY [Date],[UserName],[TYPE]
EXEC('SELECT
    [Date],UserName'+@sql+'
FROM #Test
GROUP BY [Date],UserName')
嗯是错了
 
DECLARE @sql NVARCHAR(4000)
SET @sql=N''
SELECT     @sql=@sql+','+[Type]+'cash1'+'=sum(case when [Type]='+quotename([Type],'''')+' then [cash1] else 0 end)'
                +','+[Type]+'cash2'+'=sum(case when [Type]='+quotename([Type],'''')+' then [cash2] else 0 end)'
                +','+[Type]+'cash3'+'=sum(case when [Type]='+quotename([Type],'''')+' then [cash3] else 0 end)'
FROM #Test GROUP BY [TYPE]
EXEC('SELECT
    [Date],UserName'+@sql+'
FROM #Test
GROUP BY [Date],UserName')嗯，这个对了，如果我想再加一列，SubType，就变成结果如下，要怎么改？
SubType也做group by Type,SubTypeDate                                       UserName    type1-a    type1-b   type2-a    type2-b    type3-a   type3-b
  2014-01-16 00:00:00.000    Test              1               2                3             4             5                 6