SQL 队列转换联查求教高手

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 行受影响)

*/

[解决办法]
必须搞清楚“FieldId”与“Country Province City Area GISInt”的对应关系.或者规律.
[解决办法]
这里很多例子，请参考：
http://blog.csdn.net/hdhai9451/article/details/5026933

SQL 队列转换联查 求教高手

热点推荐

SQL 队列转换联查求教高手