I'm trying to stuff the Distinct
Column values in the table based on grouping on particular columns, I'm able to stuff the distinct
values of the column in to single String but the order of the string is not correct when I compare the result that has three stuff results in a row.
And I've used SQL Query as below:
CREATE TABLE #TableB (
AcquireNbr int
, Working_Day Date
, Working_Cd varchar(15)
, Agreement_Cds varChar(60) NULL
, Agreement_id_Qtys varChar(80) NULL
, Agreement_Receiver_Cds varchar(80) NULL
, Agreement_Managers varChar(80) NULL
)
INSERT INTO #TableB
SELECT
AcquireNbr
, Working_Day
, Working_Cd
, Agreement_Cds = STUFF((SELECT DISTINCT ', ' + LTRIM(RTRIM(Agreement_Cd))
FROM #TableA b
WHERE b.Working_Cd = a.Working_Cd AND b.AcquireNbr = a.AcquireNbr AND b.Working_Day = a.Working_Day
FOR XML PATH('')), 1, 2, '')
, Agreement_id_Qtys = STUFF((SELECT DISTINCT ', ' + CONVERT(varchar(10), Agreement_ID) + '_' + CONVERT(varchar(15), Agreement_Qty)
FROM #TableA b
WHERE b.Working_Cd = a.Working_Cd AND b.AcquireNbr = a.AcquireNbr AND b.Working_Day = a.Working_Day
FOR XML PATH('')), 1, 2, '')
, Agreement_Receiver_Cds = STUFF((SELECT DISTINCT ', ' + LTRIM(RTRIM(Agreement_Receiver_Cd))
FROM #TableA b
WHERE b.Working_Cd = a.Working_Cd AND b.AcquireNbr = a.AcquireNbr AND b.Working_Day = a.Working_Day
FOR XML PATH('')), 1, 2, '')
FROM
#TableA a
GROUP BY
AcquireNbr
, Working_Day
, Working_Cd
Data Table TableA
contains my data, as below
AcquireNbr Working_Day Working_Cd Agreement_ID Agreement_Qty Agreement_Cd Agreement_Receiver_Cd
5 2018-03-09 ABC 1 246000 FT E
5 2018-03-09 DEF 2 3300 FT E1
5 2018-03-09 DEF 3 900 FT E1
5 2018-03-09 DEF 2 3300 FT E1
5 2018-03-09 ABC 4 0 NT 80
5 2018-03-09 GHI 5 25000 NT 80
5 2018-03-09 DEF 3 900 FT E1
And the result for query looks like below
AcquireNbr Working_Day Working_Cd Agreement_Cds Agreement_id_Qtys Agreement_Receiver_Cds
5 2018-03-09 GHI NT 5_25000 80
5 2018-03-09 DEF FT 3_900, 2_3300 E1
5 2018-03-09 ABC FT, NT 1_246000, 4_0 80, E
In the last row of the result the AgreementId and Agreement_Receiver_Cd order doesn't match, that is
for agreement id = 1 the receiver cd is E but in result set it shows as 80
And
for agreement id = 4 the receiver cd is 80 but in result set it shows as E
I guess the select statement in stuff -> sub query for getting Agreement_Receiver_Cds is returning the result in different order than other sub query which gets Agreement_id_Qtys.
Required result
AcquireNbr Working_Day Working_Cd Agreement_Cds Agreement_id_Qtys Agreement_Receiver_Cds
5 2018-03-09 GHI NT 5/25000 80
5 2018-03-09 DEF FT 3/900, 2/3300 E1
5 2018-03-09 ABC FT, NT 1/246000, 4/0 E, 80