| Author |
Topic |
|
raymon90
Starting Member
7 Posts |
 Posted - 2011-12-07 : 06:03:53
|
| Hi all, first time posting here. I'm trying to create a table using CTE.Is it possible to add sub total for each parent and add grand total as the last row. Below is the illustration of the table i would like to create.Parent 1 Child 1.1 Grandchild 1.1.1 Grandchild 1.1.2 Child 1.2 Grandchild 1.2.1 Sub Total Parent 2 Child 2.1 Grandchild 2.1.1 Grandchild 2.1.2 Child 2.2 Grandchild 2.2.1 Sub Total Grand Total Thanks,Raymon. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-07 : 06:41:51
|
| its very easy if you're doing this in a report. which is your front end tool by the way?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
raymon90
Starting Member
7 Posts |
Posted - 2011-12-07 : 06:50:07
|
| Umm.. not really sure what do you mean by front end tool. I'm using SQL Server 2008 R2. How do you make such query? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-07 : 06:58:42
|
| i'm asking where you want to show the data?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
raymon90
Starting Member
7 Posts |
Posted - 2011-12-07 : 07:04:28
|
| Ohh.. in a word document. I'm generating a report using coldfusion + a document generator tool called aspose. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-07 : 07:45:52
|
quote:
Originally posted by raymon90
Ohh.. in a word document. I'm generating a report using coldfusion + a document generator tool called aspose.
coldfusion should have a way to add the subtotals easily. most of reporting tools have itif you want to get this in sql you need to use WITH CUBE or WITH ROLLUP statements------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
raymon90
Starting Member
7 Posts |
Posted - 2011-12-07 : 21:47:33
|
| Hmm.. Do you have any sample code? Actually I just want to make it works first in SQL Server. Then I need to make some modification. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-08 : 00:45:03
|
quote:
Originally posted by raymon90
Hmm.. Do you have any sample code? Actually I just want to make it works first in SQL Server. Then I need to make some modification.
see the belowhttp://databases.about.com/od/sql/l/aacuberollup.htm------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
raymon90
Starting Member
7 Posts |
Posted - 2011-12-09 : 01:43:45
|
| Thanks for the link. But can is it possible to use Cube or Rollup with Common Table Expression? I'm doing a parent-child query for the table. Thanks. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-09 : 01:52:23
|
| its not possible inside CTE. why not use it in select statement from cte?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
raymon90
Starting Member
7 Posts |
Posted - 2011-12-09 : 05:20:38
|
| Still have no idea on how to make the query. This is the sample table which I would like to create: http://imageshack.us/f/856/page17q.jpg/Have any idea on how to query the table? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-12-09 : 05:24:25
|
| sorry cant view link here. will take a look later and reply------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
raymon90
Starting Member
7 Posts |
Posted - 2011-12-14 : 00:18:30
|
| Hi Visakh, have you checked out the image link I posted? Got any idea on how to create the query? Thanks. |
|
|
|
|
Hierarchical Query with Subtotal