sql行转列

后浪 2011-09-13 04:01:54
表结构
ClassRelationID shopID ClassID ClassRelationsStatus
1 245 2 0
2 245 3 0
3 245 10 0
4 245 60 0


实现
shopID ClassIDCity ClassIDQu ClassIDRemark ClassIDType ClassRelationStatus
245 2 3 10 60 0
...全文
91 14 打赏 收藏 转发到动态 举报
写回复
用AI写文章
14 条回复
切换为时间正序
请发表友善的回复…
发表回复
后浪 2011-09-13
  • 打赏
  • 举报
 回复
[Quote=引用 13 楼 fredrickhu 的回复:]

引用 11 楼 xiaoye_loison 的回复:
小F姐姐人呢，我的问题还没解决...
上面的是针对 ClassRelationID 固定的

我现在ClassRelationID 不固定 是自动生成的
每四条一转的

这个需要4条一转哦 如果不是的话就需要动态语句了
[/Quote]
好的，这样就行了，谢谢小F姐
--小F-- 2011-09-13
  • 打赏
  • 举报
 回复
[Quote=引用 11 楼 xiaoye_loison 的回复:]
小F姐姐人呢，我的问题还没解决...
上面的是针对 ClassRelationID 固定的

我现在ClassRelationID 不固定 是自动生成的
每四条一转的
[/Quote]
这个需要4条一转哦 如果不是的话就需要动态语句了
--小F-- 2011-09-13
  • 打赏
  • 举报
 回复
[Quote=引用 11 楼 xiaoye_loison 的回复:]
小F姐姐人呢，我的问题还没解决...
上面的是针对 ClassRelationID 固定的

我现在ClassRelationID 不固定 是自动生成的
每四条一转的
[/Quote]



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

-- Author  :fredrickhu(小F，向高手学习)

-- Date    :2011-09-13 16:45:55

-- Verstion:

--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 

--	Apr 22 2011 11:57:00 

--	Copyright (c) Microsoft Corporation

--	Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)

--

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

--> 测试数据：[tb]

if object_id('[tb]') is not null drop table [tb]

go 

create table [tb]([ClassRelationID] int,[shopID] int,[ClassID] int,[ClassRelationsStatus] int)

insert [tb]

select 1,245,2,0 union all

select 2,245,3,0 union all

select 3,245,10,0 union all

select 4,245,60,0 union all

select 5,8,2,0 union all

select 6,8,4,0 union all

select 7,8,19,0 union all

select 8,8,61,0

--------------开始查询--------------------------

select

  shopid,

  max(case ID when 1 then  ClassID  end) as  ClassIDCity ,

  max(case ID when 2 then  ClassID  end) as  ClassIDQu  ,

  max(case ID when 3 then  ClassID  end) as  ClassIDRemark  ,

  max(case ID when 4 then  ClassID  end) as  ClassIDType ,

  ClassRelationsStatus

from

  (select id=row_number()over(partition by shopID order by getdate()),* from tb)t

group by

  shopid,ClassRelationsStatus

----------------结果----------------------------

/* shopid      ClassIDCity ClassIDQu   ClassIDRemark ClassIDType ClassRelationsStatus

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

8           2           4           19            61          0

245         2           3           10            60          0

警告: 聚合或其他 SET 操作消除了 Null 值。



(2 行受影响)



*/
后浪 2011-09-13
  • 打赏
  • 举报
 回复
小F姐姐人呢，我的问题还没解决...
上面的是针对 ClassRelationID 固定的

我现在ClassRelationID 不固定 是自动生成的
每四条一转的
后浪 2011-09-13
  • 打赏
  • 举报
 回复
[Quote=引用 6 楼 fredrickhu 的回复:]

引用 5 楼 xiaoye_loison 的回复:
引用 3 楼 fredrickhu 的回复:

SQL code
select
shopid,
max(case ClassRelationID when 1 then ClassID end) as ClassIDCity ,
max(case ClassRelationID when 2 then ClassID end) a……
[/Quote]


表结构

ClassRelationID   shopID            ClassID     ClassRelationsStatus

1                             245                        2                0

2                             245                      3                   0

3                               245                     10                  0

4                              245                      60                  0

5                               8                          2                   0

6                              8                            4                 0

7                               8                          19                  0

8                                8                          61                 0





实现

shopID ClassIDCity ClassIDQu ClassIDRemark ClassIDType ClassRelationStatus

245                  2             3               10                       60               0

8                      2             4               19                       61              0
火才松 2011-09-13
  • 打赏
  • 举报
 回复 


 use tempdb

go

create table tb_test(ClassRelationID int, shopID int, ClassID int, ClassRelationsStatus bit)

go

insert into tb_test

select 1 ,245 ,2, 0

union all

select 2 ,245, 3, 0

union all

select 3, 245, 10, 0

union all

select 4 ,245, 60 ,0

go

select shopID,[1]as ClassIDCity ,[2]as ClassIDQu ,[3] as ClassIDRemark,[4] as ClassIDType ,ClassRelationsStatus

 from tb_test

 pivot( sum(classID) for ClassRelationID in ([1],[2],[3],[4]) )as pvt

go

drop table tb_test

go
dawugui 2011-09-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 tb a pivot (max(分数) for 课程 in (语文,数学,物理)) b



--SQL SERVER 2005 动态SQL。

declare @sql varchar(8000)

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

set @sql = '[' + @sql + ']'

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
Lemon2050 2011-09-13
  • 打赏
  • 举报
 回复
Declare @length int, @count int
set @length=(select count(*) from tb)
set @count=1
while (@count<=@length)
begin
select
shopid,
max(case ClassRelationID when @count then ClassID end) as ClassIDCity ,
max(case ClassRelationID when (@count+1) then ClassID end) as ClassIDQu ,
max(case ClassRelationID when (@count+2) then ClassID end) as ClassIDRemark ,
max(case ClassRelationID when (@count+3) then ClassID end) as ClassIDType ,
ClassRelationStatus
from tb
group by shopid,ClassRelationStatus

set @count=@count+4
end
--小F-- 2011-09-13
  • 打赏
  • 举报
 回复
[Quote=引用 5 楼 xiaoye_loison 的回复:]
引用 3 楼 fredrickhu 的回复:

SQL code
select
shopid,
max(case ClassRelationID when 1 then ClassID end) as ClassIDCity ,
max(case ClassRelationID when 2 then ClassID end) as ClassIDQu ,
max(case Clas……
[/Quote]
那多给点数据
后浪 2011-09-13
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 fredrickhu 的回复:]

SQL code
select
shopid,
max(case ClassRelationID when 1 then ClassID end) as ClassIDCity ,
max(case ClassRelationID when 2 then ClassID end) as ClassIDQu ,
max(case ClassRelationID w……
[/Quote]
我这个记录是 四条一组的啊，不能是ClassRelationID 固定 1，2，3，4的
xoxo_ 2011-09-13
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 fredrickhu 的回复:]
SQL code
select
shopid,
max(case ClassRelationID when 1 then ClassID end) as ClassIDCity ,
max(case ClassRelationID when 2 then ClassID end) as ClassIDQu ,
max(case ClassRelationI……
[/Quote]
值得学习啊。 大家就是在抢他的分。。。而主角却在这里、、、
--小F-- 2011-09-13
  • 打赏
  • 举报
 回复 
select

  shopid,

  max(case ClassRelationID when 1 then  ClassID  end) as  ClassIDCity ,

  max(case ClassRelationID when 2 then  ClassID  end) as   ClassIDQu  ,

  max(case ClassRelationID when 3 then  ClassID  end) as   ClassIDRemark  ,

  max(case ClassRelationID when 4 then  ClassID  end) as  ClassIDType ,

  ClassRelationStatus

from

  tb

group by

  shopid,ClassRelationStatus
后浪 2011-09-13
  • 打赏
  • 举报
 回复
我晕排版成这样了...
xoxo_ 2011-09-13
  • 打赏
  • 举报
 回复
先别急哈 这会大家忙着抢分呢，
2019小白SQL入门（零基础60分钟含SQL语法问答精彩）
零基础60分钟含SQL语法问答精彩
SQL行转列、列转行
SQL行转列、列转行 这个主题还是比较常见的，行转列主要适用于对数据作聚合统计，如统计某类目的商品在某个时间区间的销售情况。列转行问题同样也很常见。 一、整理测试数据 create table wyc_test( id int(32)...
死磕：SQL行转列汇总（全网最全最详细）
SQL行转列汇总 阅读目录 一. 基础语法: 二. 典型实例 一. 基础语法: PIVOT用于将列值旋转为列名（即行转列），在 SQL Server 2000可以用聚合函数配合CASE语句实现 PIVOT 的一般语法是：PIVOT(聚合函数(列) FOR ...
sql 行转列
原数据：select * from GZWZ_BZMCZJDGS t行转列sql语句： -- sql 行转列 -- to_char() : 转换成字符，在使用listagg函数是出现乱码时使用-- listagg(to_char(bz.lx),',') : 把lx行转换成列，并用"...
hivesql行转列和列转行
hivesql行转列和列转行。
MS-SQL Server

34,590

社区成员

254,589

社区内容

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

加载中

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

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

+ 用AI写文章