1

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
Raj K
  • 458
  • 2
  • 7
  • 22
  • If you want an order you must `order by` to get one. – Alex K. Mar 23 '18 at 18:40
  • I tried to Order By on Agreement ID, that gives me two more extra rows for as there are 5 different Agreement ID's, but the order in String E2,80 remains same again on them – Raj K Mar 23 '18 at 19:26
  • @RajK . . . Your links don't work (at least for me), so your question doesn't make sense. Moral? Include all relevant information directly in the question. – Gordon Linoff Mar 23 '18 at 20:33
  • 1
    Hi @Gordon Linoff, I've updated the post for image links of tables in text format, Please let me know if you have a better solution to my question above – Raj K Mar 26 '18 at 03:46

0 Answers0