求一行列转换问题

antsing_2008 2010-05-11 04:00:54
帮忙下，谢谢啦。
表数据如下：
fEmpID fDate fShiftCode fReasonCode
1 2010-01-01 A01
1 2010-01-02 A01
1 2010-01-03 A01
1 2010-01-04 其他
1 2010-01-05 A02
...
2 2010-01-01 A01
2 2010-01-02 休息
2 2010-01-03 A03
2 2010-01-04 A03
2 2010-01-05 A03
需求:
行列转换，需要结果如下：
fEmpID 1 2 3 4 5 6 ...
1 A01 A01 A01 其他 A02 ...
2 A01 休息 A03 A03 A03 ...
就这样，先谢了哈
...全文
174 12 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
jaydom 2010-05-11
  • 打赏
  • 举报
 回复
[Quote=引用 10 楼 happycell188 的回复:]
SQL code
--------------------SQL Server数据格式化工具-------------------
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
-- QQ :584738179
-- Developme……
[/Quote]

up
asharpnet 2010-05-11
  • 打赏
  • 举报
 回复
学习,up~
喜-喜 2010-05-11
  • 打赏
  • 举报
 回复 
--------------------SQL Server数据格式化工具-------------------

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

-- DESIGNER :happycell188(喜喜)

--       QQ :584738179

-- Development Tool :Microsoft Visual C++ 6.0    C Language 

-- FUNCTION :CONVERT DATA TO T-SQL

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

-- Microsoft SQL Server  2005

-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]

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

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



use test

go

if object_id('test.dbo.tb') is not null drop table tb

-- 创建数据表

create table tb

(

fEmpID int,

fDate datetime,

fShiftCode char(10),

fReasonCode varchar(100)

)

go

--插入测试数据

insert into tb select 1,'2010-01-01','A01',null

union all select 1,'2010-01-02','A01',null

union all select 1,'2010-01-03','A01',null

union all select 1,'2010-01-04',null,'其他'

union all select 1,'2010-01-05','A02',null

union all select 2,'2010-01-01','A01',null

union all select 2,'2010-01-02',null,'休息'

union all select 2,'2010-01-03','A03',null

union all select 2,'2010-01-04','A03',null

union all select 2,'2010-01-05','A03',null

go

--代码实现



declare @str varchar(max)

set @str=(select ',max(case when idd='+rtrim(idd)+' and fShiftCode is not null then fShiftCode when idd='+rtrim(idd)+' and fShiftCode is null then fReasonCode end) as '''+ltrim(idd)+''''

from (select distinct idd from (select idd =row_number()over(partition by fEmpID order by getdate()),* from tb)t)tt for xml path(''))

exec('select fEmpID'+@str+' from (select idd =row_number()over(partition by fEmpID order by getdate()),* from tb)t group by fEmpID')





/*测试结果



fEmpID 1    2    3    4    5

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

1       A01  A01  A01  其他  A02       

2       A01  休息  A03  A03  A03       



(2 行受影响)

*/
jaydom 2010-05-11
  • 打赏
  • 举报
 回复

我太菜，不会写存储过程，只会这种笨方法了


select distinct fEmpID ,max(case d.day when 1 then fShiftCode else null end) as [1]

                       ,max(case d.day when 2 then fShiftCode else null end) as [2]

                       ,max(case d.day when 3 then fShiftCode else null end) as [3]

                       ,max(case d.day when 4 then fShiftCode else null end) as [4]

                       ,max(case d.day when 5 then fShiftCode else null end) as [5]

                       ,max(case d.day when 6 then fShiftCode else null end) as [6]

                       ,max(case d.day when 7 then fShiftCode else null end) as [7]



from tb t join (select  top 31 RANk() over (order by id) as [day]

                from sysobjects 

                order by id ) d on right(t.fDate,2)=d.day

group by fEmpID
喜-喜 2010-05-11
  • 打赏
  • 举报
 回复 
declare @str varchar(max)

select @str=(select ','+ltrim(idd)+'=max(case when idd='''+rtrim(idd)+''' and fShiftCode is not null then fShiftCode

when idd='''+rtrim(idd)+''' and fShiftCode is not null then fReasonCode end)'

from (select idd =row_number()over(partition by fEmpID order by getdate()),* from tb)t for xml path(''))

exec('select fEmpID'+@str+' from tb group by fEmpID')
htl258_Tony 2010-05-11
  • 打赏
  • 举报
 回复 
----------------------------------------------------------------------------------

-- Author : htl258(Tony)

-- Date   : 2010-05-11 16:05:46

-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 

--          Jul  9 2008 14:43:34 

--          Copyright (c) 1988-2008 Microsoft Corporation

--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

-- Blog   : http://blog.csdn.net/htl258

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



--> 生成测试数据表: [tb]

IF OBJECT_ID('[tb]') IS NOT NULL

	DROP TABLE [tb]

GO

CREATE TABLE [tb] ([fEmpID] [int],[fDate] [datetime],[fShiftCode] [nvarchar](10),[fReasonCode] sql_variant)

INSERT INTO [tb]

SELECT '1','2010-01-01','A01',null UNION ALL

SELECT '1','2010-01-02','A01',null UNION ALL

SELECT '1','2010-01-03','A01',null UNION ALL

SELECT '1','2010-01-04','其他',null UNION ALL

SELECT '1','2010-01-05','A02',null UNION ALL

SELECT '2','2010-01-01','A01',null UNION ALL

SELECT '2','2010-01-02','休息',null UNION ALL

SELECT '2','2010-01-03','A03',null UNION ALL

SELECT '2','2010-01-04','A03',null UNION ALL

SELECT '2','2010-01-05','A03',null



--SELECT * FROM [tb]



-->SQL查询如下:

IF OBJECT_ID('[p_test]') IS NOT NULL

    DROP PROC [p_test]

GO

CREATE PROC [p_test]

@bt datetime,--起始日期

@et datetime --截止日期

AS

declare @s varchar(8000)

select @s=isnull(@s,'select [fEmpID]')

         +',max(case day(fdate) when '+datename(day,@bt+number)+' then fShiftCode else '''' end)['+datename(day,@bt+number)+']'

from master..spt_values 

where type='p' and number <= datediff(dd,@bt,@et)

set @s=@s+' from tb where [fDate] between '''+convert(char,@bt,23)+''' and '''+convert(char,@et,23)+''' group by fEmpID'

exec(@s)

go



--调用：

exec p_test '2010-1-2','2010-1-15'

/*

fEmpID      2          3          4          5          6          7          8          9          10         11         12         13         14         15

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

1           A01        A01        其他         A02                                                                                                           

2           休息         A03        A03        A03                                                                                                           



(2 行受影响)

*/
这个过程可以指定日期范围。
ws_hgo 2010-05-11
  • 打赏
  • 举报
 回复 
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)





select UserName,sum(case when Subject= '数学' then Score else 0 end) [数学],sum(case when Subject= '物理' then Score else 0 end) [物理],sum(case when Subject= '语文' then Score else 0 end) [语文]

declare @sql varchar(1000)

set @sql='select UserName'

select @sql=@sql+',sum(case when Subject= ''' +Subject+ ''' then Score else 0 end) ['+Subject+']' from (select distinct Subject from tb)a



set @sql = @sql + ' from tb group by UserName'

print @sql

exec(@sql) 





--讲解:



--这个是第一次执行

select 姓名,max(case 课程 when '数学' then 分数 else 0 end) [数学], 

--这个是第二次

select 姓名,max(case 课程 when '数学' then 分数 else 0 end) [数学], max(case 课程 when '物理' then 分数 else 0 end) [物理] , 

--这个是第三次

select 姓名,max(case 课程 when '数学' then 分数 else 0 end) [数学], max(case 课程 when '物理' then 分数 else 0 end) [物理] , max(case 课程 when '语文' then 分数 else 0 end) [语文] 

--这个的数量来自于

(select distinct 课程 from tb)--这里只有3们课程







create table tb(id int, value varchar(10))

insert into tb values(1, 'aa')

insert into tb values(1, 'bb')

insert into tb values(2, 'aaa')

insert into tb values(2, 'bbb')

insert into tb values(2, 'ccc')

go



create function dbo.f_str(@id int) returns varchar(100)

as

begin

    declare @str varchar(1000)

    set @str = ''

    select @str = @str + ',' + cast(value as varchar) from tb where id = @id

    set @str = right(@str , len(@str) - 1)

    return @str

end

go



--调用函数

select id , value = dbo.f_str(id) from tb group by id



drop function dbo.f_str

drop table tb





我只说一个地方 

select @str = @str + ',' + cast(value as varchar) from tb where id = @id 

你把这个看懂就明白了 

例如当@id=1 

select @str = @str + ',' + cast(value as varchar) from tb where id = 1 

把满足id=1的str值通过','累加 

当id是动态的就是1或者2...是当满足1的查询完了,把值付给str之后 

在查询满足2的直到所有的ID完为止 

这样明白了吧





本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ws_hgo/archive/2009/03/17/3999394.aspx
htl258_Tony 2010-05-11
  • 打赏
  • 举报
 回复 
----------------------------------------------------------------------------------

-- Author : htl258(Tony)

-- Date   : 2010-05-11 16:05:46

-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 

--          Jul  9 2008 14:43:34 

--          Copyright (c) 1988-2008 Microsoft Corporation

--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

-- Blog   : http://blog.csdn.net/htl258

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



--> 生成测试数据表: [tb]

IF OBJECT_ID('[tb]') IS NOT NULL

	DROP TABLE [tb]

GO

CREATE TABLE [tb] ([fEmpID] [int],[fDate] [datetime],[fShiftCode] [nvarchar](10),[fReasonCode] sql_variant)

INSERT INTO [tb]

SELECT '1','2010-01-01','A01',null UNION ALL

SELECT '1','2010-01-02','A01',null UNION ALL

SELECT '1','2010-01-03','A01',null UNION ALL

SELECT '1','2010-01-04','其他',null UNION ALL

SELECT '1','2010-01-05','A02',null UNION ALL

SELECT '2','2010-01-01','A01',null UNION ALL

SELECT '2','2010-01-02','休息',null UNION ALL

SELECT '2','2010-01-03','A03',null UNION ALL

SELECT '2','2010-01-04','A03',null UNION ALL

SELECT '2','2010-01-05','A03',null



--SELECT * FROM [tb]



-->SQL查询如下:

declare @s varchar(8000)

select @s=isnull(@s,'select [fEmpID]')

         +',max(case day(fdate) when '+ltrim(number)+' then fShiftCode else '''' end)['+ltrim(number)+']'

from master..spt_values 

where type='p' and number between 1 and 31

exec(@s+' from tb group by fEmpID') --大致这样，WHERE判断日期范围的条件自己加下

/*

fEmpID      1          2          3          4          5          6          7          8          9          10         11         12         13         14         15         16         17         18         19         20         21         22         23         24         25         26         27         28         29         30         31

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

1           A01        A01        A01        其他         A02                                                                                                                                                                                                                                                                                           

2           A01        休息         A03        A03        A03                                                                                                                                                                                                                                                                                           



(2 行受影响)

*/
Mr_Nice 2010-05-11
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 ldslove 的回复:]
SQL code
/*
标题：普通行列转换(version 2.0)
作者：爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间：2008-03-09
地点：广东深圳
说明：普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法，version 2.0增加sql server 2005的有关写法。

问题：假设有张学生成绩表(tb)如下:
姓……
[/Quote]

常见常新 啊....
wqmgxj 2010-05-11
  • 打赏
  • 举报
 回复
好快 回复内容太短了！
喜-喜 2010-05-11
  • 打赏
  • 举报
 回复 
'结果表字段数不固定的行列转换示例：'

use test

go

if object_id('test.dbo.tb1') is not null drop table tb1

-- 创建数据表

create table tb1

(

sid char(4),

学校 char(8),

班级 char(4)

)

go

--插入测试数据

insert into tb1 select '001','school1','one'

union all select '001','school2','one'

union all select '001','school3','two'

union all select '002','school1','one'

union all select '002','school2','one'

union all select '002','school3','two'

go

if object_id('test.dbo.tb2') is not null drop table tb2

-- 创建数据表

create table tb2

(

sid char(4),

名字 char(5),

性别 char(3)

)

go

--插入测试数据

insert into tb2 select '001','张三','男'

union all select '002','李四','男'

go

--代码实现



declare @str varchar(max)

select @str=(select ',学校'+ltrim(idd)+'=max(case when 学校='''+rtrim(学校)+''' then 学校 end)',

',班级'+ltrim(idd)+'=max(case when 学校='''+rtrim(学校)+''' then 班级 end)'

from (select idd =row_number()over(order by getdate()),* from (select distinct 学校 from tb1)t)tt for xml path(''))

exec('select sid'+@str+' from tb1 group by sid')



/*测试结果



sid 学校1 班级1 学校2 班级2 学校3 班级3

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

001 	school1 	one 	school2 	one 	school3 	two 

002 	school1 	one 	school2 	one 	school3 	two 



(2 行受影响)

*/
东那个升 2010-05-11
  • 打赏
  • 举报
 回复 
/*

标题：普通行列转换(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
【一本通】行列转换问题
矩阵可以认为是N*M的二维数组。现在有一个巨大但稀疏的矩阵。 N，M范围是：1≤ N，M ≤100000，有K个位置有数据,K的范围是，1≤K100000。 矩阵输入的方式是从上到下(第1行到第N行)、从左到右(从第1列到第M列)扫描，记录有数据的坐标位置(x,y)和值(v)。这是按照行优先的方式保存数据的。现在要求按照列优先的方式输出数据,即从左到右、从上到下扫描,输出有数据的坐标和数值。
SQL Server行列转换
本文主要分析了SQL Server数据库中行列转换函数PIVOT和UNPIVOT的使用方法。
【Hive SQL 每日一题】行列转换
列，分别代表每名同学对应的各科成绩，我们只需要在统计时加入判断条件即可，每列只固定求某科的成绩，如果不是则用。解决问题的方式有许多种，但往往我们需要去注重学习解决问题的思路，希望本文对你有所帮助。行转列操作，其实就是将行数据通过列的方式进行查询展示而已，这里行转列数据共有。会返回两个参数，其中第一个参数表示索引，第二个参数才是其对应的值。的方式，将表的一列扩展到多行，从而完成列转行的需求。在只使用一个扩展函数时，并不会产生笛卡尔积，如上所示。条件来进行过滤，取到对应的数据。在形成笛卡尔积后，我们可以使用。
【SQL应知应会】行列转换（一）• MySQL版
1.准备操作2.行转列1.1为何进行行转列？1.2 行转列有两个意思：1.表内的行转列 2.跨表的行转列3.行转列的思路：行变少，列变多3.1 如何进行行转列：增加字段，进行聚合(行变少)4.行转列的实操4.1 通用的行转列(Mysql和Oracle都能用)4.1.1想在结果中加入学生名字4.1.1.1加入名字的方法1：
SQL 案例分析：数据报表行列转换
本文介绍了如何利用CASE表达式和聚合函数实现数据报表中的行列转换功能。
应用实例

27,582

社区成员

68,544

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章