Post reply

Tree View Data Report

  • 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.

  • April 16, 2007 at 9:33 am

    #700723

    Any help would be greatly apprecited.

    Thanks

    Sam.

  • April 16, 2007 at 6:39 pm

    #700810

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

  • April 16, 2007 at 6:49 pm

    #700811

    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.

  • April 16, 2007 at 8:01 pm

    #700817

    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.

  • April 17, 2007 at 8:23 am

    #700929

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

    Thanks

    Sam.

  • April 17, 2007 at 11:58 am

    #700985

    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 Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 17, 2007 at 1:28 pm

    #701007

    Thanks. This works for 2000 too.

  • April 18, 2007 at 5:12 am

    #701085

    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 Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • April 18, 2007 at 12:28 pm

    #701220

    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.

     

  • April 19, 2007 at 6:00 pm

    #701606

    Any help would be greatly appreciated. Thanks.

  • April 25, 2007 at 9:58 am

    #702422

    Any suggestions how to accomplish this.

    Thanks in advance.

  • April 25, 2007 at 2:41 pm

    #702493

    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

  • April 25, 2007 at 6:23 pm

    #702512

    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.

     

     

     

     

  • April 26, 2007 at 9:37 am

    #702650

    Please provide DDL and sample data.

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

    SQL = Scarcely Qualifies as a Language

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

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