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.
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.
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;
JOINonce and then pivot your data? Sample data and expected results (in a consumable format, not an image) will help us help you here. – Thom A Commented Feb 4 at 11:13