SQL动态行转列的问题

a7740798 2010-06-02 09:39:43
如题 ：


SELECT TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd') dt,

type,

       COUNT(*)

FROM   T_LIST

WHERE to_char(to_date(ACCEPT_TIME,'yyyy-mm-dd hh24:mi:ss'),'MM')=to_char(SYSDATE-1,'MM')

GROUP  BY TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd'),type

ORDER BY TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd');



t_list 操作流水表， type 操作类型 ACCEPT_TIME操作时间
查询结果
序号 时间 类型 总量
1 2010-05-11 17011000 4
2 2010-05-11 1701100001 1
3 2010-05-12 1701100001 1

查询条件为一周内起止时间
如5月11日到17日 但不固定用户可以自己选择

要求显示的结果


 <table border='1px;' bgcolor='#ccffcc'>

   <tr>

    <td>类型</td>

    <td clospan='7'>日期</td>

    <td>总数</td>

  </tr>

  <tr>

    <td> </td>

    <td>2010-5-11</td>

    <td>2010-5-12</td>

    <td>2010-5-13</td>

    <td>2010-5-14</td>

    <td>2010-5-15</td>

    <td>2010-5-16</td>

    <td>2010-5-17</td>

    <td>6</td>

  </tr>

    <tr>

    <td>17011000</td>

    <td>4</td>

    <td>0</td>

    <td>0</td>

    <td>0</td>

    <td>0</td>

    <td>0</td>

    <td>0</td>

    <td>4</td>

  </tr>

    <tr>

    <td>1701100001</td>

    <td>1</td>

    <td>1</td>

    <td>0</td>

    <td>0</td>

    <td>0</td>

    <td>0</td>

    <td>0</td>

    <td>2</td>

  </tr>

</table>
...全文
1112 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
--小F-- 2010-06-02
  • 打赏
  • 举报
 回复 
*

标题：普通行列转换(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 课程

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
a7740798 2010-06-02
  • 打赏
  • 举报
 回复
[Quote=引用 6 楼 beirut 的回复:]
引用 1 楼 htl258 的回复:

是要Oracle的吗，是的话建议到Oracle版块提问

嗯
[/Quote]

谢谢顶帖
a7740798 2010-06-02
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 htl258 的回复:]
要显示的结果是XML格式吗
[/Quote]
麻烦帮我用sqlServer写一个 看有什么启发 谢谢！
黄_瓜 2010-06-02
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 htl258 的回复:]

是要Oracle的吗，是的话建议到Oracle版块提问
[/Quote]
嗯
a7740798 2010-06-02
  • 打赏
  • 举报
 回复
不是 是显示到页面表格中
htl258_Tony 2010-06-02
  • 打赏
  • 举报
 回复
要显示的结果是XML格式吗
a7740798 2010-06-02
  • 打赏
  • 举报
 回复 


SELECT TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd') dt,

type,

       COUNT(*)

FROM   T_LIST

WHERE to_char(to_date(ACCEPT_TIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd')>='查询开始时间' and to_char(to_date(ACCEPT_TIME,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd')<='查询结束时间' 

GROUP  BY TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd'),type

ORDER BY TO_CHAR(TO_DATE(ACCEPT_TIME, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd');


语句弄错了
a7740798 2010-06-02
  • 打赏
  • 举报
 回复
嗯 是Oracle的 SqlServer板块的牛人多些 昨天在Oracle板块发了贴
htl258_Tony 2010-06-02
  • 打赏
  • 举报
 回复
是要Oracle的吗，是的话建议到Oracle版块提问
修改一下昵称 2010-06-02
  • 打赏
  • 举报
 回复 


17011000	3	0	0	0	0	0	0

1701100001	1	1	0	0	0	0	0



忽悠，要记得结贴给分哦。下次来这边请我吃个饭就可以了。嘿嘿！！！
修改一下昵称 2010-06-02
  • 打赏
  • 举报
 回复 


DECLARE @tab table(date varchar(50),typeName varchar(50),tol int)

DECLARE @date1 varchar(50)

DECLARE @date2 varchar(50)

DECLARE @tabTemp table(date varchar(20))

DECLARE @i int 

DECLARE @SQL varchar(8000) 

SET @i = 0

SET @date1 = '2010-05-11' 

SET @date2 = '2010-05-17'

while(@i<7) 

BEGIN

	insert into @tabTemp values(REPLACE(CONVERT(varchar(10),dateadd(dd,@i,convert(dateTime,@date1)),120),N'-00','-'))

	SET @i = @i +1

END



SET @SQL = 'DECLARE @tab table(date varchar(50),typeName varchar(50),tol int) ' 

SET @SQL = @SQL + ' insert into @tab select ''2010-05-11'',''17011000'',2 union all select ''2010-05-11'',''17011000'',1 union all select ''2010-05-11'',''1701100001'',1 union all select ''2010-05-12'',''1701100001'',1 '

SET @SQL =  @SQL + ' select * from @tab '

----

SET @SQL = @SQL + ' ;with temp as(select typeName ' 

select @SQL = @SQL + ' ,case date when '''+date+''' then sum(tol) else 0 end as '''+('N'+replace(date,'-',''))+'''' from @tabTemp

SET @SQL = @SQL+'from @tab group by date,typeName) select typeName '

select @SQL = @SQL + ',sum('+('N'+replace(date,'-',''))+') as '''+date+'''' from @tabTemp

SET @SQL = @SQL + ' from temp group by typeName  '





EXEC(@SQL)
Sql Server 动态行转列
Sql Server 动态行转列。
动态行转列：处理不确定数量的行转列操作
行转列操作是一种常见的数据转换技术，它可以将原始的行数据转换为列数据，从而更方便地进行分析和可视化。然而，传统的行转列方式只能处理固定数量的行转列操作，无法处理不确定数量的行转列操作。为了解决这个问题，我们可以使用动态行转列技术。动态行转列可以处理不确定数量的行转列操作，因为它使用动态 SQL 语句生成动态的列名。动态行转列具有高度的灵活性和扩展性，可以适应不同的数据结构和应用场景。在本文中，我们将介绍如何使用动态 SQL 实现动态行转列，并提供一个具体的案例演示。
mysql动态行转列,sql动态行转列的两种方法
这篇文章主要为大家详细介绍了sql动态行转列的两种方法，具有一定的参考价值，可以用来参考一下。感兴趣的小伙伴，下面一起跟随512笔记的小编两巴掌来看看吧！第一种方法：代码如下:select *from ( select Url,case when Month=01 then '1月' when Month=02 then '2月' when Month=03 then '3月' whe...
SQL Server 动态行转列（参数化表名、分组列、行转列字段、字段值）
其实行转列并不是一个什么新鲜的话题了，甚至已经被大家说到烂了，网上的很多例子多多少少都有些问题，所以我希望能让大家快速的看到执行的效果，所以在动态列的基础上再把表、分组字段、行转列字段、值这四个行转列固定需要的值变成真正意义的参数化，大家只需要根据自己的环境，设置参数值，马上就能看到效果了（可以直接跳转至：“(三) 接着以动态的方式实现行转列，这是使用拼接SQL的方式实现的，所以它适用于SQL Server 2000以上的数据库版本，执行脚本返回的结果如图2所示；（图1：行转列效果图）
SQL Server 行转列，列转行,多行转成一列
目录一、多行转成一列（并以","隔开）二、一列转成多行三、行转列1、建立表格2、使用SQL Server 2000静态SQL四、行转列结果加上总分、平均分1、使用SQL Server 2000静态SQL2、使用SQL Server 2000动态SQL3、使用SQL Server 2005静态SQL4、使用SQL Server 2005动态SQL五、列转行1、建立表格2、使用SQL Server 2000静态SQL2、使用SQL Server 2000动态SQL3、使用SQL Server 2005静态SQL
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章