I have a bit of an issue. I have a dataset with around 6000 records stored in a MSSQL 2016 database, where I need to concatenate each column, sorted on row basis and grouped by an ID.
It is no problem to sort the data within the column itself, but the position of the concatenated values is the key in the system that needs the data. So, if I merge four rows with the same ID, each row value must be in the same position throughout the whole resultset row.
To make it even more messy, I must be able to apply some logic to determine which row should be in position one.
If I use the the sort functionality in GROUP_CONCAT_DS (I'm using a semicolon delimiter), it sorts on the column values, which make the index position of each merge inconsistent related to the rows that holds the data.
Example data:
id col1 col2
--------------------
1 foo bar
1 lorem ipsum
2 hi cowboy
Expected output:
id, concat_col1, concat_col2
----------------------------
1 foo;lorem bar;ipsum
2 hi cowboy
Absolutely unacceptable output:
id concat_col1 concat_col2
----------------------------
1 bar;foo lorem;ipsum
2 hi cowboy
In pseudo-SQL, this is a simplified example of what I want to achieve:
SELECT
[id]
,dbo.GROUP_CONCAT_D([col1], ';') AS [concat_col1]
,dbo.GROUP_CONCAT_D([col2], ';') AS [concat_col2]
FROM [hrm].[vEmploymentsPositions]
GROUP BY [id]
ORDER BY [id] asc, [col1] desc, [col2] asc
... But for loical reason the sort doesn't work that way.
How can I approach this issue and make a robust solution that fit our needs?
