根据条件将多行列不同的值提取到单行中

I have two tables Files, FileTypes

我有两个表文件，FileTypes

Files will hold the data of both files types internal and external for all clients i want to fetch a client's file in a single row with internal file path and extenral filepath.

文件将保存所有客户端的内部和外部两种文件类型的数据，我想在一行中使用内部文件路径和外部文件路径获取客户端的文件。

Below is the metadata

以下是元数据

create table filetypes (
  filetypeid int  primary key,
  typename varchar(20)
);

create table files (
  fileid int  primary key,
  filetypeid int,
  fiilepath nvarchar(200),
  client_id int
);

insert into files values (10, 1,'\testpath9\\InternalFoldername', 1);
insert into files values (11, 2,'\testpath2\\ExternalFoldername', 1);
insert into files values (12, 1,'\testpath5\\InternalFoldername', 2);
insert into files values (13, 2,'\testpath6\\ExternalFoldername', 3);


insert into filetypes values (1,'Internal');
insert into filetypes values (2,'External');

i want to have SQL to fetch results like below

我想让SQL获取如下结果

client_id   InternalPath                   ExternalPath
----------- ------------------------------ ------------------------------
1           \testpath9\\InternalFoldername \testpath2\\ExternalFoldername

1 个解决方案

#1

Try this sql statement,

试试这个sql语句，

 select  t1.internal_client as client_id, t1.InternalPath as InternalPath, 
 t2.ExternalPath as ExternalPath from 
 (select  client_id  as internal_client,fiilepath as InternalPath from files where filetypeid=1 ) t1 
 left join
 (select  client_id as external_client, fiilepath  as ExternalPath from files where filetypeid=2 )t2
 on t1.internal_client = t2.external_client 
 where t1.InternalPath IS NOT NULL and t2.ExternalPath IS NOT NULL