Concatenate Location

Question

Hi

I have a table with field BPLId. I want to query another table which have same BPLId and then concatenate LocCode field from another table

which have same DocNum of that same BPLId records using Sql Query.

Thanks

Tuhin Paul · Answer

Example Data Table1 ( Table1 ): BPLId DocNum 1 101 1 102 2 103 Table2 ( Table2 ): BPLId LocCode 1 A 1 B 1 C 2 X 2 Y Result: BPLId DocNum ConcatenatedLocCodes 1 101 A, B, C 1 102 A, B, C 2 103 X, Y

Tuhin Paul · Answer

In PostgreSQL, you can use the STRING_AGG function. SELECT t1.BPLId, t1.DocNum, STRING_AGG(t2.LocCode, ', ' ORDER BY t2.LocCode) AS ConcatenatedLocCodes FROM Table1 t1 JOIN Table2 t2 ON t1.BPLId = t2.BPLId GROUP BY t1.BPLId, t1.DocNum; STRING_AGG is used to concatenate LocCode values with a specified delimiter ( ', ' ). The ORDER BY clause inside STRING_AGG ensures the concatenated values are sorted. In SQL Server, you can use the STRING_AGG function (introduced in SQL Server 2017) or FOR XML PATH for older versions. Using STRING_AGG (SQL Server 2017+): SELECT t1.BPLId, t1.DocNum, STRING_AGG(t2.LocCode, ', ') WITHIN GROUP (ORDER BY t2.LocCode) AS ConcatenatedLocCodes FROM Table1 t1 JOIN Table2 t2 ON t1.BPLId = t2.BPLId GROUP BY t1.BPLId, t1.DocNum; Using FOR XML PATH (Older Versions): SELECT t1.BPLId, t1.DocNum, STUFF(( SELECT ', ' + t2.LocCode FROM Table2 t2 WHERE t1.BPLId = t2.BPLId FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS ConcatenatedLocCodes FROM Table1 t1 GROUP BY t1.BPLId, t1.DocNum; STUFF removes the leading delimiter ( , ) from the concatenated string. FOR XML PATH('') is used to concatenate values into a single string.

Tuhin Paul · Answer

To achieve this, you can use a combination of SQL JOIN and string aggregation functions (like GROUP_CONCAT in MySQL or STRING_AGG in PostgreSQL/SQL Server). Join the two tables based on the matching BPLId . Group the records by DocNum and concatenate the LocCode values for each group. In MySQL, you can use the GROUP_CONCAT function to concatenate values. SELECT t1.BPLId, t1.DocNum, GROUP_CONCAT(t2.LocCode ORDER BY t2.LocCode SEPARATOR ', ') AS ConcatenatedLocCodes FROM Table1 t1 JOIN Table2 t2 ON t1.BPLId = t2.BPLId GROUP BY t1.BPLId, t1.DocNum; t1 represents the first table ( Table1 ) with BPLId and DocNum . t2 represents the second table ( Table2 ) with BPLId and LocCode . The JOIN ensures that only rows with matching BPLId in both tables are considered. GROUP_CONCAT concatenates the LocCode values for each DocNum group. ORDER BY t2.LocCode ensures the concatenated values are sorted alphabetically. SEPARATOR ', ' specifies the delimiter between concatenated values.

Sangeetha S · Answer

Let's consider you have two tables: Table1 with fields BPLId and DocNum Table2 with fields BPLId , DocNum , and LocCode You want to concatenate the LocCode field from Table2 for records with the same BPLId and DocNum . Here's how you can do it: SELECT t1.BPLId, t1.DocNum, STRING_AGG(t2.LocCode, ', ') AS ConcatenatedLocCodes FROM Table1 t1 JOIN Table2 t2 ON t1.BPLId = t2.BPLId AND t1.DocNum = t2.DocNum GROUP BY t1.BPLId, t1.DocNum; Explanation: JOIN : Combines rows from Table1 and Table2 where BPLId and DocNum match. STRING_AGG : Concatenates the LocCode values from Table2 into a single string, separated by commas. GROUP BY : Groups the results by BPLId and DocNum . If you are using MySQL, you can use GROUP_CONCAT instead: SELECT t1.BPLId, t1.DocNum, GROUP_CONCAT(t2.LocCode SEPARATOR ', ') AS ConcatenatedLocCodes FROM Table1 t1 JOIN Table2 t2 ON t1.BPLId = t2.BPLId AND t1.DocNum = t2.DocNum GROUP BY t1.BPLId, t1.DocNum;