SQL Server - 'Stuff' function not working as expected, or Top 1 not working as expected

I'm returning two rows that are identical, except for one column - mode - I want to return one row, and have both of the column values of 'mode' stuffed into one column - mode2.
I'm going to attach a file with the SQL Code, and a file with the result set.
For some reason, the STUFF is returning ALL values - not just the ones that apply to that particular row or both rows (there are 4 values that are being appended, comma delimited, into the mode2 column).
(Look at rows 7 and 8  of the result set - I want the column mode2 to have a value of "air, rental" and then I will remove the column "mode" and return only one row).

The other option, is to use Top (1) - however, that's also ALWAYS pulling the same column value, and it's not pulling the Top one for that particular row.
Who is Participating?
SharathConnect With a Mentor Data EngineerCommented:
Give a try
     AS (SELECT DISTINCT au.LastName + ', ' + au.FirstName                                                                                           AS 'student',
                         CONVERT(VARCHAR(10), CONVERT (DATETIME, mimxstartdate.OptionText), 120)                                                     AS 'startdate',
                         CONVERT(VARCHAR(10), CONVERT (DATETIME, mimxenddate.OptionText), 120)                                                       AS 'enddate',
                         DATEPART(yyyy, GETDATE())                                                                                                   AS 'year',
                         DATEPART(m, GETDATE())                                                                                                      AS 'month',
                         mimxstatus.OptionID                                                                                                         AS 'status',
                         CONVERT (INT, CONVERT (DATETIME, mimxenddate.OptionText)) - CONVERT (INT, CONVERT (DATETIME, mimxstartdate.OptionText)) + 1 AS 'days',
                         xam.ActivityID                                                                                                              AS 'activityid',
                         'vCAL'                                                                                                                      AS 'report',
                         'vXSLCAL'                                                                                                                   AS 'report_xsl',
                         mimxtype.OptionID                                                                                                           AS 'type',
                         mimxhotel.OptionID                                                                                                          AS 'type1',
                         mimxstatus.OptionID                                                                                                         AS 'type2',
                         mimxpo.OptionID                                                                                                             AS 'type3',
                         mo.OptionName                                                                                                               AS 'mode'
         FROM   tray_ItemMetadataItem mimi 
                LEFT OUTER JOIN tray_ItemMetadataXref mimxins 
                             ON mimxins.ItemTypeInstanceID = mimi.OptionID 
                                AND mimxins.CategoryID = 799 
                LEFT OUTER JOIN tray_ItemMetadataXref mimxtype 
                             ON mimxtype.ItemTypeInstanceID = mimi.OptionID 
                                AND mimxtype.CategoryID = 777 
                LEFT OUTER JOIN tray_ItemMetadataXref mimxpo 
                             ON mimxpo.ItemTypeInstanceID = mimi.OptionID 
                                AND mimxpo.CategoryID = 778 
                LEFT OUTER JOIN tray_ItemMetadataXref mimxhotel 
                             ON mimxhotel.ItemTypeInstanceID = mimi.OptionID 
                                AND mimxhotel.CategoryID = 780 
                LEFT OUTER JOIN tray_ItemMetadataXref mimxstartdate 
                             ON mimxstartdate.ItemTypeInstanceID = mimi.OptionID 
                                AND mimxstartdate.CategoryID = 662 
                LEFT OUTER JOIN tray_ItemMetadataXref mimxenddate 
                             ON mimxenddate.ItemTypeInstanceID = mimi.OptionID 
                                AND mimxenddate.CategoryID = 661 
                LEFT OUTER JOIN tray_ItemMetadataXref mimxstatus 
                             ON mimxstatus.ItemTypeInstanceID = mimi.OptionID 
                                AND mimxstatus.CategoryID = 765 
                LEFT OUTER JOIN tray_ItemMetadataXref mimxtmode 
                             ON mimxtmode.ItemTypeInstanceID = mimi.OptionID 
                                AND mimxtmode.CategoryID = 766 
                LEFT OUTER JOIN tray_Option mo 
                             ON mo.OptionID = mimxtmode.OptionID 
                INNER JOIN ibm_ActivityMain xam 
                        ON xam.ActivityID = mimi.ItemtypeInstanceID 
                INNER JOIN ibm_ActivityContent xac 
                        ON xac.ContentID = xam.ContentID 
                INNER JOIN ACCEL_Users au 
                        ON au.UserID = mimxins.OptionID) 
SELECT t1.*, 
                               FROM   CTE t2 
                               WHERE  t1.student = t2.student 
                               FOR xml path('')), ' '), 2, 2000)) AS Mode2 
FROM   CTE t1 

Open in new window

The OUTER APPLY subquery should be correlated.  Your main query has tray_Option mo, remove tray_Option mo1 from your subquery and join back to mo.
avivapAuthor Commented:
I wasn't clear about how to do what you said.
Would you mind sending me over the exact code for me to try?
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

The attachment contains a correlated-subquery.

Also, placing a filter in the where-clause on a table participating in a left-outer-join defeats that behavior (i.e., an inner-join would accomplish the same task).
avivapAuthor Commented:
I tried this solution, and it doesn't work!
In fact, the mode2 column is coming out identical to the mode column.
If you will look at my original results.xls file, you'll see that lines 7 and 8 are identical, except that one has a mode of air and one has a mode of travel.
(Adding the code - above - AND      mo1.optionid = mo.optionid - will ensure that MULTIPLE rows are being returned - rather than a single row!)
What I want for my result set is ONE row (not two), and the mode should either have ONLY air (TOP 1) or the mode should have "air, travel' (the STUFF value) - so that only one row is returned in the select distinct.
Rows 3 & 4, should only be returning one row - with either 'local' or 'local,rental' as the mode.
Rows 7 & 8, should only be returning one row - with either 'air' or 'air, rental' as the mode.
Rows 10 & 11, should only be returning one row - with either 'air' or 'air, rental' as the mode.
Does this make sense?
(I added mode2, which will eventually replace 'mode' - so when reading what I wrote above, please keep that in mind).
avivapAuthor Commented:
I did use your SQL - albeit with a bit of tweaking.  Thanks for the idea of using a CTE - however - because we have such large tables, it substantially changed the query time...and our users are already annoyed with having to wait so long!
Thanks for your help - I learned something!
SharathData EngineerCommented:
You have LEFT JOIN to same table multiple times with different CataegoryId. I think that part can be optimized. If you open a new question with sample data, I think me or someone can help to optimize the query.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.