Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
homelessnick
Starting Member
3 Posts |
Posted - 2013-02-21 : 06:21:02
|
Hi everyone!I have a food menu table with the following structure:MenuId Order FoodId------ ----- ------320 1 NP0030320 2 NS0862320 3 NG4376611 1 NP0030611 2 NS0862611 3 NG4376Which means that menu number 320 has 3 plates, with its respective order and FoodId. MenuId 611 is exactly the same, only changes the menuIDThe problem is that I need to create another table containing unique menus, for example:MenuId Order FoodId------ ----- ------001 1 NP0030001 2 NS0862001 3 NG4376Any advice or help would be appreciated. Thanks in advance! |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-02-21 : 07:05:01
|
DECLARE @tab TABLE(MenuId INT, [Order] INT, FoodId VARCHAR(10))INSERT INTO @tabSELECT 320, 1, 'NP0030' UNION ALLSELECT 320, 2, 'NS0862' UNION ALLSELECT 320, 3, 'NG4376' UNION ALLSELECT 611, 1, 'NP0030' UNION ALLSELECT 611, 2, 'NS0862' UNION ALLSELECT 611, 3, 'NG4376'SELECT RIGHT( '000'+ CAST(DENSE_RANK() OVER( Order BY (SELECT 1)) AS VARCHAR(5)), 3) MenuId, [Order], FoodId FROM @tabGROUP BY [Order], FoodId--Chandu |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2013-02-21 : 07:22:47
|
[code]create table #yak (MenuID varchar(3), [Order] int, FoodID varchar(6))insert into #yak (MenuID, [Order], FoodID)values ('320',1,'NP0030') ,('320',2,'NS0862') ,('320',3,'NG4376') ,('611',1,'NP0030') ,('611',2,'NS0862') ,('611',3,'NG4376') ,('225',1,'NM0908') ,('225',2,'NJ0889')select * from #yakselect MenuID ,ROW_NUMBER()OVER(ORDER BY MenuID) as [Order] ,FoodIDfrom (select distinct '001' as MenuID ,foodidfrom #yak) yMenuID Order FoodID001 1 NG4376001 2 NJ0889001 3 NM0908001 4 NP0030001 5 NS0862[/code]I added a few extra records to see if this is what the OP meant. My way is MUCH more clumsy , but i do not get a repeating order number.How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
homelessnick
Starting Member
3 Posts |
Posted - 2013-02-21 : 09:18:40
|
Thanks in advance for the quick replies, but I think I have explained myself correctly.What I need is to know how many unique menus I have in my table, not creating one menu made of all the plates.For example:MenuId Order FoodId------ ----- ------320 1 NP0030320 2 NS0862320 3 NG4376611 1 NP0030611 2 NS0862611 3 NG4376555 1 DS0974555 2 DS8374555 3 DS1232Would return:320 1 NP0030320 2 NS0862320 3 NG4376555 1 DS0974555 2 DS8374555 3 DS1232MenuId 611 disapears because its duplicated (MenuId 320 goes first and its the same menu)Thanks again! |
|
|
homelessnick
Starting Member
3 Posts |
Posted - 2013-02-21 : 09:29:33
|
At least I need to know if a menu in table 1 exists in table 2. I meanTable 1MenuId Order FoodId------ ----- ------999 1 NP0030999 2 NS0862999 3 NG4376Table 2MenuId Order FoodId------ ----- ------320 1 NP0030320 2 NS0862320 3 NG4376555 1 DS0974555 2 DS8374555 3 DS1234MenuId 999 exists in table 2, with the MenuId 320Thanks again!!! |
|
|
|
|
|
|
|