SQL 行列转换联查 求教高手
表A
FieldId Type FieldCode FieldName sampleNo
--------------- ----------- ------------------ ----------------- -------
GIS_Country 3 NULL Country 01
GIS_Province 3 NULL Province 01
GIS_City 3 NULL City 01
GIS_Area 3 NULL Area 01
SHKSA01 1 GISInt NULL 01
表B
sampleNo Country Province City Area GISInt
----------- --------- --------- -------- ------- ---------
01 china fujian xiamen jimei 1
说明：表B中出现的字段根据表A中 FieldCode 、 FieldName的值来决定。
表A和表B通过sampleNo字段可以关联
如何通过SQL语句查询得到下面的结果集为：
FieldId Type colName sampleNo value
--------------- ----------- ----------------- ---------- ---------
GIS_Country 3 Country 01 china
GIS_Province 3 Province 01 fujian
GIS_City 3 City 01 xiamen
GIS_Area 3 Area 01 jimei
SHKSA01 1 GISInt 01 1
注： 当type = 1 时 colName 取表A中的FieldCode字段的值
当type = 3 时 colName 取表A中的FieldName字段的值
SQL
[解决办法]
with a1 (FieldId,Type,FieldCode,FieldName,sampleNo) as
(
select 'GIS_Country',3,NULL,'Country','01' union all
select 'GIS_Province',3,NULL,'Province','01' union all
select 'GIS_City',3,NULL,'City','01' union all
select 'GIS_Area',3,NULL,'Area','01' union all
select 'SHKSA01',1,'GISInt',NULL,'01'
)
,a2 (sampleNo,Country,Province,City,Area,GISInt) as
(
select '01','china','fujian','xiamen','jimei','1'
)
select a.FieldId,a.Type,case when a.Type=1 then a.FieldCode else a.FieldName end colName,
a.sampleNo,
case when a.FieldId='GIS_Country' then b.Country
when a.FieldId='GIS_Province' then b.Province
when a.FieldId='GIS_City' then b.City
when a.FieldId='GIS_Area' then b.Area
else b.GISInt
end value
from a1 a
inner join a2 b on a.sampleNo=b.sampleNo
[解决办法]
if not object_id('a') is null drop table a
create table a(fieldid char(50),type char(10),fieldcode char(50),fieldname char(50),sampleno char(10))
insert a select 'GIS_Country', '3' , NULL , 'Country', '01'
insert a select 'GIS_Province' , '3' , NULL, 'Province', '01'
insert a select 'GIS_City ' , '3' , NULL , 'City' , '01'
insert a select 'GIS_Area' , '3', NULL , 'Area' , '01'
insert a select 'SHKSA01' , '1', 'GISInt' , NULL , '01'
if not object_id('b') is null drop table b
create table b(sampleno char(10),country char(50),province char(50),city char(50),area char(50),gisint char(10))
insert b select '01' ,'china', 'fujian', 'xiamen', 'jimei', '1'
go
with c as (
select fieldid,type,case when type=3 then fieldname else fieldcode end as colname,sampleno from a)
select fieldid,type,colname,b.sampleno,
case when colname='country' then country
when colname='province' then province
when colname='city' then city
when colname='area' then area
when colname='gisint' then gisint
end as [value]
from b left join c on b.sampleno=c.sampleno
/*
fieldid type colname sampleno value
-------------------------------------------------- ---------- -------------------------------------------------- ---------- --------------------------------------------------
GIS_Country 3 Country 01 china
GIS_Province 3 Province 01 fujian
GIS_City 3 City 01 xiamen
GIS_Area 3 Area 01 jimei
SHKSA01 1 GISInt 01 1
(5 行受影响)
*/