请教一个group by问题

踏雪听雨 2014-06-09 06:14:04
以下数据：
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能写不？
...全文
188 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
exception92 2014-06-10
  • 打赏
  • 举报
 回复
把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
*/
exception92 2014-06-10
  • 打赏
  • 举报
 回复 

 /*
  Date	UserName	type1	type2	type3	type1	type2	type3
  2014-01-16 00:00:00.000	Test	1	2	3	4	5	6
  */
只能做到这了。
chen357313771 2014-06-10
  • 打赏
  • 举报
 回复 
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')
踏雪听雨 2014-06-10
  • 打赏
  • 举报
 回复
引用 2 楼 DBA_Huangzj 的回复:
动态还是静态的？
动态的，Type在另一个表里有定义。
發糞塗牆 2014-06-10
  • 打赏
  • 举报
 回复
动态还是静态的？
踏雪听雨 2014-06-10
  • 打赏
  • 举报
 回复
引用 8 楼 chen357313771 的回复:
[quote=引用 7 楼 SaRoot 的回复:] [quote=引用 4 楼 chen357313771 的回复:] 
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')
这个错了，加一些数据，列名就重复了 

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')
[/quote] 嗯是错了 
 
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')
[/quote] 嗯，这个对了，如果我想再加一列，SubType，就变成结果如下，要怎么改？ SubType也做group by Type,SubType Date 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
chen357313771 2014-06-10
  • 打赏
  • 举报
 回复
引用 7 楼 SaRoot 的回复:
[quote=引用 4 楼 chen357313771 的回复:] 
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')
这个错了，加一些数据，列名就重复了 

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')
[/quote] 嗯是错了 
 
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')
踏雪听雨 2014-06-10
  • 打赏
  • 举报
 回复
引用 4 楼 chen357313771 的回复:
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')
这个错了，加一些数据，列名就重复了 

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')
詩和遠方 2014-06-09
  • 打赏
  • 举报
 回复
当然可以，只是有点小麻烦罢了： 
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
解决C#中Linq GroupBy 和OrderBy失效的方法
在一个数据列表中我用了Linq GroupBy 和OrderBy。 排序在本机正常使用，发到测试后排序死活不对，总以为是程序问题。于是请教了别人有了以下的答案。 问题原因和解决方法 因为服务器装的是英文版操作系统，没有中文...
MySQL 查询某个字段不重复的所有记录
假设现在有如下N条记录 表明叫book id author title 1 aaa AAA 2 bbb ...想请教应该怎么写 答案: 代码如下:select a.* from book a right join ( select max(id) id from book group by title) b on b.id = a.id where
SQL Server数据库基础到精通课程
变量定义，字段约束、等等，Select 、Where、Top、Group By 、Order By，Distinct、Declare等，详细可看图片和课程，本课程带有完整的SQL数据库课程课件，代码有注释解析等等，学习起来简单易懂，易上手直接操作，...
Mysql踩坑——Group by+Order by带来的问题
最近上线的一个统计月活用户的需求，看起来一个定时任务+几句sql统计就一步到位结束了。近几日发现生产数据没我的月活数据，于是就排查起来。 问题知道，那下一步肯定就是去排查了。 排查思路 先确定是不是任务...
Oracle如何给groupby加索引,请教 group by如何走索引
SQL> set autot on SQL> select owner,max(object_id) from t group by owner; OWNER MAX(OBJECT_ID) ------------------------------ -------------- ACMS 59869 DBSNMP 11991 EXFSYS 53969 XXXX 58952 ...
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章