求SQL语句，行转列并进行汇总

maoyunaa 2013-08-09 02:37:39

大家好！
如何将一个表中的行转行列并进行汇总。详情见下图:

既将A表的数据转成B表，其中A表的数据行数不固定。

...全文

3938 17 打赏收藏转发到动态举报

写回复

用AI写文章

17 条回复

切换为时间正序

请发表友善的回复…

发表回复

hbyjw 2013-09-13

打赏
举报

2005及以上有个行转列函数PIVOT

越过越咸 2013-09-12

打赏
举报

mark 接分！

xxfvba 2013-08-29

打赏
举报

create table test2 (item nvarchar(20),Dat varchar(10),Qty int) insert test2 select 1001,'06-08',11 union all select 1001,'06-09',12 union all select 1001,'06-10',13 union all select 1001,'06-11',14 union all select 1001,'06-12',15 union all declare @s varchar(max) select @s=isnull(@s+',','')+ '['+dat+']' from test2 group by dat order by dat set @s='select * from test2 pivot(sum(qty) for dat in ('+@s+')) a' --print(@s) exec(@s)

chenghaibing2008 2013-08-29

打赏
举报

select 1001,'06-08',11 union all select 1001,'06-09',12 union all select 1001,'06-10',13 union all select 1001,'06-11',14 union all select 1001,'06-12',15 看上面好多回复,很多人都只是拿楼主的例子在做行转列但明显不符合楼主的需要,人家的行列不固定的,你这里写死1001,06-08,06-08什么的,要是有1000个, 也要写1000笔union all吗, 楼主想要的是算法,而不是单独的把那几行几列数据相互转而已,写死的方法谁不会啊

發糞塗牆 2013-08-28

打赏
举报

另外补充两点： 1、回复请引用，你这个贴拖得太久了，10几天，不引用的话，很难看到你有疑问。 2、如果纯sql无法作出漂亮的报表，特别是你一开始那个表2，那个不是sql的范凑了，只要数据查出来，剩下的就应该交给报表软件或者前端软件去实现。

發糞塗牆 2013-08-28

打赏
举报

我这里哪里指定列了？我特意写的是动态的，难道你没试过？

引用 11 楼 maoyunaa 的回复:

[quote=引用 2 楼 DBA_Huangzj 的回复:]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-08-09 14:40:54
-- Version:
--      Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64) 
--	Jun 10 2013 20:09:10 
--	Copyright (c) Microsoft Corporation
--	Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([品号] int,[日期] varchar(5),[数量] int)
insert [A]
select 1001,'06-08',11 union all
select 1001,'06-09',12 union all
select 1001,'06-10',13 union all
select 1001,'06-11',14 union all
select 1001,'06-12',15
--------------开始查询--------------------------

declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([日期])+'=max(case when [日期]='+quotename([日期],'''')+' then [数量] else 0 end)'
from [A] group by [日期]
exec('select [品号]'+@s+' from A group by [品号]')
----------------结果----------------------------
/* 
品号          06-08       06-09       06-10       06-11       06-12
----------- ----------- ----------- ----------- ----------- -----------
1001        11          12          13          14          15
*/

这对固定列的表可以用这种方法，但我的需求是列是不固定的。[/quote]

maoyunaa 2013-08-27

打赏
举报

引用 2 楼 DBA_Huangzj 的回复:

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-08-09 14:40:54
-- Version:
--      Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64) 
--	Jun 10 2013 20:09:10 
--	Copyright (c) Microsoft Corporation
--	Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([品号] int,[日期] varchar(5),[数量] int)
insert [A]
select 1001,'06-08',11 union all
select 1001,'06-09',12 union all
select 1001,'06-10',13 union all
select 1001,'06-11',14 union all
select 1001,'06-12',15
--------------开始查询--------------------------

declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([日期])+'=max(case when [日期]='+quotename([日期],'''')+' then [数量] else 0 end)'
from [A] group by [日期]
exec('select [品号]'+@s+' from A group by [品号]')
----------------结果----------------------------
/* 
品号          06-08       06-09       06-10       06-11       06-12
----------- ----------- ----------- ----------- ----------- -----------
1001        11          12          13          14          15
*/

这对固定列的表可以用这种方法，但我的需求是列是不固定的。

nonoqiqi 2013-08-20

打赏
举报

也就是说如果从统计的话，楼主有个‘品号’字段很有可能是很多条记录同一个品号，套用 @SQL+ CASE THEN 能出来同一‘品号’的数量信息而对于我这张表，没有‘品号’这个概念，只需要把行彻底转换为列。并且有几列就转几行这个逻辑

nonoqiqi 2013-08-20

打赏
举报

各位大哥
我要的是这样的结果

用斑竹的公式

declare @s nvarchar(4000) set @s=''Select     @s=@s+','+quotename([日期])+'=max(case when [日期]='+quotename([日期],'''')+' then [数量] else 0 end)'from [A] group by [日期] exec('select [品号]'+@s+' from A group by [品号]')

出来表2的效果

nonoqiqi 2013-08-20

打赏
举报

这个不是要的效果啊。。。
出来变这样了

yudeli 2013-08-14

打赏
举报

declare @s nvarchar(4000) set @s='' Select @s=@s+','+quotename([日期])+'=SUM(case when [日期]='+quotename([日期],'''')+' then [数量] else 0 end)' from [A] group by [日期] PRINT @S exec('select [品号]'+@s+' from A group by [品号]')

maoyunaa 2013-08-09

打赏
举报

大家，见以下SQL代码:

if object_id('[TESTKC]') is not null drop table [TESTKC]
go 
CREATE TABLE [dbo].[TESTKC](
	[FID] [int] IDENTITY(1,1) NOT NULL,
	[LA001] [nvarchar](20) NULL,
	[LA004] [nvarchar](10) NULL,
	[JSL] [numeric](15, 6) NULL)
	
insert [TESTKC]
select 1001,'06-08',10 
union all
select 1001,'06-09',11 
union all
select 1002,'07-07',1 
union all
select 1002,'07-08',3

Declare @PivotSQL NVARCHAR(MAX)
SET @PivotSQL = N' SELECT LA001,[06-08],[06-09],[07-07],[07-08]
                   FROM TESTKC
                   pivot (sum([JSL]) FOR [LA004] IN ([06-08],[06-09],[07-07],[07-08]) ) AS BB '
execute sp_executesql @PivotSQL

查询出来的结果如下：

LA001	06-08	06-09	07-07	07-08
1001	10.000000	NULL	NULL	NULL
1001	NULL	11.000000	NULL	NULL
1002	NULL	NULL	1.000000	NULL
1002	NULL	NULL	NULL	3.000000

如何修改代码，使其查询的如果变成下面的结果：

LA001	06-08	06-09	07-07	07-08
1001	10.000000	11	NULL	NULL
1002	NULL	NULL	1.000000	3

Shawn 2013-08-09

打赏
举报

又见行转列呀

DROP TABLE #tb
create table #tb([品号] int,[日期] varchar(5),[数量] int)
insert #tb
select 1001,'06-08',11 union all
select 1001,'06-09',12 union all
select 1001,'06-10',13 union all
select 1001,'06-11',14 union all
select 1001,'06-12',15

DECLARE @sql NVARCHAR(MAX), @colList NVARCHAR(MAX)
SET @colList = STUFF((SELECT ','+QUOTENAME([日期]) FROM #tb GROUP BY [日期] ORDER BY [日期] FOR XML PATH('')),1,1,'')
SET @sql = N'
    select * from #tb a
    pivot
    (max([数量]) for [日期] in('+ @colList +')) b
'
EXEC(@sql)
/*
品号	06-08	06-09	06-10	06-11	06-12
1001	11	12	13	14	15
*/

-Tracy-McGrady- 2013-08-09

打赏
举报


declare @tbA table([品号] varchar(5),[日期] varchar(5),[数量] int)
insert into @tbA 
select '1001','06-08',11 union all
select '1001','06-09',12 union all
select '1001','06-10',13 union all
select '1001','06-11',14 union all
select '1001','06-12',15 union all
select '1001','06-13',16 

select [品号],
sum(case when [日期]='06-08' then [数量] else 0 end) as [06-08],
sum(case when [日期]='06-09' then [数量] else 0 end) as [06-09],
sum(case when [日期]='06-10' then [数量] else 0 end) as [06-10],
sum(case when [日期]='06-11' then [数量] else 0 end) as [06-11],
sum(case when [日期]='06-12' then [数量] else 0 end) as [06-12],
sum(case when [日期]='06-13' then [数量] else 0 end) as [06-13]
from @tbA
group by [品号]

(6 行受影响)
品号    06-08       06-09       06-10       06-11       06-12       06-13
----- ----------- ----------- ----------- ----------- ----------- -----------
1001  11          12          13          14          15          16

(1 行受影响)

chen357313771 2013-08-09

打赏
举报

CREATE TABLE #MyTable(
	ID int
	, time VARCHAR(10)
	, count INT
) 
INSERT INTO #MyTable
SELECT 1001,	'06-08',	11		
UNION	
SELECT 1001,	'06-09',	12	
UNION		
SELECT 1001,	'06-10',	13
UNION				
SELECT 1001,	'06-11',	14	
UNION			
SELECT 1001,	'06-12',	15	
UNION			
SELECT 1001,	'06-13',	16	



DECLARE @Sql VARCHAR(MAX)
SELECT @Sql=ISNULL(@Sql+',','')+'['+time+']' FROM #MyTable GROUP BY time
SELECT @Sql='SELECT *
             FROM #MyTable a 
             PIVOT(MAX(count) FOR time IN('+@Sql+')) p '      
EXEC(@Sql)

DROP TABLE #MyTable

發糞塗牆 2013-08-09

打赏
举报

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-08-09 14:40:54
-- Version:
--      Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64) 
--	Jun 10 2013 20:09:10 
--	Copyright (c) Microsoft Corporation
--	Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([品号] int,[日期] varchar(5),[数量] int)
insert [A]
select 1001,'06-08',11 union all
select 1001,'06-09',12 union all
select 1001,'06-10',13 union all
select 1001,'06-11',14 union all
select 1001,'06-12',15
--------------开始查询--------------------------

declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename([日期])+'=max(case when [日期]='+quotename([日期],'''')+' then [数量] else 0 end)'
from [A] group by [日期]
exec('select [品号]'+@s+' from A group by [品号]')
----------------结果----------------------------
/* 
品号          06-08       06-09       06-10       06-11       06-12
----------- ----------- ----------- ----------- ----------- -----------
1001        11          12          13          14          15
*/

-Tracy-McGrady- 2013-08-09

打赏
举报


select [品号],
sum(case when [日期]='06-08' then [数量] else 0 end) as [06-08],
sum(case when [日期]='06-09' then [数量] else 0 end) as [06-09],
sum(case when [日期]='06-10' then [数量] else 0 end) as [06-10],
sum(case when [日期]='06-11' then [数量] else 0 end) as [06-11],
sum(case when [日期]='06-12' then [数量] else 0 end) as [06-12],
sum(case when [日期]='06-13' then [数量] else 0 end) as [06-13]
from tbA
group by [品号]

对SQL Server 数据库基本SQL语句汇总，大部分复杂语句都可以通过这些基本语句组合实现

SQL行转列汇总阅读目录一. 基础语法: 二. 典型实例一. 基础语法: PIVOT用于将列值旋转为列名（即行转列），在 SQL Server 2000可以用聚合函数配合CASE语句实现 PIVOT 的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P 注意：PIVOT、UNPIVOT是SQL Server 2005 的语法，使用需修改数据库兼容级别(在数据库属性->选项->兼容级别改为 90 ) SQL2008 中可以直接使用完整语法： .

您观看课程学习后免费入群领取【超全Python资料包+17本学习电子书】七年SQL语句编写经验。满满的干货！在网上找了好多SQL数据库的教程，但你还会面临这样的问题：1. 自己写了SQL却不知道对不对。2. 在上线后发现原有的数据内容SQL语句执⾏结果没问题，但是数据变化了以后SQL返回的结果就错误了。3. 稍微复杂⼀点的SQL就不知道该如何写了。4. 看不懂同⾏写的SQL是为什么。5. 不知道索引的原理。6. 不知道怎么加索引能够提⾼查询性能纵观全网的教程都只停留在概念解析的层面，没有深入剖析原理，所以看了那么多的教程，可还是不会写。本教程与其他教程不同带你深入了解原理，让你真正了解什么是SQL语句，让你懂得SQL语句的编写⽅法。不再为SQL语句而烦恼。真正做到面对任何问题能够判断出SQL语句是不是能够实现，该怎样实现。

SQL 学习记录

hivesql行转列和列转行。