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

Posted on 2014-03-29
Medium Priority
Last Modified: 2014-04-03
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.
Question by:avivap
  • 3
  • 2
  • 2
LVL 11

Expert Comment

ID: 39964985
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.

Author Comment

ID: 39964998
I wasn't clear about how to do what you said.
Would you mind sending me over the exact code for me to try?
LVL 11

Expert Comment

ID: 39967199
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).
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.


Author Comment

ID: 39967300
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).
LVL 41

Accepted Solution

Sharath earned 2000 total points
ID: 39971444
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


Author Closing Comment

ID: 39974086
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!
LVL 41

Expert Comment

ID: 39974296
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.

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

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.

Join & Write a Comment

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Manually copying shapes and their assigned macros one by one to a new location can be tedious, but if you use the Excel utility workbook attached to this article, the process will be much quicker and easier.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question