Tree View Data Report

Question

Post reply

Tree View Data Report

Sam-246632

Old Hand

Points: 336
More actions
April 13, 2007 at 10:17 pm

#195109

Hi,
   I have two tables Product and Details. I display the data in a Tree View.
Ex:
Drinks
Hot Drinks
   ----Coffee
   ----Tea
   ----Hot Chocolate
Cold Drinks
   ----Coke
   ----Pepsi
   ----Canada Dry
Miscellaneous
   ---Decaf
   ---Other
I want to display the data in a report format. If the user selects drinks I need to display the Tree View data related to drinks in a report format. if he selects hot Drinks then I need to display data related to hot drinks.
Any help would be greatly appreciated.
Thanks
San.

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Sam-246632 Old Hand Points: 336 More actions · Answer 1

Sam-246632

Old Hand

Points: 336

April 16, 2007 at 9:33 am

#700723

Any help would be greatly apprecited.

Thanks

Sam.

Koji Matsumura SSCrazy Points: 2221 More actions · Answer 2

What do you mean by "Tree View" and "Report Format?"

Sam-246632 Old Hand Points: 336 More actions · Answer 3

Thanks for the reply. I am displaying the data in the front end using Tree view control. I need to export the data to excel or display the data in a report view basend on the user selection.

Ex: If the user want to view the data from a particular node, I want to display the data in a report format, the details related to that node and its child nodes.

Thanks in advance.

Sam.

Koji Matsumura SSCrazy Points: 2221 More actions · Answer 4

SELECT * FROM Product A

LEFT OUTER JOIN Details B

ON B.[???] = A.[???]

[???] should be the fields linking two tables.

If all records in Product have records in Details

or

You don't have to display records in Product which have no Details

the

use INNNER JOIN instead.

Sam-246632 Old Hand Points: 336 More actions · Answer 5

Thanks for the reply. I need to display the details of the parent and all related child nodes.

Thanks

Sam.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 6

I read this yesterday and wasn't sure what you're looking for. Would a common table expression work? This is derived from BOL:

WITH TableX AS

(SELECT e.ManagerId, e.EmployeeId, 0 AS Level

FROM table e

WHERE ManagerId is NULL

UNION ALL

SELECT e.ManagerId, e.EmployeeId, Level + 1

FROM table e

INNER JOIN TableX d

ON e.ManagerId = d.EmployeeId

)

SELECT ManagerId, EmployeeId, Level

FROM TableX;

That will get you the parent and all related child nodes. You can just join in the other tables that you need either up in the CTE or down in the query proper.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Sam-246632 Old Hand Points: 336 More actions · Answer 7

Sam-246632

Old Hand

Points: 336

April 17, 2007 at 1:28 pm

#701007

Thanks. This works for 2000 too.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 8

Nope, sorry. Common Table Expressions are new in 2005.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

Sam-246632 Old Hand Points: 336 More actions · Answer 9

Thanks. My table structure is something like this.

GROUP PRODUCT

GROUP_ID GROUP_ID

DESC DESC

TYPE CODE

I need to display the details in a report format or export to excel.

Drinks general Food

Hot Drinks Drinks HT

Coffee Hot Drinks C

Tea Hot Drinks T

Other Hot Drinks O

Cold Drinks Drinks CD

Coke Cold Drinks CK

Pepsi Cold Drinks P

Other Cold Drinks O

Thanks in advance.

Sam.

Sam-246632 Old Hand Points: 336 More actions · Answer 10

Sam-246632

Old Hand

Points: 336

April 19, 2007 at 6:00 pm

#701606

Any help would be greatly appreciated. Thanks.

Sam-246632 Old Hand Points: 336 More actions · Answer 11

Any suggestions how to accomplish this.

Thanks in advance.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 12

There is a solution that is described in Joe Celko's book "Trees and Hierarchies in SQL for Smarties"

ISBN-13: 978-1558609204

http://www.amazon.com/Hierarchies-Smarties-Kaufmann-Management-Systems/dp/1558609202/ref=sr_1_5/002-5405555-1788044?ie=UTF8&s=books&qid=1177533429&sr=1-5

set nocount on

set xact_abort on

go

if object_id('tempdb..#temp') is not null drop table #temp

if object_id('Product') is not null drop table Product

if object_id('Product_show_Tree') is not null drop procedure Product_show_Tree

go

create table Product

( ProductId intnot null

, ProductId_Parent int not null

, Description varchar(255) not null

, constraint Product_Pprimary key (ProductId)

, constraint Product_F_Product foreign key (ProductId_Parent) references Product (ProductId )

)

insert into Product values (0,0,'Drinks')

insert into Product values (100,0,'Drinks - Hot')

insert into Product values (2000,100,'Coffee')

insert into Product values (30000,100,'Coffee - French Blend')

insert into Product values (499999,2000,'Coffee - Breakfast Blend')

insert into Product values (59999,2000,'Coffee - Mocha')

insert into Product values (69999,30000,'Coffee - Capacino')

insert into Product values (7999999,0,'Drinks - Cold')

insert into Product values (89976,7999999,'Soda')

insert into Product values (967676,7999999,'Gatorade')

go

create procedure Product_show_Tree

(@ProductIdinteger )

as

set nocount on

set xact_abort on

select ProductId

,ProductId_Parent

,Description

, 0 as DepthCount

, CAST ( Product.ProductId as VARBINARY(6000) ) AS Hierachy

into #Temp

from Product

where ProductId_Parent = @ProductId

declare @DepthCount int

set @DepthCount = 0

while @@rowcount > 0

begin

set @DepthCount = @DepthCount + 1

insert into #Temp

select Product.ProductId

,Product.ProductId_Parent

,Product.Description

, @DepthCount

, T.Hierachy + CAST ( Product.ProductId as VARBINARY(6000) )

from Product

join #Temp T

on Product.ProductId_Parent = T.ProductId

and Product.ProductId_Parent != Product.ProductId

WHERET.DepthCount = @DepthCount - 1

end

select ProductId, DepthCount, ProductId_Parent, Description , Hierachy from #temp

order by Hierachy

go

exec Product_show_Tree @ProductId = 0 -- Show everything

go

exec Product_show_Tree @ProductId = 100 -- Only Coffee

go

SQL = Scarcely Qualifies as a Language

Sam-246632 Old Hand Points: 336 More actions · Answer 13

Thanks for the reply.

But my structure of the table is something like this

GROUP PRODUCT

ID ID

GROUP_ID GROUP_ID

NAME NAME

DESC DESC

DATA FOR TABLE GROUP

1 0 FOOD FOOD

2 0 DRINKS BEVERAGES

3 0 OTHERS OTHER

4 1 CAKES

5 1 DESSERT

6 1 MAIN COURSE

7 2 HOT DRINKS

8 2 COLD DRINKS

9 2 ALCOHOL

DATA FOR TABLE PRODUCT

100 7 TEA

101 7 COFFEE

102 7 CAPPUCCINO

103 8 COKE

104 8 JUICE

105 8 MILK

106 9 BEER

107 9 WINE

When the user selects Drinks, it should display the data in the following way.

0 DRINKS BEVERAGES

2 HOT DRINKS

7 TEA

7 COFFEE

7 CAPPUCCINO

2 COLD DRINKS

8 COKE

8 JUICE

8 MILK

2 ALCOHOL

9 BEER

9 WINE

Hope you got it.

Thanks in advance.

Carl Federl One Orange Chip Points: 25384 More actions · Answer 14

Please provide DDL and sample data.

See http://www.aspfaq.com/etiquette.asp?id=5006

SQL = Scarcely Qualifies as a Language