April 15, 2009 at 3:06 am
Hi,
I would like to retrieve the value of a column in a specific column depending of the value of other column.
Maybe I am not good explaining my self, but here it is an example:
Table:
ID Meal Order
1 "Tomato Soup" 1
2 "Green Salad" 1
3 "Medit Salad" 1
4 "Seafood soup" 1
5 "Chicken" 2
6 "Fish" 2
7 "Ribs" 2
Query
FirstOnes SecondOnes
"Tomato Soup" "Chicken"
"Green Salad" "Fish"
"Medit Salad" "Ribs"
"Seafood soup"
I have tried with Union, but it will return me 7 rows with empty fields in both columns
SELECT Meal as First, '' as Second
FROM Meals
where Order = 1
UNION
SELECT '' as First, Meal as Second
FROM Meals
where Order = 2
I have also tried with full/cross/left joins but I can't get it to work
Any guidance will be helpfull and appreciated.
Thanks.
Kindest Regards,
@puy Inc
April 15, 2009 at 3:14 am
Reading another post, I found the PIVOT operator and I think it will help. I am going to take a look.
Kindest Regards,
@puy Inc
April 15, 2009 at 4:31 am
IS there any unique id between Tomato soup- chicken, green salad-Fish, and Medit salad-Ribs?
---------------------------------------------------------------------------------
April 15, 2009 at 4:49 am
No, there is not unique id, maybe I didn't explained my self very good.
What I wanted to do is to return the values of the Meal column in the FirstOne or SecondOne columns based on the Order column.
This way all of the meals that have Order=1 goes to FirstOne Column and all of the meals that have Order = 2 goes to the SecondOne Column
This time, I hope you understand
Kindest Regards,
@puy Inc
April 15, 2009 at 5:11 am
@puyinc (4/15/2009)
No, there is not unique id, maybe I didn't explained my self very good.What I wanted to do is to return the values of the Meal column in the FirstOne or SecondOne columns based on the Order column.
This way all of the meals that have Order=1 goes to FirstOne Column and all of the meals that have Order = 2 goes to the SecondOne Column
This time, I hope you understand
Pakki understands just fine, he's attempting to determine for sure if the table design is fundamentally flawed or not. This will return the results you want from your existing table structure:
DROP TABLE #temp
CREATE TABLE #temp ([ID] INT, Meal VARCHAR(20), [Order] INT)
INSERT INTO #temp ([ID], Meal, [Order])
SELECT 1, 'Tomato Soup', 1 UNION ALL
SELECT 2, 'Green Salad', 1 UNION ALL
SELECT 3, 'Medit Salad', 1 UNION ALL
SELECT 4, 'Seafood soup', 1 UNION ALL
SELECT 5, 'Chicken', 2 UNION ALL
SELECT 6, 'Fish', 2 UNION ALL
SELECT 7, 'Ribs', 2
; WITH MyCTE AS
(SELECT [ID], Meal, [Order],
ROW_NUMBER() OVER (PARTITION BY [Order] ORDER BY [ID]) AS seq
FROM #temp)
SELECT a.meal AS FirstOnes, ISNULL(b.meal, 'nothing') AS SecondOnes
FROM MyCTE a
LEFT JOIN MyCTE b ON b.seq = a.seq AND b.[Order] = 2
WHERE a.[Order] = 1
Results:
FirstOnes SecondOnes
-------------------- --------------------
Tomato Soup Chicken
Green Salad Fish
Medit Salad Ribs
Seafood soup nothing
However, meal-pairs such as 'Tomato Soup' and 'Chicken' - should they not have a more formal relationship between them? How about DinerID?
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 15, 2009 at 5:28 am
If there isn't a unique id between the first course meals and the second course meals you'll need to create one. Without one there's nothing to tell you that a Tomato Soup is paired with Chicken and that a Green Salad is paired with Fish.
If the implication is that the first FirstCourse meal is paired up with the first SecondCourse meal then you could then use temp tables - like so:
select identity(int, 1, 1) as unique_id, Meal
into #first_course_meal
from my_table
where [Order] = 1
----
select identity(int, 1, 1) as unique_id, Meal
into #second_course_meal
from my_table
where [Order] = 2
----
select f.Meal as FirstCourse, s.Meal as SecondCourse
from #first_course_meal f
left outer join #second_course_meal s on s.unique_id = f.unique_id
Actually, Chris's use of CTE's is much more elegant. I've got to start using them more often.
April 15, 2009 at 8:10 am
If there isn't a unique id between the first course meals and the second course meals you'll need to create one. Without one there's nothing to tell you that a Tomato Soup is paired with Chicken and that a Green Salad is paired with Fish.
If the implication is that the first FirstCourse meal is paired up with the first SecondCourse meal then you could then use temp tables - like so:
Thank u both for the help so far, I have learned about CTE's (didn't know about it).
There is no formal relationship between FirstOnes (entrances) and SecondOnes (Main Dishes). The resultant query will be something like a Restaurant's Menu, where one column will be Entrances and other column will be Main Dishes, and we may add another column for Deserts.
The only problem with the code Chris provided is that if there are more MainDishes than Entrances it will not return all of them. To workaround this issue I added a count of Entrances and Maindishes and depending of which one has more items, is the query I would execute (not too elegant but works). The code will be like this:
declare @CountEntrances int
declare @CountMainDishes int
select @countEntrances = count(1) from #temp where [Order] = 1
select @countMainDishes = count(1) from #temp where [Order] = 2
if @countEntrances > @countMainDishes
begin
; WITH MyCTE AS
(SELECT [ID], Meal, [Order],
ROW_NUMBER() OVER (PARTITION BY [Order] ORDER BY [ID]) AS seq FROM #temp)
SELECT ISNULL(a.meal, 'nothing') AS FirstOnes, ISNULL(b.meal, 'nothing') AS SecondOnes
FROM MyCTE a
LEFT JOIN MyCTE b ON b.seq = a.seq AND b.[Order] = 2
WHERE a.[Order] = 1
end
else
begin
; WITH MyCTE AS
(SELECT [ID], Meal, [Order],
ROW_NUMBER() OVER (PARTITION BY [Order] ORDER BY [ID]) AS seq FROM #temp)
SELECT ISNULL(a.meal, 'nothing') AS SecondOnes, ISNULL(b.meal, 'nothing') AS FirstOnes
FROM MyCTE a
LEFT JOIN MyCTE b ON b.seq = a.seq AND b.[Order] = 1
WHERE a.[Order] = 2
end
Kindest Regards,
@puy Inc
April 15, 2009 at 8:49 am
@puyinc (4/15/2009)
There is no formal relationship between FirstOnes (entrances) and SecondOnes (Main Dishes). The resultant query will be something like a Restaurant's Menu, where one column will be Entrances and other column will be Main Dishes, and we may add another column for Deserts.
Going back to your original data set, how will you resolve this scenario?
FirstOnesSecondOnes
"Tomato Soup"nothing
"Green Salad""Fish"
nothing"Ribs"
"Seafood soup"nothing
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 15, 2009 at 9:22 am
Going back to your original data set, how will you resolve this scenario?
FirstOnesSecondOnes"Tomato Soup"nothing
"Green Salad""Fish"
nothing"Ribs"
"Seafood soup"nothing
Remember that the idea was to display a list of Entrances and Maindishes, so, this is not a possible scenario. As long as there is at least one, that one will go first. Also there is no relationship between FirstOnes and SecondOnes. So the scenario you are saying will go like this:
Information in the table
IDMealOrder
1Tomato Soup1
2Green Salad1
3Seafood soup1
4Fish2
5Ribs2
And the query should return this:
FirstOnesSecondOnes
"Tomato Soup""Fish"
"Green Salad""Ribs"
"Seafood soup"nothing
Kindest Regards,
@puy Inc
April 15, 2009 at 9:48 am
Aha, gotcha! Thanks for the clarification. Blonde moment. Try this:
DROP TABLE #temp
CREATE TABLE #temp ([ID] INT, Meal VARCHAR(20), [Order] INT)
INSERT INTO #temp ([ID], Meal, [Order])
SELECT 1, 'Tomato Soup', 1 UNION ALL
SELECT 2, 'Green Salad', 1 UNION ALL
SELECT 3, 'Medit Salad', 1 UNION ALL
SELECT 4, 'Seafood soup', 1 UNION ALL
SELECT 5, 'Chicken', 2 UNION ALL
SELECT 6, 'Fish', 2 UNION ALL
SELECT 7, 'Ribs', 2
SELECT seq, MAX(Main) AS Main, MAX(Starter) AS Starter
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY [Order] ORDER BY [ID]) AS seq,
CASE WHEN [Order] = 1 THEN Meal END AS Main,
CASE WHEN [Order] = 2 THEN Meal END AS Starter
FROM #temp) d
GROUP BY seq
The output is:
seq Main Starter
-------------------- -------------------- --------------------
1 Tomato Soup Chicken
2 Green Salad Fish
3 Medit Salad Ribs
4 Seafood soup NULL
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
April 15, 2009 at 10:26 am
Hey Chris, thanks for your help.
where is the option to mark this thread as answered?
Kindest Regards,
@puy Inc
April 15, 2009 at 10:52 am
You're welcome!
There's no option for marking a thread as "answered" - they're all kept open for reference. Also, someone could come along yet with a killer solution.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply