Solved

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

Posted on 2014-03-29
7
1,050 Views
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.
sql-code.sql
results.xls.xlsx
0
Comment
Question by:avivap
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 11

Expert Comment

by:John_Vidmar
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.
0
 

Author Comment

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

Expert Comment

by:John_Vidmar
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).
correlated.sql
0
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 

Author Comment

by:avivap
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.
etc.
Does this make sense?
(I added mode2, which will eventually replace 'mode' - so when reading what I wrote above, please keep that in mind).
Thanks.
0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 39971444
Give a try
;WITH CTE 
     AS (SELECT DISTINCT au.LastName + ', ' + au.FirstName                                                                                           AS 'student',
                         au.firstname, 
                         au.lastname, 
                         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.*, 
       RTRIM(SUBSTRING(ISNULL((SELECT ',' + [Mode] 
                               FROM   CTE t2 
                               WHERE  t1.student = t2.student 
                               FOR xml path('')), ' '), 2, 2000)) AS Mode2 
FROM   CTE t1 

Open in new window

0
 

Author Closing Comment

by:avivap
ID: 39974086
Hi,
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!
0
 
LVL 41

Expert Comment

by:Sharath
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.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

705 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