求行转列SQL语句 谢谢

ykwang 2008-10-13 09:41:11
有个表数据如下:
Name Companyname date prices
A aa公司 2008-10-10 5.5
A ab公司 2008-10-09 4
B aa公司 2008-10-10 5
B ab公司 2008-10-9 4

要实现的结果如下

name aa公司 ab公司
a 5.5 4
b 5 4

这个主要是行转列问题,因为刚接触SQL语句,搞不定. 谢谢高手
...全文
330 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
wer123q 2008-10-13
  • 打赏
  • 举报
 回复 
CREATE TABLE #T (Name VARCHAR(1),Companyname NVARCHAR(6),date DATETIME,prices NUMERIC(2,1))

INSERT INTO #T

SELECT 'A','aa公司','2008-10-10','5.5' UNION ALL

SELECT 'A','ab公司','2008-10-09','4' UNION ALL

SELECT 'B','aa公司','2008-10-10','5' UNION ALL

SELECT 'B','ab公司','2008-10-9','4'





select * from #T





declare 

	@str varchar(1000)

	set @str = 'select name '

	select @str = @str + ','+ 'max(case Companyname when '''+Companyname+''' then prices else 0 end)'''+Companyname+''''

	from (select distinct Companyname from #t )aa

	

	select @str = @str + ' from  #t group by name '

exec (@str)
BernardSun 2008-10-13
  • 打赏
  • 举报
 回复 


declare  @AA table([Name] varchar(50),Companyname varchar(50),

[date] datetime,prices float)

                  

insert into @AA([Name],Companyname,[date],prices)

select 'A'   ,       'aa公司'         , '2008-10-10',        5.5 union all

select 'A'    ,      'ab公司'        ,  '2008-10-09' ,       4 union all

select 'B'     ,     'aa公司'       ,   '2008-10-10'  ,      5 union all

select 'B'      ,    'ab公司'       ,   '2008-10-9'    ,      4 



select * from @aa



select [Name],

       aa公司 =sum(case when Companyname='aa公司' then prices else 0 end),

       ab公司= sum(case when Companyname='ab公司' then prices else 0 end)

from @aa

group by [Name]
hsie168518 2008-10-13
  • 打赏
  • 举报
 回复 
CREATE TABLE #T (Name VARCHAR(1),Companyname NVARCHAR(6),date DATETIME,prices NUMERIC(2,1))

INSERT INTO #T

SELECT 'A','aa公司','2008-10-10','5.5' UNION ALL

SELECT 'A','ab公司','2008-10-09','4' UNION ALL

SELECT 'B','aa公司','2008-10-10','5' UNION ALL

SELECT 'B','ab公司','2008-10-9','4'



select name,

  max(case companyname when 'aa公司' then prices else 0 end) aa公司,

  max(case companyname when 'ab公司' then prices else 0 end) ab公司

from #t

group by name

--结果

name    aa公司  aa公司

A	5.5	4.0

B	5.0	4.0
liangCK 2008-10-13
  • 打赏
  • 举报
 回复
3楼是动态的.指CompanyName不定的情况..

下面是静态的.

SELECT Name,

       MAX(CASE WHEN Companyname='aa公司' then prices ELSE 0 END) [aa公司],

       MAX(CASE WHEN Companyname='ab公司' then prices ELSE 0 END) [ab公司] 

FROM tb_name

GROUP BY Name
liangCK 2008-10-13
  • 打赏
  • 举报
 回复 
--> liangCK小梁 于2008-10-13

--> 生成测试数据: #T

IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T

CREATE TABLE #T (Name VARCHAR(1),Companyname NVARCHAR(6),date DATETIME,prices NUMERIC(2,1))

INSERT INTO #T

SELECT 'A','aa公司','2008-10-10','5.5' UNION ALL

SELECT 'A','ab公司','2008-10-09','4' UNION ALL

SELECT 'B','aa公司','2008-10-10','5' UNION ALL

SELECT 'B','ab公司','2008-10-9','4'



--SQL查询如下:



DECLARE @str nvarchar(4000);

SET @str='';



SELECT @str=@str+',MAX(CASE WHEN Companyname='''+Companyname+''' then prices ELSE 0 END) ['+Companyname+']'

FROM (SELECT DISTINCT Companyname FROM #T ) AS t;



EXEC ('SELECT Name'+@str+' FROM #T GROUP BY Name');



/*

Name aa公司                                    ab公司

---- --------------------------------------- ---------------------------------------

A    5.5                                     4.0

B    5.0                                     4.0



(2 行受影响)



*/
liangCK 2008-10-13
  • 打赏
  • 举报
 回复 
/*

标题：普通行列转换(version 2.0)

作者：爱新觉罗.毓华 

时间：2008-03-09

地点：广东深圳

说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。



问题：假设有张学生成绩表(tb)如下:

姓名 课程 分数

张三 语文 74

张三 数学 83

张三 物理 93

李四 语文 74

李四 数学 84

李四 物理 94

想变成(得到如下结果)： 

姓名 语文 数学 物理 

---- ---- ---- ----

李四 74   84   94

张三 74   83   93

-------------------

*/



create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)

insert into tb values('张三' , '语文' , 74)

insert into tb values('张三' , '数学' , 83)

insert into tb values('张三' , '物理' , 93)

insert into tb values('李四' , '语文' , 74)

insert into tb values('李四' , '数学' , 84)

insert into tb values('李四' , '物理' , 94)

go



--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)

select 姓名 as 姓名 ,

  max(case 课程 when '语文' then 分数 else 0 end) 语文,

  max(case 课程 when '数学' then 分数 else 0 end) 数学,

  max(case 课程 when '物理' then 分数 else 0 end) 物理

from tb

group by 姓名



--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)

declare @sql varchar(8000)

set @sql = 'select 姓名 '

select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'

from (select distinct 课程 from tb) as a

set @sql = @sql + ' from tb group by 姓名'

exec(@sql) 



--SQL SERVER 2005 静态SQL。

select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b



--SQL SERVER 2005 动态SQL。

declare @sql varchar(8000)

select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程

exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')



---------------------------------



/*

问题：在上述结果的基础上加平均分，总分，得到如下结果：

姓名 语文 数学 物理 平均分 总分 

---- ---- ---- ---- ------ ----

李四 74   84   94   84.00  252

张三 74   83   93   83.33  250

*/



--SQL SERVER 2000 静态SQL。

select 姓名 姓名,

  max(case 课程 when '语文' then 分数 else 0 end) 语文,

  max(case 课程 when '数学' then 分数 else 0 end) 数学,

  max(case 课程 when '物理' then 分数 else 0 end) 物理,

  cast(avg(分数*1.0) as decimal(18,2)) 平均分,

  sum(分数) 总分

from tb

group by 姓名



--SQL SERVER 2000 动态SQL。

declare @sql varchar(8000)

set @sql = 'select 姓名 '

select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'

from (select distinct 课程 from tb) as a

set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'

exec(@sql) 



--SQL SERVER 2005 静态SQL。

select m.* , n.平均分 , n.总分 from

(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,

(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n

where m.姓名 = n.姓名



--SQL SERVER 2005 动态SQL。

declare @sql varchar(8000)

select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程

exec ('select m.* , n.平均分 , n.总分 from

(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m , 

(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n

where m.姓名 = n.姓名')



drop table tb    



------------------

------------------



/*

问题：如果上述两表互相换一下：即表结构和数据为：

姓名 语文 数学 物理

张三 74　　83　　93

李四 74　　84　　94

想变成(得到如下结果)： 

姓名 课程 分数 

---- ---- ----

李四 语文 74

李四 数学 84

李四 物理 94

张三 语文 74

张三 数学 83

张三 物理 93

--------------

*/



create table tb(姓名 varchar(10) , 语文 int , 数学 int , 物理 int)

insert into tb values('张三',74,83,93)

insert into tb values('李四',74,84,94)

go



--SQL SERVER 2000 静态SQL。

select * from

(

 select 姓名 , 课程 = '语文' , 分数 = 语文 from tb 

 union all

 select 姓名 , 课程 = '数学' , 分数 = 数学 from tb

 union all

 select 姓名 , 课程 = '物理' , 分数 = 物理 from tb

) t

order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 end



--SQL SERVER 2000 动态SQL。

--调用系统表动态生态。

declare @sql varchar(8000)

select @sql = isnull(@sql + ' union all ' , '' ) + ' select 姓名 , [课程] = ' + quotename(Name , '''') + ' , [分数] = ' + quotename(Name) + ' from tb'

from syscolumns 

where name! = N'姓名' and ID = object_id('tb') --表名tb，不包含列名为姓名的其它列

order by colid asc

exec(@sql + ' order by 姓名 ')



--SQL SERVER 2005 动态SQL。

select 姓名 , 课程 , 分数 from tb unpivot (分数 for 课程 in([语文] , [数学] , [物理])) t



--SQL SERVER 2005 动态SQL，同SQL SERVER 2000 动态SQL。



--------------------

/*

问题：在上述的结果上加个平均分，总分，得到如下结果：

姓名 课程   分数

---- ------ ------

李四 语文   74.00

李四 数学   84.00

李四 物理   94.00

李四 平均分 84.00

李四 总分   252.00

张三 语文   74.00

张三 数学   83.00

张三 物理   93.00

张三 平均分 83.33

张三 总分   250.00

------------------

*/



select * from

(

 select 姓名 as 姓名 , 课程 = '语文' , 分数 = 语文 from tb 

 union all

 select 姓名 as 姓名 , 课程 = '数学' , 分数 = 数学 from tb

 union all

 select 姓名 as 姓名 , 课程 = '物理' , 分数 = 物理 from tb

 union all

 select 姓名 as 姓名 , 课程 = '平均分' , 分数 = cast((语文 + 数学 + 物理)*1.0/3 as decimal(18,2)) from tb

 union all

 select 姓名 as 姓名 , 课程 = '总分' , 分数 = 语文 + 数学 + 物理 from tb

) t

order by 姓名 , case 课程 when '语文' then 1 when '数学' then 2 when '物理' then 3 when '平均分' then 4 when '总分' then 5 end



drop table tb
ykwang 2008-10-13
  • 打赏
  • 举报
 回复
补充一点: 这个 Companyname 的数量是不固定的,有可能有多个单位.
7天带你玩转Mysql数据库之SQL语句
您观看课程学习后 免费入群领取【超全Python资料包+17本学习电子书】 七年SQL语句编写经验。满满的干货！ 在网上找了好多SQL数据库的教程，但你还会面临这样的问题： 1. 自己写了SQL却不知道对不对。 2. 在上线后...
mysql行变列sql_SQL行转列、列转行的简单实现
行列转换就是如下图所示两种展示形式的互相转换行转列假如我们有下表：SELECT *FROM studentPIVOT (SUM(score) FOR subject IN (语文, 数学, 英语))通过上面 SQL 语句即可得到下面的结果PIVOT 后跟一个聚合函数来拿...
SQL实现行转列的两种方式
SQL实现行转列的两种方式 在我们日常开发的场景中，可能会遇到使用SQL进行行转列的问题，那么下面我将介绍两种方式供大家参考。大家只要认真看完，我想肯定会学会这两种简单的方法的！ 首先我们准备一张数据表：my_...
SQL行转列、列转行的简单实现
行转列 假如我们有下表： 1 2 3 4 5 SELECT* FROMstudent PIVOT ( SUM(score)FORsubjectIN(语文, 数学, 英语) ) 通过上面 SQL 语句即可得到下面的结果 ...
【SQL】窗口函数及行转列等操作总结
行转列用groupby+sumif，列转行用union allUNION和UNION ALL效率：UNION和UNION ALL关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同。对重复结果的处理：UNION在进行表链接后会筛选掉重复...
MS-SQL Server

34,590

社区成员

254,589

社区内容

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

加载中

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

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

+ 用AI写文章