avivap
asked on
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.
sql-code.sql
results.xls.xlsx
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
John_Vidmar
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.
ASKER
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!
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!
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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
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.