simple query question - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

simple query question

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2008-04-30 : 01:01:45

My categories table is as follows categoryid, CategoryName, ParentID, DateAdded

I have data that looks like the following from this SQL

SELECT * from categories ORDER By CategoryName, ParentID

32 Automotive 0 2008-04-08 13:23:24.440
39 Bartending 0 2008-04-08 13:24:11.970
45 test 32 2008-04-29 17:24:22.640
33 The dressing room 0 2008-04-08 13:23:32.550

What I would like is to Order this by CategoryName, but whenever the category has sub categories, I would like them to appear beneath it like so

32 Automotive 0 2008-04-08 13:23:24.440
45 test 32 2008-04-29 17:24:22.640
39 Bartending 0 2008-04-08 13:24:11.970
33 The dressing room 0 2008-04-08 13:23:32.550

I am sure this is easy, but I am just missing it. Any thoughts?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-30 : 04:37:45

[code]DECLARE @temp table
(
categoryid int,
CategoryName varchar(50),
ParentID int,
DateAdded datetime

)

INSERT INTO @temp
SELECT 32, 'Automotive', 0, '2008-04-08 13:23:24.440'
UNION ALL
SELECT 39, 'Bartending', 0, '2008-04-08 13:24:11.970'
UNION ALL
SELECT 45, 'test', 32, '2008-04-29 17:24:22.640'
UNION ALL
SELECT 33, 'The dressing room', 0, '2008-04-08 13:23:32.550'
UNION ALL
SELECT 47, 'xcvsd', 39, '2008-04-08 13:23:32.550'
UNION ALL
SELECT 49, 'sdvfdbg', 33, '2008-04-08 13:23:32.550'
UNION ALL
SELECT 53, 'vbgr', 39, '2008-04-08 13:23:32.550'
UNION ALL
SELECT 65, 'ert', 0, '2008-04-08 13:23:32.550'
UNION ALL
SELECT 77, 'cvxnbvng', 32, '2008-04-08 13:23:32.550'

SELECT t.categoryid,t.CategoryName,t.ParentID,t.DateAdded
FROM(
SELECT t1.*,
COALESCE(t2.CategoryName,t1.CategoryName) AS SortOrder
FROM @temp t1
LEFT JOIN @temp t2
ON t2.categoryid=t1.ParentID)t
ORDER BY t.SortOrder,t.ParentID
output
-----------------------------------------------------------
categoryid CategoryName ParentID DateAdded
----------- -------------------------------------------------- ----------- -----------------------
32 Automotive 0 2008-04-08 13:23:24.440
45 test 32 2008-04-29 17:24:22.640
77 cvxnbvng 32 2008-04-08 13:23:32.550
39 Bartending 0 2008-04-08 13:24:11.970
47 xcvsd 39 2008-04-08 13:23:32.550
53 vbgr 39 2008-04-08 13:23:32.550
65 ert 0 2008-04-08 13:23:32.550
33 The dressing room 0 2008-04-08 13:23:32.550
49 sdvfdbg 33 2008-04-08 13:23:32.550
[/code]

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-30 : 06:06:56

Hi Harry

Another option I sometimes prefer when dealing with hierarchical data like this is to create a function which returns the 'path' of ancestors. You can then use that path in various ways - one of which is sorting. Here's an example...

-- Structure and data
CREATE TABLE MyCategories (CategoryId int primary key, CategoryName varchar(50), ParentId int, DateAdded datetime)
INSERT INTO MyCategories
          SELECT 32, 'Automotive', 0, '2008-04-08 13:23:24.440'
UNION ALL SELECT 39, 'Bartending', 0, '2008-04-08 13:24:11.970'
UNION ALL SELECT 45, 'test', 32, '2008-04-29 17:24:22.640'
UNION ALL SELECT 33, 'The dressing room', 0, '2008-04-08 13:23:32.550'
UNION ALL SELECT 47, 'xcvsd', 39, '2008-04-08 13:23:32.550'
UNION ALL SELECT 49, 'sdvfdbg', 33, '2008-04-08 13:23:32.550'
UNION ALL SELECT 53, 'vbgr', 39, '2008-04-08 13:23:32.550'
UNION ALL SELECT 65, 'ert', 0, '2008-04-08 13:23:32.550'
UNION ALL SELECT 77, 'cvxnbvng', 45, '2008-04-08 13:23:32.550'
go

-- Function
create function dbo.AncestorPath(@CategoryId int) returns varchar(100) as
begin
    declare @Path varchar(100)
    while 0 = 0
    begin
        select @Path = cast(CategoryId as varchar(5)) + isnull('/' + @Path, ''), @CategoryId = ParentId
        from dbo.MyCategories where CategoryId = @CategoryId

        if @@rowcount = 0 break
    end

    return @Path
end
go

-- Calculation
select * from
    (select *, isnull(dbo.AncestorPath(ParentId) + '/', '') + cast(CategoryId as varchar(10)) as Path from MyCategories) a
order by Path

/* Results
CategoryId  CategoryName           ParentId    DateAdded               Path
----------- ---------------------- ----------- ----------------------- -----------------
32          Automotive             0           2008-04-08 13:23:24.440 32
45          test                   32          2008-04-29 17:24:22.640 32/45
77          cvxnbvng               45          2008-04-08 13:23:32.550 32/45/77
33          The dressing room      0           2008-04-08 13:23:32.550 33
49          sdvfdbg                33          2008-04-08 13:23:32.550 33/49
39          Bartending             0           2008-04-08 13:24:11.970 39
47          xcvsd                  39          2008-04-08 13:23:32.550 39/47
53          vbgr                   39          2008-04-08 13:23:32.550 39/53
65          ert                    0           2008-04-08 13:23:32.550 65
*/

There's another example here...
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=101053

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-30 : 06:25:07

Using Ryan's sample data, try a recursive CTE

;WITH Yak (CategoryID, ParentID, Path, categoryname, dateadded)
AS (
	SELECT	CategoryID,
		ParentID,
		CAST(CategoryID AS VARCHAR(max)),
		categoryname,
		dateadded
	FROM	MyCategories
	WHERE	ParentID = 0

	union all

	select		c.categoryid,
			c.parentid,
			y.path + '/' + cast(c.categoryid as varchar(max)),
			c.categoryname,
			c.dateadded
	from		mycategories as c
	inner join	yak as y on y.categoryid = c.parentid
)

select		categoryid,
		categoryname,
		parentid,
		dateadded,
		path
from		yak
order by	Path

E 12°55'05.25"
N 56°04'39.16"

Harry C
Posting Yak Master

148 Posts

Posted - 2008-04-30 : 08:22:51

wow, thank you to all three of you. Very much appreciated. Glad I asked though, it was hardly as "easy" as I was trying to make it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources