请问一个group by有关问题
请教一个group by问题
以下数据：
date username type cash1 cash2 cash3
2014-01-16 test aa 1 2 3
2014-01-16 test bb 4 5 6
以上数据是通过group by date, username, type出来的，想变成如下格式：
date username aacash1 aacash2 aacash3 bbcash1 bbcash2 bbcash3
2014-01-16 test 1 2 3 4 5 6
sql能写不？
------解决方案--------------------
当然可以，只是有点小麻烦罢了：
------解决方案--------------------
动态还是静态的？
------解决方案--------------------
------解决方案--------------------
只能做到这了。
------解决方案--------------------
把4楼的修改了一下。
以下数据：
date username type cash1 cash2 cash3
2014-01-16 test aa 1 2 3
2014-01-16 test bb 4 5 6
以上数据是通过group by date, username, type出来的，想变成如下格式：
date username aacash1 aacash2 aacash3 bbcash1 bbcash2 bbcash3
2014-01-16 test 1 2 3 4 5 6
sql能写不？
------解决方案--------------------
当然可以，只是有点小麻烦罢了：
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'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
GO
SELECT
[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
------解决方案--------------------
动态还是静态的？
------解决方案--------------------
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'Test',N'dd',2,3,4 UNION ALL
SELECT '2014-01-16',N'Test',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')
------解决方案--------------------
/*
Date UserName type1 type2 type3 type1 type2 type3
2014-01-16 00:00:00.000 Test 1 2 3 4 5 6
*/
只能做到这了。
------解决方案--------------------
把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]