August 23, 2013 at 8:38 am
Hi,
I have 2 master-details tables : Category and Item
I want to group them to show in drop-down like:
Category1
Item1
Item2
Item3
Category2
Item4
Item5
I will disable selection of Category in drop-down.
Please let me know which query I should use considering performance issues.
August 23, 2013 at 8:41 am
Pretty sparse on the details but maybe something like this is what you are looking for?
Select 'Category1', Item, 1 as SortOrder
from Table1
union
Select 'Category2', Item, 2 as SortOrder
from Table2
order by SortOrder, Item
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 23, 2013 at 8:54 am
this is table structure:
Category: CategoryID, CategoryName
Item: ItemID, CategoryID, ItemName
Now, I want to display item names in drop-down along with category name:
CategoryName1
ItemName1
ItemName2
ItemName3
CategoryName2
ItemName4
ItemName5
CategoryName3
.
.
.
and so on
August 23, 2013 at 8:58 am
shirish_padekar (8/23/2013)
this is table structure:Category: CategoryID, CategoryName
Item: ItemID, CategoryID, ItemName
Now, I want to display item names in drop-down along with category name.
The query I posted seems like it is really close. If that doesn't work you will have to provide more information about what you want to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 23, 2013 at 9:03 am
Below is the result I want:
CategoryName1
ItemName1
ItemName2
ItemName3
CategoryName2
ItemName4
ItemName5
CategoryName3
.
.
.
and so on
I want to dynamically load both category names and their respective item names from these 2 tables into a dropdownlist.
August 23, 2013 at 9:07 am
shirish_padekar (8/23/2013)
Below is the result I want:CategoryName1
ItemName1
ItemName2
ItemName3
CategoryName2
ItemName4
ItemName5
CategoryName3
.
.
.
and so on
I want to dynamically load both category names and their respective item names from these 2 tables into a dropdownlist.
Just reposting the same thing is not providing details. Can you take the 30 seconds it will take to put together create table scripts and insert statements for a few rows of data?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 23, 2013 at 9:16 am
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Category](
[CategoryID] [int] IDENTITY(1,1) NOT NULL,
[CategoryName] [varchar](50) NULL,
CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED
(
[CategoryID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
-----------------------------------------------------------------------------------------------
USE [test]
GO
/****** Object: Table [dbo].[Item] Script Date: 08/23/2013 20:42:14 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Item](
[ItemID] [int] IDENTITY(1,1) NOT NULL,
[CategoryID] [int] NULL,
[ItemName] [varchar](50) NULL,
CONSTRAINT [PK_Item] PRIMARY KEY CLUSTERED
(
[ItemID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
-----------------------------------------------------------------------------------------------
insert into Category values ('CategoryName1')
insert into Category values ('CategoryName2')
insert into Category values ('CategoryName3')
insert into Item values (1,'Item1')
insert into Item values (1,'Item2')
insert into Item values (1,'Item3')
insert into Item values (2,'Item4')
insert into Item values (2,'Item5')
insert into Item values (3,'Item6')
insert into Item values (3,'Item7')
insert into Item values (3,'Item8')
-----------------------------------------------------------------------------------------------
Please check above. Thank you.
August 23, 2013 at 9:50 am
Thanks. Having ddl and sample data makes this a LOT easier. 😀
Something like this should work.
with SortedItems as
(
select CategoryID, CategoryName, ROW_NUMBER() over(order by CategoryName) as RowNum, 1 as SortOrder
from Category
union all
select CategoryID, ItemName, ROW_NUMBER() over(order by ItemName) as RowNum, 2 as SortOrder
from Item
)
select CategoryName
from SortedItems
order by CategoryID, SortOrder, RowNum
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 23, 2013 at 10:25 am
No need in windowed functions, you can achieve the same by simple ORDER BY of UNION ALL:
SELECT ItemName
FROM (SELECT *
FROM Item
UNION ALL
SELECT 0, CategoryID, CategoryName
FROM Category) Q
ORDER BY CategoryId, ItemId
August 23, 2013 at 10:29 am
Eugene Elutin (8/23/2013)
No need in windowed functions, you can achieve the same by simple ORDER BY of UNION ALL:
SELECT ItemName
FROM (SELECT *
FROM Item
UNION ALL
SELECT 0, CategoryID, CategoryName
FROM Category) Q
ORDER BY CategoryId, ItemId
Nice, pretty much the same idea but much simpler way to get there. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 23, 2013 at 11:32 am
Great !! pretty simple and clean approach!
Can I alphabetically sort the result by category name first and then alphabetically sort item names?
Thanks for your help.
August 24, 2013 at 7:24 am
How can I order first by category names and then by item names? I tried to use order by in following query:
SELECT ItemName
FROM (SELECT *
FROM Item
UNION ALL
SELECT 0, CategoryID, CategoryName
FROM Category order by categoryname) Q
ORDER BY CategoryId, ItemId
But I am getting following error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
August 24, 2013 at 2:35 pm
Both tables are going to be scanned at least once. I haven't tried it for efficiency but I thought I'd let some of the inbuilt tools in SQL Server worry about this 2 level hierarchical sorted output. This will even work as far back as SQL Server 2000 and maybe earlier (I just don't remember when they introduced ROLLUP).
WITH
cteEnumerate AS
(
SELECT c.CategoryName,
ItemName = ISNULL(i.ItemName,c.CategoryName),
GroupItem# = GROUPING(ItemName)
FROM dbo.Category c
FULL JOIN dbo.Item i
ON c.CategoryID = i.CategoryID
GROUP BY CategoryName,ItemName WITH ROLLUP
)
SELECT ItemName
FROM cteEnumerate
WHERE ItemName > '' --Is NOT NULL and is NOT BLANK
ORDER BY CategoryName, GroupItem# DESC, ItemName
;
Try the code with this data and see.
insert into Category values ('CategoryName7') --Changed
insert into Category values ('CategoryName2')
insert into Category values ('CategoryName3')
insert into Item values (1,'Item1')
insert into Item values (1,'Item2')
insert into Item values (1,'Item3')
insert into Item values (2,'Item9') --Changed
insert into Item values (2,'Item5')
insert into Item values (3,'Item6')
insert into Item values (3,'Item7')
insert into Item values (3,'Item8')
--Jeff Moden
August 24, 2013 at 2:50 pm
Shifting gears a bit, no matter which way you solve this problem, the server and the I/O system are going to suffer quite a bit. Even the "pipe" is going to carry a whole lot of unnecessarily redundant data. Let's stop and think about it. This is a data driven menu and every time someone exercises the menu, it has to make a trip to the server. I just went through this at work and, with all the users that we have using the app and the number of times they hit the menu, it caused TRILLIONS of bytes of I/O every day. Even if it's all in server memory, it was a totally silly waste of resources better used for something else.
It also caused a delay in the menu. It wasn't much but it was enough to notice.
We solved the problem by making the realization that the data for the menu didn't change all that frequently. We simply cached the data onto the web server and updated it just once per day. Of course, if the data is a bit more active, you could put a trigger on the tables to mark the menu as "dirty" and make it so the next person that used the menu would cause the single update of the web cache.
--Jeff Moden
August 24, 2013 at 3:21 pm
Thanks, it worked! I am not sure about query performance, will check with actual database or may be will try other alternative as per your suggestion.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply