|
|
  /**//*字段表*/
 create table D_InsuranceField
(
IF_ID int primary key identity(1,1),
IF_Name varchar(50),
IF_ILID int
)
insert into D_InsuranceField values('checkColumn',1)
insert into D_InsuranceField values('isHasNextLayer',1)
insert into D_InsuranceField values('layers',1)
insert into D_InsuranceField values('inputConfig',1)
insert into D_InsuranceField values('parentId',1)
insert into D_InsuranceField values('carRegion',1)
insert into D_InsuranceField values('carType',1)
insert into D_InsuranceField values('carSeries',1)
insert into D_InsuranceField values('carBrand',1)
insert into D_InsuranceField values('carModel',1)
insert into D_InsuranceField values('price',1)
/**//*保險數據表*/
create table D_InsuranceData
(
ID_ID int primary key identity(1,1),
D_IFID int,
D_Value varchar(50),
InsuranceCode varchar(50)
)
truncate table D_InsuranceData
insert into D_InsuranceData values(1,'*1','A001')
insert into D_InsuranceData values(2,'1','A001')
insert into D_InsuranceData values(3,'1','A001')
insert into D_InsuranceData values(4,'xxxxxxxxxX','A001')
insert into D_InsuranceData values(5,1,'A001')
insert into D_InsuranceData values(6,'北京','A001')
insert into D_InsuranceData values(7,'自用汽車','A001')
insert into D_InsuranceData values(8,'奧迪','A001')
insert into D_InsuranceData values(9,'奧迪A6','A001')
insert into D_InsuranceData values(10,'奧迪A6L2.6','A001')
insert into D_InsuranceData values(11,'550000','A001')
insert into D_InsuranceData values(1,'*11','A002')
insert into D_InsuranceData values(2,'11','A002')
insert into D_InsuranceData values(3,'11','A002')
insert into D_InsuranceData values(4,'xxxxxxxxx2X','A002')
insert into D_InsuranceData values(5,2,'A002')
insert into D_InsuranceData values(6,'北京2','A002')
insert into D_InsuranceData values(7,'自用汽車2','A002')
insert into D_InsuranceData values(8,'奧迪2','A002')
insert into D_InsuranceData values(9,'奧迪A62','A002')
insert into D_InsuranceData values(10,'奧迪A6L2.62','A002')
insert into D_InsuranceData values(11,'5500002','A002')
go
declare @s nvarchar(4000),@s2 nvarchar(4000)
set @s2=''
select @s=isnull(@s+',','')+quotename(IF_ID),@s2=@s2+','+quotename(IF_Name)+'='+quotename(IF_ID)
from D_InsuranceField
exec('select InsuranceCode'+@s2+'
from
(select D_IFID,D_Value,InsuranceCode from D_InsuranceData) a
pivot (max(D_Value) for D_IFID in('+@s+'))b')
輸出結果:
A001 *1 1 1 xxxxxxxxxX 1 北京 自用汽車 奧迪 奧迪A6 奧迪A6L2.6
A002 *11 11 11 xxxxxxxxx2X 2 北京2 自用汽車2 奧迪2 奧迪A62 奧迪A6L2.62
另一種方法:
declare @sql varchar(8000)
set @sql='select InsuranceCode'
select @sql=@sql+',['+IF_Name+']=max(case D_IFID when '''+ltrim(IF_ID)+''' then D_Value else '''' end)' from D_InsuranceField
set @sql=@sql+' from D_InsuranceData group by InsuranceCode'
exec(@sql)
|