sql - Join two Tables with one have columns to rows -

i have table structure:

table 1: id(pk) | <other columns>               1 | otherdata               2 | otherdata 

the other table, it´s list of documents (pdf,doc,etc) url download. these documents stored in network.

table 2: id | iddoc |    linkdoc    | info           1 |   1   | 'http://url1' | 'info1'           1 |   2   | 'http://url2' | 'info2'           2 |   1   | 'http://url3' | 'info3' 

id foreign key table 1,iddoc foreign key 3rd table (below) describe document type:

table 3: iddoc | name             1  | 'contract'             2  | 'notification' 

i need generate query join these tables , similar structure

id | <somecollumstable1> | namedesc1 | nameurl1 | ... | namedesc2 | nameurl2 

example output:

  id | <somecollumstable1> | contractdesc | contracturl   | notificationdesc | notificationurl   1  | otherdata           | 'info1'      | 'http://url1' | 'info2'          | 'http://url2'   2  | otherdata           | 'info3'      | 'http://url3' | ''               | '' 

i.e. generate many pairs desc/url many records exits in "table3". sample data have 2 documents types , generate 4 columns.

currently have subquerys each desired document, sounds inefficient me, query big , new documents add in "table3" need change in whole query , need adjust clause indicate why iddoc´s need. (using in clause)

or better manipulate in application (winforms/vb.net)?

the app generate report in excel format.

please try below query:

declare @qu nvarchar(max), @pcol nvarchar(max) select   @pcol= coalesce(@pcol + ',','') + type  (select name+n'url' type t3 union select name+n'desc' type t3 )  set @qu=n'select id,b,c,'+ @pcol + n'   (     select t1.id, t1.b,t1.c, t2.linkdoc data,t3.name +n''url'' type t1 left join t2 on t1.id=t2.id     left join t3 on t2.iddoc=t3.iddoc     union     select t1.id, t1.b, t1.c, t2.info data, t3.name +n''desc'' type t1 left join t2 on t1.id=t2.id     left join t3 on t2.iddoc=t3.iddoc   )s   pivot   (   max(data)      type in ('+@pcol +n')) piv' exec sp_executesql @qu 

here's sql fiddle : http://sqlfiddle.com/#!6/9fb46/1

edit:explanation added

so using pivot, except pivot can done on single column , in our case, either on url or desc columns. need both these columns pivoted,so used union both single column data below

select t1.id, t1.b,t1.c, t2.linkdoc data,t3.name +n'url' type t1 left join t2 on t1.id=t2.id left join t3 on t2.iddoc=t3.iddoc union select t1.id, t1.b, t1.c, t2.info data, t3.name +n'desc' type t1 left join t2 on t1.id=t2.id left join t3 on t2.iddoc=t3.iddoc 

which used in pivot :

select id,b,c,[contracturl],[contractdesc],[notificationurl],[notificationdesc]   (       select t1.id, t1.b,t1.c, t2.linkdoc data,t3.name +n'url' type t1 left join t2 on t1.id=t2.id   left join t3 on t2.iddoc=t3.iddoc   union   select t1.id, t1.b, t1.c, t2.info data, t3.name +n'desc' type t1 left join t2 on t1.id=t2.id   left join t3 on t2.iddoc=t3.iddoc   )s   pivot   (   max(data)      type in ([contracturl],[contractdesc],[notificationurl],[notificationdesc])   )piv  

now making dynamic calculated unique columns table t3

select name+n'url' type t3 union select name+n'desc' type t3