Multiple left joins with different values from same column

I need help with SQL code that takes different values from the same column with each iteration and puts them in the same row without multiplying rows.

Example:

In table DECLARATIONS there is data about imports that contains columns declaration_id(PK), date, weight, price, etc. In table DOCUMENTATION there is data about specific documents for each import that contains columns declaration_id(FK), doc_type, doc_number, etc.

In one import declaration there are multiple documents. For example, in table DOCUMENTATION, for one declaration_id there are up to 8 documents with doc_type = 'U44' with different doc_numbers.

I need to display data for one declaration in one row with one specific doc_type (declaration_id, date, doc_number1, doc_number2, doc_number3, doc_number4, doc_number5, doc_number6, doc_number7, doc_number8).

I tried multiple left joins on table DOCUMENTATION:

select A.decalaration_id, date, B.doc_number, B1.doc_number, B2.doc_number,
    B3.doc_number, B4.doc_number, B5.doc_number, B6.doc_number, B7.doc_number

from DECLARATION as A

left join DOCUMENTATION as B
on A.declaration_id = B.declaration_id 
and B.doc_type = 'U44'

left join DOCUMENTATION as B1
on A.declaration_id = B1.declaration_id 
and B1.doc_type = 'U44'
and B1.doc_number != B.doc_number

left join DOCUMENTATION as B2
on A.declaration_id = B2.declaration_id 
and B2.doc_type = 'U44'
and B1.doc_number != B.doc_number
and B2.doc_number not in (B.doc_number, B1.doc_number)

left join DOCUMENTATION as B3 on
A.declaration_id = B3.declaration_id 
and B3.doc_type = 'U44'
and B1.doc_number != B.doc_number
and B2.doc_number not in (B.doc_number, B1.doc_number)
and B3.doc_number not in (B.doc_number, B1.doc_number, B2.doc_number)
.
.
.

This does the job for the first row, but then it multiplies rows by taking B1.doc_number as B.doc_number and so on.

Solution

I suggest you read the variable number of document numbers into comma-separated strings. This gets you a very simple query that can deal with about any number of documents per declaration (until the string gets too large for SQL Server to handle):

SELECT 
  dec.declaration_id, 
  dec.date, 
  STRING_AGG (doc.doc_number, ', ') WITHIN GROUP (ORDER BY doc.doc_number) AS docs
FROM declaration AS d
LEFT JOIN documentation AS doc ON doc.declaration_id = dec.declaration_id 
                              AND doc.doc_type = 'U44'
GROUP BY dec.declaration_id, dec.date
ORDER BY dec.declaration_id;

If you must have separate columns, then you need to decide for a maximum number of documents to show, which might prove a wrong decision at some point in time. For this you'd number the documents per declaration, so you can easily get the documents 1 to n with conditional aggregation using MIN or MAX.

WITH
  numbered AS
  (
    SELECT
      dec.declaration_id, 
      dec.date,
      doc.doc_number
      ROW_NUMBER() OVER (PARTITION BY dec.declaration_id) AS rn
    FROM declaration AS d
    LEFT JOIN documentation AS doc ON doc.declaration_id = dec.declaration_id
                                  AND doc.doc_type = 'U44'
  )
SELECT
  declaration_id, 
  date,
  MIN(CASE WHEN rn = 1 THEN doc_number END) AS doc_number_1,
  MIN(CASE WHEN rn = 2 THEN doc_number END) AS doc_number_2,
  ...
  MIN(CASE WHEN rn = 8 THEN doc_number END) AS doc_number_8
FROM numbered
GROUP BY declaration_id, date
ORDER BY declaration_id;