数据库横表变成竖表

wangdg520 2010-04-23 09:05:11
大家好，我现在有一个表：如下

Code dDate Num1 Num2 Num3
WC_PAINTING 2010-04-21 1504.00 960.00 156.66
WC_PAINTING 2010-04-22 1507.00 960.00 156.98
......

表数据有可能是很多行

我想实现转换成的表如下
dDate1 dDate2 dDate3 ....
WC_PAINTING WC_PAINTING ...
2010-04-21 2010-04-22 ...
1504.00 1507.00 ...
960.00 960.00 ...
156.66 156.98 ...


请高手指点，谢谢
...全文
592 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
wangdg520 2010-04-23
  • 打赏
  • 举报
 回复
8楼的兄弟很好！我应该也给你分的！你很负责！谢谢！
我的QQ：175815383
希望能跟你多学习
chuifengde 2010-04-23
  • 打赏
  • 举报
 回复 
CREATE TABLE #b(Code varchar(20),dDate char(10),Num1 DECIMAL(20,2),Num2 DECIMAL(20,2),Num3 DECIMAL(20,2),id int)

DECLARE @a table(Code varchar(20),dDate char(10),Num1 DECIMAL(20,2),Num2 DECIMAL(20,2),Num3 DECIMAL(20,2))

insert @a SELECT 'WC_PAINTING','2010-04-21', 1504.00, 960.00, 156.66 

union all select 'WC_PAINTING', '2010-04-22', 1507.00, 960.00, 156.98 



INSERT #b

SELECT code,dDate,sum(num1) x,sum(Num2) y,sum(Num3) z,

(SELECT count(1) FROM @a WHERE code=a.code AND dDate<=a.dDate)

FROM @a a GROUP BY Code,dDate



DECLARE @sql1 varchar(1000)

DECLARE @sql2 varchar(1000)

DECLARE @sql3 varchar(1000)

DECLARE @sql4 varchar(1000)

DECLARE @sql5 varchar(1000)



SELECT @sql1=isnull(@sql1+',','')+' max(case when id='+ltrim(id)+' then ltrim(Num1) else null end) [dDate'+ltrim(id)+']' FROM #b 

SELECT @sql2=isnull(@sql2+',','')+' max(case when id='+ltrim(id)+' then ltrim(Num2) else null end) [dDate'+ltrim(id)+']' FROM #b 

SELECT @sql3=isnull(@sql3+',','')+' max(case when id='+ltrim(id)+' then ltrim(Num3) else null end) [dDate'+ltrim(id)+']' FROM #b 

SELECT @sql4=isnull(@sql4+',','')+' max(case when id='+ltrim(id)+' then Code else null end) [dDate'+ltrim(id)+']' FROM #b 

SELECT @sql5=isnull(@sql5+',','')+' max(case when id='+ltrim(id)+' then dDate else null end) [dDate'+ltrim(id)+']' FROM #b 





exec('

select '+@sql4 +' from #b group by Code union all 

select '+@sql5 +' from #b group by Code union all 

select '+@sql1 +' from #b group by Code union all 

select '+@sql2 +' from #b group by Code union all 

select '+@sql3 +' from #b group by Code'

)



drop table #b

--result

/*dDate1                                   dDate2                                   

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

WC_PAINTING                              WC_PAINTING

2010-04-21                               2010-04-22

1504.00                                  1507.00

960.00                                   960.00

156.66                                   156.98

*/
wangdg520 2010-04-23
  • 打赏
  • 举报
 回复
恩，对，就是这样，谢谢！！！
dawugui 2010-04-23
  • 打赏
  • 举报
 回复
你这个是数据90度转换.参考如下内容:
/*

数据库中tb表格如下

 

月份    工资   福利  奖金

1月     100    200   300

2月     110    210   310

3月     120    220   320

4月     130    230   330



我想得到的结果是



项目   1月    2月  3月  4月

工资   100    110  120  130

福利   200    210  220  230

奖金   300    310  320  330



就是说完全把表格的行列颠倒，有点像那种旋转矩阵，请问如何用sql 语句实现？

*/



if exists (select * from dbo.sysobjects

where id = object_id(N'[dbo].[p_zj]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[p_zj]

GO

/*--行列互换的通用存储过程(原著:邹建):将指定的表,按指定的字段进行行列互换*/



create proc p_zj

       @tbname sysname, --要处理的表名

       @fdname sysname, --做为转换的列名

       @new_fdname sysname='' --为转换后的列指定列名

as

declare @s1 varchar(8000) , @s2 varchar(8000),

        @s3 varchar(8000) , @s4 varchar(8000),

        @s5 varchar(8000) , @i varchar(10)

select @s1 = '' , @s2 = '' , @s3 = '' , @s4 = '' , @s5 = '' , @i = '0'

select @s1 = @s1 + ',@' + @i + ' varchar(8000)',

       @s2 = @s2 + ',@' + @i + '=''' + case isnull(@new_fdname , '') when '' then ''

       else @new_fdname + '=' end + '''''' + name + '''''''',

       @s3 = @s3 + 'select @' + @i + '=@' + @i + '+'',['' + [' + @fdname + 

       ']+'']=''+cast([' + name + '] as varchar) from [' + @tbname + ']',

       @s4 = @s4 + ',@' + @i + '=''select ''+@' + @i,

       @s5 = @s5 + '+'' union all ''+@' + @i,

       @i=cast(@i as int)+1

from syscolumns

where object_id(@tbname)=id and name<>@fdname



select @s1=substring(@s1,2,8000),

       @s2=substring(@s2,2,8000),

       @s4=substring(@s4,2,8000),

       @s5=substring(@s5,16,8000)

exec('declare ' + @s1 + 'select ' + @s2 + @s3 + 'select ' + @s4 + '

exec(' + @s5 + ')')

go



--用上面的存储过程测试：



create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)

insert Test 

select '1月',100,200,300 union all

select '2月',110,210,310 union all

select '3月',120,220,320 union all

select '4月',130,230,330

go



exec p_zj 'Test', '月份' , '项目'



drop table Test

drop proc p_zj



/*

项目   1月         2月         3月         4月          

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

福利   200         210         220         230

工资   100         110         120         130

奖金   300         310         320         330



（所影响的行数为 3 行）

*/



/*

静态写法(SQL2005)

*/

--测试环境

create table Test(月份 varchar(4), 工资 int, 福利 int, 奖金 int)

insert Test

select '1月',100,200,300 union all

select '2月',110,210,310 union all

select '3月',120,220,320 union all

select '4月',130,230,330

go

--测试语句

SELECT * FROM 

(

  SELECT 考核月份,月份,金额 FROM 

     (SELECT 月份, 工资, 福利, 奖金 FROM Test) p

  UNPIVOT

     (金额 FOR 考核月份 IN (工资, 福利, 奖金))AS unpvt

) T

PIVOT

(MAX(金额)  FOR 月份 in ([1月],[2月],[3月],[4月]))AS pt



--测试结果



/*

考核月份  1月     2月      3月     4月

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

福利200210220230

工资100110120130

奖金300310320330

*/



--删除环境

Drop table Test
sych888 2010-04-23
  • 打赏
  • 举报
 回复
一、行转列
if not object_id('class') is null
drop table class
go
create table class(sname varchar(10),cname varchar(10),score int)

Insert class
select N'张三',N'语文',78 union all
select N'张三',N'数学',87 union all
select N'张三',N'英语',82 union all
select N'张三',N'物理',90 union all
select N'李四',N'语文',65 union all
select N'李四',N'数学',77 union all
select N'李四',N'英语',65 union all
select N'李四',N'物理',85

--1、静态方法
select
sname,
[数学]=max(case when cname='数学' then score else 0 end),
[物理]=max(case when cname='物理' then score else 0 end),
[英语]=max(case when cname='英语' then score else 0 end),
[语文]=max(case when cname='语文' then score else 0 end)
from
Class
group by sname
--2、利用函数
create function GetScore(@sname varchar(10),@cname varchar(10))
returns int
as
begin
declare @score int
set @score=0
select @score=score from class where sname=@sname and cname=@cname
return @score
end

select sname,语文=dbo.GetScore(sname,'语文'),数学=dbo.GetScore(sname,'数学'),
物理=dbo.GetScore(sname,'物理'),英语=dbo.GetScore(sname,'英语')
from class
group by sname
--3、动态
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename(cname)+'=max(case when cname='+quotename(cname,'''')+' then score else 0 end)'
from Class group by cname
select @s
exec('select sname'+@s+' from Class group by sname')
--quotename使函数中的输入成为一个有效的标示符
--4、利用pivot对表进行透视
select *
from
Class
pivot
(max([Score]) for cname in([数学],[物理],[英语],[语文]))b
htl258_Tony 2010-04-23
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 wangdg520 的回复:]
不一样的，我得到的表的列是根据查询日期来的！行可以确定！
[/Quote]
那你提供完整的测试数据，我们就提供给你完整的测试结果。
wangdg520 2010-04-23
  • 打赏
  • 举报
 回复
不一样的，我得到的表的列是根据查询日期来的！行可以确定！
--小F-- 2010-04-23
  • 打赏
  • 举报
 回复 
/*

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

姓名 语文 数学 物理

张三 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。



--------------------
不同数据库表结构的转化
通过学习PowerDesigner工具，学习概念模型，物理模型，面向对象模型，业务模型，以及不同数据库表结构的转化。
如何将数据库中横表转为竖表
有如下表kuai； id name subject score 1 张三 语文 100 2 张三 数学 90 3 张三 英语 95 4 李四 语文 98 5 李四 数学 80 6 李四 英语 75 用SQL语句实现如下查询结果; 姓名 语文 数学 英语 张三 100 90 95 李四 98 80...
mysql数据横表变成竖表_MySQL中横表和竖表相互转换
一 竖表转横表1. 首先创建竖表create table student (id varchar(32) primary key,name varchar (50) not null,subject varchar(50) not null,result int);2. 插入数据insert into student (id, name, subject, ...
数据库设计---关于建表的时候选择横表和竖表(纵表)的一点思考
数据库设计---关于建表的时候选择横表和竖表(纵表)的一点思考 本文出处：http://www.cnblogs.com/wy123/p/6677073.html 在做数据统计类数据库设计的时候，在考虑数据存储的时候，经常会遇到逻辑上同一个...
mysql竖表转横表，横表转竖表
--竖表转行表使用pivot（这个是sqlserver，mysql不可以使用）第二行数据，课程值为‘数学’时,then'数学'变为70，起列名为数学。无论是横转竖，还是竖转横，原表都不变还是原来的样子。score2，此表为横表。
MS-SQL Server

34,590

社区成员

254,589

社区内容

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

加载中

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

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

+ 用AI写文章