Join two Tables with one have columns to rows

Question

I have a table with this structure:

Table 1: ID(PK) | <other columns>
              1 | otherdata
              2 | otherdata

the other table, It´s a list of documents (PDF,DOC,etc) with a URL to download. these documents is stored in my network.

Table 2: ID | IDDOC |    LINKDOC    | INFO
          1 |   1   | 'http://URL1' | 'Info1'
          1 |   2   | 'http://URL2' | 'Info2'
          2 |   1   | 'http://URL3' | 'Info3'

ID is the foreign key for Table 1,IDDOC is a foreign key to a 3rd table (below) that describe the document type:

Table 3: IDDOC | Name
            1  | 'Contract'
            2  | 'Notification'

I need to generate a query to join these tables and get a 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 as many records exits in "Table3". the sample data have 2 documents types and generate 4 columns.

Currently i have subquerys to each desired document, but sounds very inefficient for me, the query is big and new documents i add in "Table3" need change in the whole query and need to just adjust the Where clause to indicate why IDDOC´s need. (using a IN clause)

Or its better to manipulate this in my application (winforms/vb.net)?

The App generate a report in EXCEL format.

Please edit your question with sample data and desired result. It will help clarify what you are trying to do. — Gordon Linoff, Jul 27 '15 at 12:09
Sounds like two easy inner joins, did you already tried that? — kl78, Jul 27 '15 at 12:24
It's probably better to do this with a PIVOT in Excel. But if you want to do it in SQL, google SQL DYNAMIC PIVOT for examples. — Tab Alleman, Jul 27 '15 at 15:20
Sample data added. Using a simple inner join also generate duplicate results, i.e. many "Table1" records for every document type. EXCEL pivot is undesirable because the client cannot use advanced functions. — joao godinho, Jul 27 '15 at 20:31
Possible Duplicate: http://stackoverflow.com/questions/10319562/sql-pivot-with-string The answer there doesn't have the PIVOT automatically change for changes in Table 3. The only way to do that is dynamic SQL, with the incumbent security issues. — mwwaters, Jul 29 '15 at 06:12
@joaogodinho I posted an answer using a dyamic pivot. Also posted a pivot. Please check. This works! — DhruvJoshi, Jul 29 '15 at 18:11

DhruvJoshi · Answer 1 · 2015-07-29T18:24:32.727

Please try the below query:

DECLARE @qu NVARCHAR(MAX), @pcol NVARCHAR(MAX)
SELECT   @pcol= COALESCE(@pcol + ',','') + type FROM
 (SELECT Name+N'URL' AS type FROM t3 UNION SELECT Name+N'Desc' AS type FROM t3 ) A

SET @qu=N'Select ID,b,c,'+ @pcol + N' FROM
  (
    SELECT t1.ID, t1.b,t1.c, t2.linkdoc as data,t3.Name +N''URL'' as Type FROM 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 as data, t3.Name +N''Desc'' as Type FROM t1 LEFT JOIN t2 ON t1.ID=t2.ID
    LEFT JOIN t3 ON t2.iddoc=t3.iddoc
  )S
  PIVOT
  (
  MAX(data) 
    FOR Type IN ('+@pcol +N')) AS piv'
EXEC sp_executesql @qu

Here's a sql fiddle for you : http://sqlfiddle.com/#!6/9fb46/1

EDIT:explanation added

So basically I am using PIVOT, except that PIVOT can be done on a single column , in our case, either on URL or Desc columns. But we need both these columns to be pivoted,so I used UNION to get both into a single column data like below

SELECT t1.ID, t1.b,t1.c, t2.linkdoc as data,t3.Name +N'URL' as Type FROM 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 as data, t3.Name +N'Desc' as Type FROM t1 LEFT JOIN t2 ON t1.ID=t2.ID
LEFT JOIN t3 ON t2.iddoc=t3.iddoc

which I then used in PIVOT like this :

Select ID,b,c,[ContractURL],[ContractDesc],[NotificationURL],[NotificationDesc]
FROM
  (
      SELECT t1.ID, t1.b,t1.c, t2.linkdoc as data,t3.Name +N'URL' as Type FROM 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 as data, t3.Name +N'Desc' as Type FROM t1 LEFT JOIN t2 ON t1.ID=t2.ID
  LEFT JOIN t3 ON t2.iddoc=t3.iddoc
  )S
  PIVOT
  (
  MAX(data) 
    FOR Type IN ([ContractURL],[ContractDesc],[NotificationURL],[NotificationDesc])
  )piv

Now for making this dynamic I calculated all the unique columns from table t3 like

SELECT Name+N'URL' AS type FROM t3 UNION SELECT Name+N'Desc' AS type FROM t3

Before I upvote, can you add some more explanation to the code? Personally I've got a pretty good grip on what it's doing, however those that are less familiar with SQL may not. — user2366842, Jul 29 '15 at 18:13

Join two Tables with one have columns to rows

1 Answers1