🚀 go-pugleaf

�����ij��case�ǿͻ�ϣ��������б任������Vx��������Ϊ������������޷���PIVOT
����

 

Source table:
==========

SiteNo SubNo MV Date
���� �����C �� ��������
001 SUB-028 V1 2007-03-11
001 SUB-028 V2 2007-05-21
001 SUB-028 V3 2007-06-22
001 SUB-028 V4 2007-07-11
001 SUB-028 V5 2007-08-05
001 SUB-028 V6 2007-09-28
002 SUB-010 V1 2007-04-22
002 SUB-010 V2 2007-05-22
002 SUB-010 V3 2007-06-05

 

Result table:
===========
SiteNo SubNo [V1] [V2] [V3] [V4] [V5] [V6]
���� ������ �������C ������ ������- �������C �������C ������-
001 SUB-028 2007-03-11 2007-05-21 2007-06-22 2007-07-11 2007-08-05 2007-09-28
002 SUB-010 2007-04-22 2007-05-22 2007-06-05

 �������ܼ򵥣������@sql��ִ��exec @sql�Ϳ����ˣ��������£�

declare @sql varchar(4000)
set @sql = 'select [SiteNo],[SubNo]'
select @sql = @sql + ',max(case [MV] when '''+[MV]+''' then [Date] end) as 
['+[MV]+']'
from (select distinct [MV] from [testtable2]) as a
select @sql = @sql+' from [testtable2] group by [SiteNo],[SubNo] order by 
[SiteNo]'
exec(@sql)
 
���鷳�����������棺
����SiteNo, SubNo, MV��ͬ��Date����ͬ�ģ�Ҫ�Բ�ͬ����ʾ������
SiteNo SubNo MV Date
���� �����C �� ��������
001 SUB-028 V1 2007-03-11
001 SUB-028 V1 2007-03-15
001 SUB-028 V1 2007-03-19
001 SUB-028 V2 2007-05-21
001 SUB-028 V3 2007-06-22
001 SUB-028 V4 2007-07-11
001 SUB-028 V5 2007-08-05
001 SUB-028 V6 2007-09-28
002 SUB-010 V1 2007-04-22
002 SUB-010 V2 2007-05-22
002 SUB-010 V3 2007-06-05


SiteNo SubNo [V1] [V1] [V1] [V2] [V3] [V4] [V5] [V6]
���� ������ �������C ������ ������- �������C �������C ������- �������� ������
����-
001 SUB-028 2007-03-11 2007-03-15 2007-03-19 2007-05-21 2007-06-22 
2007-07-11 2007-08-05 2007-09-28

 
ͬ������Vx������
����ƺ����˴����ʱ��û����취�ˡ��Ͼ�Vx������Ҫȡ��������ֵ��
֪�����Сʱ������������case���й����caseʱ�ŷ��֣������ظ��ı���Υ����ݿ�
���ԭ��ġ������ͻ���������������⡣
���ǽ����ΪV1_1, V1_2������
д�˴�룬��������һ��ʵ��д��ͷʹ�ˡ�������ȫ������Ч�ʣ�����ش�����ʱ�?
�Ѿ���ʵ��MVȫ���嵽��һ�С�

 

SELECT 
IDENTITY(int,1,1) as id
,[SiteNo]
,[SubNo]
,[MV]
,[Date]
into #temp
FROM [TestDB1].[dbo].[testtable2]
GO

select * from #temp

SELECT 
[mv]
,count([MV]) as mvnum
into #temp1
  FROM [TestDB1].[dbo].[testtable2]
  group by [SiteNo],[SubNo],[mv]
go

select * from #temp1

select identity(int,1,1) as id,mv,MAX(mvnum) as mvmax
into #temp2
from #temp1
group by mv
order by mv

select * from #temp2

create table #temp3 ([MV] [nchar](10) NULL)

declare @i int
set @i=1
declare @j int
select @j= [mvmax] from #temp2 where id=1
while ((select COUNT(*) from #temp2 where id=@i)>0)
begin
    insert into #temp3
    select rtrim([mv])+'_'+CAST(@j as NCHAR(2)) from #temp2 where id=@i
    if @j=1
    begin
        set @i=@i+1
        select @j=[mvmax] from #temp2 where id=@i
    end
    else
    begin
        set @j=@j-1
    end
end

select * into #temp4 from #temp3 order by MV

if exists (select * from sysobjects where name='testtable3')
drop table testtable3

declare @strsql   as   varchar(8000)   
set @strsql='create   table   testtable3 ([SiteNo] [nchar](10) NULL,[SubNo] 
[nchar](10) NULL'
select @strsql=@strsql+',['+[MV]+'] [nchar](10) NULL'
from (select rtrim([MV]) as [MV] from #temp4) as a
set @strsql=@strsql+')'
exec (@strsql)

--declare @siteno nchar(10)
--declare @subno nchar(10)
--declare @mv nchar(10)
--declare @v nchar(10)
--declare @date date
set @i=1
while ((select COUNT(*) from #temp where id=@i)>0)
begin
--select @siteno=[SiteNo],@subno=[SubNo], @mv=[MV],@date=[DATE] from #temp 
where id=@i
--set @v=SUBSTRING(@mv,0,3)
--if((select COUNT(*) from #temp3 where [SiteNo]=@siteno and [SubNo]=@subno 
and [MV]=@v
--insert into testtable3(siteno,subno,v1_1) values(@siteno,@subno,@date)

set @i=@i+1
end

drop table #temp
drop table #temp1
drop table #temp2
drop table #temp3
drop table #temp4


�����������һ��Ҫ���MV��ֵ���жϲ������У�V1_1,V1_2����V2_1�����V1_1��ֵ��
���嵽V1_2�� Vx_y��x��y�����Dz����ģ�������жϲ���һ��㸴�ӡ�����

�о��ͻ�������Ѿ�Υ���˵�һ��ʽ������ǿ�ҽ��������޸���ݿ�������ˡ�����
������һ�ͻ����ϡ������ðɣ���ֻ��������Ѫ�ˡ�


 
--
Welcome to Database Board
��ӭ������ݿ��
http://bbs.sjtu.edu.cn/bbsdoc?board=database

�� ��Դ:����ˮ˼Դ bbs.sjtu.edu.cn��[FROM: 207.46.55.28]