Displaying Records in a Hierarchy

• Ali Kazim Gardezi

  Grasshopper

  Points: 11

  More actions

  October 9, 2004 at 7:26 pm

  #182091

  Hi,

  I need to display data (items purchased in a month) in a

  hierarchy like below:

  2004                             10

        Dec                         05

        Nov                         03

        Jul                           02

  2003                              07

        Nov                         04

        Aug                         03

  I want to return it in a single query and I'm using TreeView web

  control to display it on webpage for this tree like structure.

  Here is the DDL:

  CREATE TABLE Books

  (

   BookID             INT,

   BookName         VARCHAR2(50),

   PurchaseDate    DATE

  );

  INSERT INTO Books VALUES (1, 'XYZ', '11/10/2004');

  INSERT INTO Books VALUES (2, 'XYZ', '12/10/2004');

  .

  .

  .

  I'm able to return data in following manner:

  2004              Dec               05

  2004              Nov               03

  2004              Jul                 02

  2003              Nov               04

  2003              Aug               03

  Using this query:

  SELECT YEAR(PurchaseDate), MONTH(PurchaseDate), COUNT(*)

  FROM PRPressRms

  GROUP BY YEAR(PRPressRmDate), MONTH(PRPressRmDate)

  But I need to return data in following manner:

  ID             ITEM              TOTAL                PARENT

  1              2004              10

  2              Dec                05                         1

  3              Nov                03                         1

  4              Jul                  02                         2

  5              2005               07                        

  6              Dec                04                         5

  7              Nov                03                         5

  Am I doing the right way? Or do I need to change my approach to

  this problem?

  Any help will be highly appriciated.

  Regards

  Ali

• Razvan Socol

  SSCarpal Tunnel

  Points: 4811

  More actions

  October 10, 2004 at 9:34 am

  #526059

  Although your DDL suggests that you are using Oracle, I will give you two T-SQL solutions (because this is a MS SQL Server site). Neither of them provides exactly the requested result, but I think they are close enough to give you some clues on how to solve the problem.

  SELECT CASE WHEN MonthlyGroup=0 THEN DATENAME(MONTH,DATEADD(MONTH,PurchaseMonth-1,0)) 
   ELSE CONVERT(varchar(20),PurchaseYear) END AS Item, Total, 
   CASE WHEN MonthlyGroup=0 THEN PurchaseYear ELSE NULL END AS Parent
  FROM (
   SELECT YEAR(PurchaseDate) AS PurchaseYear, MONTH(PurchaseDate) AS PurchaseMonth,
    COUNT(*) AS Total, GROUPING(MONTH(PurchaseDate)) AS MonthlyGroup
   FROM Books GROUP BY YEAR(PurchaseDate), MONTH(PurchaseDate) WITH ROLLUP
   HAVING GROUPING(YEAR(PurchaseDate))=0
  )A ORDER BY PurchaseYear, MonthlyGroup DESC, PurchaseMonth

  Item                           Total       Parent      
  ------------------------------ ----------- ----------- 
  2004                           10          NULL
  July                           2           2004
  November                       3           2004
  December                       5           2004
  2005                           7           NULL
  November                       3           2005
  December                       4           2005

  (7 row(s) affected)

  DECLARE @Result TABLE (
   ID int IDENTITY,
   Item varchar(30),
   Total int,
   Parent int
  )

  INSERT INTO @Result (Item, Total)
  SELECT YEAR(PurchaseDate), COUNT(*)
  FROM Books GROUP BY YEAR(PurchaseDate)

  INSERT INTO @Result (Item, Total, Parent)
  SELECT PurchaseMonthName, Total, (SELECT ID FROM @Result WHERE Item=PurchaseYear) as Parent
  FROM (
   SELECT MONTH(PurchaseDate) as PurchaseMonth, DATENAME(MONTH,PurchaseDate) as PurchaseMonthName, 
    YEAR(PurchaseDate) as PurchaseYear, COUNT(*) as Total
   FROM Books GROUP BY MONTH(PurchaseDate), DATENAME(MONTH,PurchaseDate), YEAR(PurchaseDate)
  )A ORDER BY PurchaseYear, PurchaseMonth

  SELECT * FROM @Result

  ID          Item                           Total       Parent      
  ----------- ------------------------------ ----------- ----------- 
  1           2004                           10          NULL
  2           2005                           7           NULL
  3           July                           2           1
  4           November                       3           1
  5           December                       5           1
  6           November                       3           2
  7           December                       4           2

  (7 row(s) affected)

  Razvan