列数不定的行列变化等(SQL Server)

今天的某个case是客户希望进行行列变换，列数Vx不定（因为这个条件所以无法用PIVOT函数）

 

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

SiteNo SubNo MV Date
—— ——– — ————
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]
—— ——— ———– ——— ———- ———– ———– ———-
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
—— ——– — ————
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]
—— ——— ———– ——— ———- ———– ———– ———- ———— —————-
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都还是不定的，这个的判断不是一点点复杂。。。

感觉客户的设计已经违反了第一范式，还是强烈建议其先修改数据库设计算了。。。
不过万一客户不肯。。。好吧，我只能陪他吐血了。