把列变成行!高手请进

flyfly2008 2011-07-06 02:13:52
如表:worktable

prod dept finishqty startdate enddate
pro1 A 1000 2011-06-01 2011-06-02
pro1 B 800 2011-06-04 2011-06-05
pro1 C 600 2011-06-06 2011-06-07
pro2 A 1200 2011-06-10 2011-06-11
pro2 B 1000 2011-06-13 2011-06-15
pro2 C 500 2011-06-15 2011-06-18

现要把它变成横向显示

prod deptAqty Astartdate Aenddate deptBqty Bstartdate Benddate deptCqty Cstartdate Cenddate
pro1 1000 2011-06-01 2011-06-02 800 2011-06-04 2011-06-05 600 2011-06-06 2011-06-07
pro2 1200 2011-06-10 2011-06-11 1000 2011-06-13 2011-06-15 500 2011-06-15 2011-06-18
...全文
83 6 打赏 收藏 转发到动态 举报
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
往前'走 2011-07-06
  • 打赏
  • 举报
回复
晕倒,我还没贴出来,分就被抢完了。太不划算了。
往前'走 2011-07-06
  • 打赏
  • 举报
回复
--创建表
if (object_id('tb_a')is not null)
drop table tb_a;
go
create table tb_a
(
prod varchar(10),
dept char(1),
finishqty int,
startdate smalldatetime,
enddate int
);
go
--修改表结构
alter table tb_a
alter column enddate smalldatetime;
go
--插入数据
insert tb_a
select 'pro1','A',1000,'2011-06-01','2011-06-02'
union
select 'pro1','B',800,'2011-06-04','2011-06-05'
union
select 'pro1','C',600,'2011-06-06','2011-06-07'
union
select 'pro2','A',1200,'2011-06-10','2011-06-11'
union
select 'pro2','B',1000,'2011-06-13','2011-06-15'
union
select 'pro2','C',500,'2011-06-15','2011-06-18';
go
select * from tb_a
--行转列
select prod,sum(case dept when 'A' then finishQty end) as petyAQty,
max(case dept when 'A' then startDate end)as AStartDate,
max(case dept when 'A' then endDate end)as AEndDate,

sum(case dept when 'B' then finishQty end) as petyBQty,
max(case dept when 'B' then startDate end)as BStartDate,
max(case dept when 'B' then endDate end)as BEndDate,

sum(case dept when 'C' then finishQty end) as petyCQty,
max(case dept when 'C' then startDate end)as CStartDate,
max(case dept when 'C' then endDate end)as CEndDate
from tb_a group by prod

--演化之后
declare @sql nvarchar(1500);
set @sql='select prod,';
select @sql=@sql+'sum(case dept when '''+dept+''' then finishQty end) as pety'+dept
+'Qty,max(case dept when '''+dept+''' then startDate end)as '+dept+'StartDate,max(case dept when '''+dept+''' then endDate end)as '+dept+'EndDate,'
from tb_a group by dept
set @sql=left(@sql,len(@sql)-1)+' from tb_a group by prod;';
--print @sql--查看一下要执行的代码
execute (@sql);
leisure_cool 2011-07-06
  • 打赏
  • 举报
回复
楼上几位,你们抢分的吧,太快了。
chuanzhang5687 2011-07-06
  • 打赏
  • 举报
回复
create table a
(
prod varchar(20),
dept varchar(20),
finishqty int,
startdate datetime ,
enddate datetime
)

insert into a values('pro1' ,'A' ,1000 ,'2011-06-01', '2011-06-02')
insert into a values('pro1' ,'B' ,800 ,'2011-06-04', '2011-06-05')
insert into a values('pro1' ,'C' ,600 ,'2011-06-06', '2011-06-07')
insert into a values('pro2' ,'A' ,1200 ,'2011-06-10', '2011-06-11')
insert into a values('pro2' ,'B' ,1000 ,'2011-06-13', '2011-06-15')
insert into a values('pro2' ,'C' ,500 ,'2011-06-15', '2011-06-18')

select prod,
sum(case when dept='A' then finishqty end) as deptAqty,
max(case when dept='A' then startdate end) as Astartdate,
max(case when dept='A' then enddate end) as Aenddate,
sum(case when dept='B' then finishqty end) as deptBqty,
max(case when dept='B' then startdate end) as Bstartdate,
max(case when dept='B' then enddate end) as Benddate,
sum(case when dept='C' then finishqty end) as deptCqty,
max(case when dept='C' then startdate end) as Cstartdate,
max(case when dept='C' then enddate end) as Cenddate
from a group by prod
中国风 2011-07-06
  • 打赏
  • 举报
回复
use Tempdb
go
--> -->

if not object_id(N'Tempdb..#worktable') is null
drop table #worktable
Go
Create table #worktable([prod] nvarchar(4),[dept] nvarchar(1),[finishqty] int,[startdate] Datetime,[enddate] Datetime)
Insert #worktable
select N'pro1',N'A',1000,'2011-06-01','2011-06-02' union all
select N'pro1',N'B',800,'2011-06-04','2011-06-05' union all
select N'pro1',N'C',600,'2011-06-06','2011-06-07' union all
select N'pro2',N'A',1200,'2011-06-10','2011-06-11' union all
select N'pro2',N'B',1000,'2011-06-13','2011-06-15' union all
select N'pro2',N'C',500,'2011-06-15','2011-06-18'
Go
declare @s nvarchar(4000)
set @s=''
Select
@s=@s+','+quotename('dept'+[dept]+N'Qty')+'=max(case when [dept]='''+[dept]+''' then [finishqty] end)'
+','+quotename([dept]+N'startdate')+'=max(case when [dept]='''+[dept]+''' then convert(varchar(10),[startdate],120) end)'
+','+quotename([dept]+N'startdate')+'=max(case when [dept]='''+[dept]+''' then convert(varchar(10),[enddate],120) end)'
from #worktable
group by [dept]
exec('select [prod]'+@s+' from #worktable group by [prod] ')

/*
prod deptAQty Astartdate Astartdate deptBQty Bstartdate Bstartdate deptCQty Cstartdate Cstartdate
pro1 1000 2011-06-01 2011-06-02 800 2011-06-04 2011-06-05 600 2011-06-06 2011-06-07
pro2 1200 2011-06-10 2011-06-11 1000 2011-06-13 2011-06-15 500 2011-06-15 2011-06-18
*/
-晴天 2011-07-06
  • 打赏
  • 举报
回复
create table worktable(prod nvarchar(10),dept nvarchar(10),finishqty int,startdate datetime,enddate datetime)
insert into worktable select 'pro1','A',1000,'2011-06-01','2011-06-02'
insert into worktable select 'pro1','B',800,'2011-06-04','2011-06-05'
insert into worktable select 'pro1','C',600,'2011-06-06','2011-06-07'
insert into worktable select 'pro2','A',1200,'2011-06-10','2011-06-11'
insert into worktable select 'pro2','B',1000,'2011-06-13','2011-06-15'
insert into worktable select 'pro2','C',500,'2011-06-15','2011-06-18'
go
select prod,
sum(case when dept='A' then finishqty end) as deptAqty,
max(case when dept='A' then startdate end) as Astartdate,
max(case when dept='A' then enddate end) as Aenddate,
sum(case when dept='B' then finishqty end) as deptBqty,
max(case when dept='B' then startdate end) as Bstartdate,
max(case when dept='B' then enddate end) as Benddate,
sum(case when dept='C' then finishqty end) as deptCqty,
max(case when dept='C' then startdate end) as Cstartdate,
max(case when dept='C' then enddate end) as Cenddate
from worktable group by prod
/*
prod deptAqty Astartdate Aenddate deptBqty Bstartdate Benddate deptCqty Cstartdate Cenddate
---------- ----------- ----------------------- ----------------------- ----------- ----------------------- ----------------------- ----------- ----------------------- -----------------------
pro1 1000 2011-06-01 00:00:00.000 2011-06-02 00:00:00.000 800 2011-06-04 00:00:00.000 2011-06-05 00:00:00.000 600 2011-06-06 00:00:00.000 2011-06-07 00:00:00.000
pro2 1200 2011-06-10 00:00:00.000 2011-06-11 00:00:00.000 1000 2011-06-13 00:00:00.000 2011-06-15 00:00:00.000 500 2011-06-15 00:00:00.000 2011-06-18 00:00:00.000

(2 行受影响)

*/
go
drop table worktable

34,575

社区成员

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

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