Solved

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

Posted on 2014-03-29
7
969 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
  • 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 40

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 40

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now