Summing contents of sub categories

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)

Summing contents of sub categories

Author

Topic

pigeonrandle
Starting Member

2 Posts

Posted - 2007-09-13 : 08:02:59

This is my first post, so hello everyone!

I hope someone can help as this is bending my brain in unusual directions...

I have a table of categories...

CategoryID | ParentCategoryID | CategoryName

and a table of products

ProductID | ProductName |ProductCategoryID

and i want to return results for all the products in a category, but if that category has sub categories, then i want to return all those products aswell.

In other words, if i choose shoes as an example...

Shoes
.....Stilletos
..........Prada
...............product1
..........Gucci
...............product2
.....Trainers
..........Nike
...............product3
..........Puma
...............product4

so if i ask for the contents of shoes i expect products 1,2,3,4
and the contents of Stilletos would be products 1,2
and Prada would just be product1

So i guess i'm asking

1) how on earth do i do this?!

Cheers in advance,
James.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-13 : 08:13:29

http://www.intelligententerprise.com/001020/celko.jhtml

PS: If anyone is interested in a SQL Server job in Connecticut with excellent pay send me your resume to ValterBorges@msn.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 08:13:45

With a Common Table Expression, CTE.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-13 : 08:33:55

Also refer
http://www.nigelrivett.net/RetrieveTreeHierarchy.html

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 09:18:31

See this recent topic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88675#329208

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 09:25:04

[code]DECLARE @Sample TABLE (ID INT, ParentID INT, Name VARCHAR(1000))

INSERT @Sample
SELECT 7, NULL, 'Ljunggren' UNION ALL
SELECT 3, 1, 'Gulli' UNION ALL
SELECT 8, 7, 'Kerstin' UNION ALL
SELECT 1, NULL, 'Rosberg' UNION ALL
SELECT 4, 2, 'Peter' UNION ALL
SELECT 5, 3, 'Susanne' UNION ALL
SELECT 2, 1, 'Jan-Eric' UNION ALL
SELECT 10, 9, 'Jennie' UNION ALL
SELECT 6, 3, 'Annelie' UNION ALL
SELECT 9, 7, 'Kenneth' UNION ALL
SELECT 11, 9, 'Jessica'

;WITH Yak (ID, ParentID, Name, Path, Indent)
AS (
SELECT ID,
ParentID,
Name,
CONVERT(VARCHAR, ROW_NUMBER() OVER (ORDER BY Name)),
0
FROM @Sample
WHERE ParentID IS NULL

UNION ALL

SELECT s.ID,
s.ParentID,
s.Name,
CONVERT(VARCHAR, y.Path + ',' + CONVERT(VARCHAR, ROW_NUMBER() OVER (ORDER BY s.Name DESC))),
y.Indent + 1
FROM @Sample AS s
INNER JOIN Yak AS y ON y.ID = s.ParentID
)

SELECT ID,
ParentID,
REPLICATE('.....', Indent) + Name,
Path
FROM Yak
ORDER BY Path[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 09:26:41

Output from query above

ID	ParentID	(No column name)	Path
--	--------	-----------------	-----
7	NULL		Ljunggren		1
8	7		.....Kerstin		1,1
9	7		.....Kenneth		1,2
11	9		..........Jessica	1,2,1
10	9		..........Jennie	1,2,2
1	NULL		Rosberg			2
2	1		.....Jan-Eric		2,1
4	2		..........Peter		2,1,1
3	1		.....Gulli		2,2
5	3		..........Susanne	2,2,1
6	3		..........Annelie	2,2,2

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

pigeonrandle
Starting Member

2 Posts

Posted - 2007-09-13 : 09:52:14

Err, WOW! thanks for all your suggestions.

James.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources