December 4, 2011 at 11:54 pm
hello
i have one table demo..
USE [chk]
GO
/****** Object: Table [dbo].[Demo] Script Date: 12/05/2011 12:16:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Demo](
[AC_ID] [bigint] ,
[RootId] [bigint] NULL,
[parentID] [bigint] NULL,
[Type] [varchar](50) NULL,
[Name] [varchar](50) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Demo Data For This Table
INSERT INTO [demo] VALUES(1,1,0,'G','Group1')
INSERT INTO [demo] VALUES(2,2,0,'G','Group2')
INSERT INTO [demo] VALUES(3,3,0,'G','Group3')
INSERT INTO [demo] VALUES(4,4,0,'G','Group4')
INSERT INTO [demo] VALUES(5,1,1,'S','SubGroup11')
INSERT INTO [demo] VALUES(6,1,1,'S','SubGroup12')
INSERT INTO [demo] VALUES(7,1,5,'S','SubSubGroup11')
INSERT INTO [demo] VALUES(8,1,5,'S','SubSubGroup12')
INSERT INTO [demo] VALUES(9,2,2,'S','SubGroup21')
INSERT INTO [demo] VALUES(10,2,9,'S','SUBSubGroup21')
INSERT INTO [demo] VALUES(11,2,9,'S','SubSubGroup22')
INSERT INTO [demo] VALUES(12,3,3,'S','SubGroup31')
INSERT INTO [demo] VALUES(13,4,4,'S','SubGroup41')
INSERT INTO [demo] VALUES(14,3,12,'S','SubSubGroup31')
INSERT INTO [demo] VALUES(15,4,13,'S','SubSubGroup41')
INSERT INTO [demo] VALUES(16,3,14,'S','SubSubGroup32')
INSERT INTO [demo] VALUES(17,1,5,'A','AC11')
INSERT INTO [demo] VALUES(18,1,5,'A','AC12')
INSERT INTO [demo] VALUES(19,1,7,'A','AC13')
INSERT INTO [demo] VALUES(20,1,8,'A','AC14')
INSERT INTO [demo] VALUES(21,2,9,'A','AC21')
INSERT INTO [demo] VALUES(22,2,9,'A','AC22')
INSERT INTO [demo] VALUES(23,2,11,'A','AC23')
INSERT INTO [demo] VALUES(24,2,11,'A','AC24')
I want to show this data in hierarchy form
1 group
Than Sub Group
and so on....
My Try code is...
alter PROC [dbo].[ShowHierarchy]
@Root int
as
DECLARE @ACID int, @ACName varchar(500),@ACType varchar(50),@ACType1 varchar(50)
SET @ACName = (SELECT [Name] FROM dbo.Demo WHERE AC_ID= @Root)
SET @ACType = (SELECT [Type] FROM dbo.Demo WHERE AC_ID= @Root)
SET @ACType1 = (SELECT [Type] FROM dbo.Demo WHERE AC_ID= @Root)
print REPLICATE('-', @@NESTLEVEL * 4) + @ACName+'--' --+ @root
SET @ACID = (SELECT MIN(AC_ID) FROM Demo WHERE parentID= @Root)
WHILE @ACID IS NOT NULL
BEGIN
EXEC dbo.ShowHierarchy @ACID
SET @ACID = (SELECT MIN(AC_ID) FROM Demo WHERE parentID = @Root AND AC_ID > @ACID)
END
When i will Run
this
--exec ShowHierarchy 1
Then it Will show result in this formate
----Group1--
--------SubGroup11--
------------SubSubGroup11--
----------------AC13--
------------SubSubGroup12--
----------------AC14--
------------AC11--
------------AC12--
--------SubGroup12--
but i want after --------SubGroup11--
it show
------------AC11--
------------AC12--
because both account under
--------SubGroup11--
then show other subsub group and account...
How can i do this.. Please Help..
Thanks...
.
December 5, 2011 at 12:29 am
You have two pieces of code similar to this one:
SELECT MIN(AC_ID) FROM Demo WHERE parentID= @Root
Replace this with
select top 1 AC_ID FROM Demo where parentId=@Root order by [Type],[Name], and perform a similar modification to the second place you retrieve AC_ID, and I think you have what you want.
December 5, 2011 at 12:40 am
In That Case This Will Not Give Full Result
Its Only Give
----Group1--
--------SubGroup11--
------------AC11--
------------AC12--
--------SubGroup12--
December 5, 2011 at 3:08 am
Please Help me On this Problem....
December 5, 2011 at 4:11 am
Having looked somewhat more closely on your case, it appears that your issue is a design flaw, where you expect the AC_ID of an "entry" always to be less than the subgroups at the same level.
December 5, 2011 at 4:26 am
Yes..
Actually I am Getting All my result....
But is not showing in format which i want...
i want first show group
then subgroup
then subgroup account if there is any
Then other sub sub group and their account
...........
I tried so many thing But not getting...
December 5, 2011 at 7:17 am
If data presentation is your issue, then SQL Server is not the right place to look for the answers. It should be managed at Presentation Layer (application).
December 5, 2011 at 7:49 am
But in front end i can't rearrange this data....
for this i again need recursion..
December 5, 2011 at 7:50 am
But in front end i can't rearrange this data....
for this i again need recursion..
so this is more time consuming....
December 5, 2011 at 7:55 am
dilip.aim11 (12/5/2011)
But in front end i can't rearrange this data....for this i again need recursion..
You can't (some limitations) / you don't know. In second case, I would recommend you to learn it.
Please try to understand Data Presentation / Formatting should be done on Presentation Layer ONLY. They are well designed for it.
Loops / Recursions in SQL (or any RDBMS) are not very efficient. You can do it but it doesn’t mean you should do it.
December 7, 2011 at 3:15 am
This I Was Try To Do...
Thanks To every One.. For give useful suggestion...
ALTER procedure [dbo].[ShowHierarchy]
@Root int
as
DECLARE @ACID int, @ACName varchar(500),@ACType varchar(50),@ACType1 varchar(50)
Select @ACName = Name, @ACType =[Type]
from dbo.Demo where AC_ID = @Root
print REPLICATE('-', @@NESTLEVEL * 4) + @ACName+'--'; --+ @root
SET @ACID = (SELECT Top 1 AC_ID FROM Demo WHERE parentID = @Root Order by Name)
Select @ACName = Name, @ACType =[Type] From dbo.Demo where AC_ID = @ACID
WHILE @ACID IS NOT NULL
BEGIN
EXEC dbo.ShowHierarchy @ACID
SET @ACID = (SELECT Top 1 AC_ID FROM Demo WHERE parentID = @Root AND Name > @ACName Order by Name)
Select @ACName = Name, @ACType =[Type] From dbo.Demo where AC_ID = @ACID
END
December 7, 2011 at 7:20 am
You can obtain the same result by using a CTE, which is prefect for retrieving results that contain a hierarchy, like so (I used a table variable for demo purposes):
set nocount on;
declare @demo TABLE (
[AC_ID] [bigint] ,
[RootId] [bigint] NULL,
[parentID] [bigint] NULL,
[Type] [varchar](50) NULL,
[Name] [varchar](50) NULL
);
INSERT INTO @demo VALUES(1,1,0,'G','Group1')
INSERT INTO @demo VALUES(2,2,0,'G','Group2')
INSERT INTO @demo VALUES(3,3,0,'G','Group3')
INSERT INTO @demo VALUES(4,4,0,'G','Group4')
INSERT INTO @demo VALUES(5,1,1,'S','SubGroup11')
INSERT INTO @demo VALUES(6,1,1,'S','SubGroup12')
INSERT INTO @demo VALUES(7,1,5,'S','SubSubGroup11')
INSERT INTO @demo VALUES(8,1,5,'S','SubSubGroup12')
INSERT INTO @demo VALUES(9,2,2,'S','SubGroup21')
INSERT INTO @demo VALUES(10,2,9,'S','SUBSubGroup21')
INSERT INTO @demo VALUES(11,2,9,'S','SubSubGroup22')
INSERT INTO @demo VALUES(12,3,3,'S','SubGroup31')
INSERT INTO @demo VALUES(13,4,4,'S','SubGroup41')
INSERT INTO @demo VALUES(14,3,12,'S','SubSubGroup31')
INSERT INTO @demo VALUES(15,4,13,'S','SubSubGroup41')
INSERT INTO @demo VALUES(16,3,14,'S','SubSubGroup32')
INSERT INTO @demo VALUES(17,1,5,'A','AC11')
INSERT INTO @demo VALUES(18,1,5,'A','AC12')
INSERT INTO @demo VALUES(19,1,7,'A','AC13')
INSERT INTO @demo VALUES(20,1,8,'A','AC14')
INSERT INTO @demo VALUES(21,2,9,'A','AC21')
INSERT INTO @demo VALUES(22,2,9,'A','AC22')
INSERT INTO @demo VALUES(23,2,11,'A','AC23')
INSERT INTO @demo VALUES(24,2,11,'A','AC24');
with cteDemo as
(
select [d].[AC_ID]
, [d].[RootId]
, [d].[parentID]
, [d].[Type]
, [d].[Name]
, convert(varchar(100), row_number() over (order by [d].[Name])) as [sort_column]
, 4 as [level]
from @demo as d
where [d].[parentID] = 0
union all
select [cd].[AC_ID]
, [cd].[RootId]
, [cd].[parentID]
, [cd].[Type]
, [cd].[Name]
, convert(varchar(100), [pd].[sort_column] + convert(varchar(100), row_number() over (order by [cd].[Type], [cd].[Name])))
, 4 + [pd].[level]
from @demo as cd
inner join [cteDemo] as pd on [cd].[parentID] = [pd].[AC_ID]
)
select [cteDemo].[AC_ID]
, [cteDemo].[RootId]
, [cteDemo].[parentID]
, [cteDemo].[Type]
, [cteDemo].[Name]
, [cteDemo].[sort_column]
, [cteDemo].[level]
, replicate('-', [cteDemo].[level]) + [cteDemo].[Name] + '--' as [graph]
from [cteDemo]
where [cteDemo].[RootId] = 1
order by [cteDemo].[sort_column] ;
In which case you could only return the results in the correct order and provide "make-up" on the client-side.
_____________________________________________________
Do not go past the mark you aimed for, but learn when to stop.
You can find me on LinkedIn.
I support The Programmer's Bill of Rights.
MCITP, MCDBA, MCSD
December 8, 2011 at 4:06 am
Hi..
When I am Using Ur Same Query .. Or My Query in this Set of Data My Result Not Meet My Requirement
INSERT INTO [dnm] VALUES(5,5,0,'G','Expenditure')
INSERT INTO [dnm] VALUES(14,5,5,'S','Goods for resale')
INSERT INTO [dnm] VALUES(15,5,5,'S','Overheads')
INSERT INTO [dnm] VALUES(16,5,15,'S','Depreciation')
INSERT INTO [dnm] VALUES(51,5,14,'A','Goods')
INSERT INTO [dnm] VALUES(52,5,14,'A','Delivery Charges')
INSERT INTO [dnm] VALUES(53,5,15,'A','Wages')
INSERT INTO [dnm] VALUES(54,5,15,'A','Rent')
INSERT INTO [dnm] VALUES(55,5,15,'A','Rates')
INSERT INTO [dnm] VALUES(56,5,15,'A','Insurance')
INSERT INTO [dnm] VALUES(57,5,15,'A','Heat & Light')
INSERT INTO [dnm] VALUES(58,5,15,'A','Telephone')
INSERT INTO [dnm] VALUES(59,5,15,'A','Stationery')
INSERT INTO [dnm] VALUES(60,5,15,'A','Advertising')
INSERT INTO [dnm] VALUES(61,5,15,'A','Travel and Subsistence')
INSERT INTO [dnm] VALUES(62,5,15,'A','Motor Expenses')
INSERT INTO [dnm] VALUES(63,5,15,'A','Leases')
INSERT INTO [dnm] VALUES(64,5,15,'A','Licenses')
INSERT INTO [dnm] VALUES(65,5,15,'A','Repairs & Renewals')
INSERT INTO [dnm] VALUES(66,5,15,'A','Staff Welfare & Cleaning')
INSERT INTO [dnm] VALUES(67,5,15,'A','Sundry Expenses')
INSERT INTO [dnm] VALUES(68,5,15,'A','Accountancy')
INSERT INTO [dnm] VALUES(69,5,15,'A','Legal & Professional')
INSERT INTO [dnm] VALUES(70,5,15,'A','Entertainment')
INSERT INTO [dnm] VALUES(71,5,15,'A','Wages')
INSERT INTO [dnm] VALUES(72,5,16,'A','Intangible Assets Amortisation')
INSERT INTO [dnm] VALUES(73,5,16,'A','Motor Vehicles Depreciation')
INSERT INTO [dnm] VALUES(74,5,16,'A','Buildings Depreciation')
INSERT INTO [dnm] VALUES(75,5,16,'A','Fixtures and Fittings Depreciation')
INSERT INTO [dnm] VALUES(76,5,16,'A','Office Equipment Depreciation')
INSERT INTO [dnm] VALUES(77,5,16,'A','Computer Software Depreciation')
INSERT INTO [dnm] VALUES(78,5,16,'A','Bank Charges & Interest')
INSERT INTO [dnm] VALUES(79,5,16,'A','Intangible Assets Amortisation')
it showing some level 12 record at last...
Why i not understand...
December 8, 2011 at 6:18 am
I'm not 100% sure that this is what you mean, but I changed the way the sorting is handled by the query. Instead of using the row_number() function, I have concatenated the names of each record. For giving a proper order to the subgroup and the account types, I have used a case statement. Eventually this is the result, I have included both sets of testdata so that you can quickly check the results:
set nocount on;
declare @demo TABLE (
[AC_ID] [bigint] ,
[RootId] [bigint] NULL,
[parentID] [bigint] NULL,
[Type] [varchar](50) NULL,
[Name] [varchar](50) NULL
);
INSERT INTO @demo VALUES(5,5,0,'G','Expenditure')
INSERT INTO @demo VALUES(14,5,5,'S','Goods for resale')
INSERT INTO @demo VALUES(15,5,5,'S','Overheads')
INSERT INTO @demo VALUES(16,5,15,'S','Depreciation')
INSERT INTO @demo VALUES(51,5,14,'A','Goods')
INSERT INTO @demo VALUES(52,5,14,'A','Delivery Charges')
INSERT INTO @demo VALUES(53,5,15,'A','Wages')
INSERT INTO @demo VALUES(54,5,15,'A','Rent')
INSERT INTO @demo VALUES(55,5,15,'A','Rates')
INSERT INTO @demo VALUES(56,5,15,'A','Insurance')
INSERT INTO @demo VALUES(57,5,15,'A','Heat & Light')
INSERT INTO @demo VALUES(58,5,15,'A','Telephone')
INSERT INTO @demo VALUES(59,5,15,'A','Stationery')
INSERT INTO @demo VALUES(60,5,15,'A','Advertising')
INSERT INTO @demo VALUES(61,5,15,'A','Travel and Subsistence')
INSERT INTO @demo VALUES(62,5,15,'A','Motor Expenses')
INSERT INTO @demo VALUES(63,5,15,'A','Leases')
INSERT INTO @demo VALUES(64,5,15,'A','Licenses')
INSERT INTO @demo VALUES(65,5,15,'A','Repairs & Renewals')
INSERT INTO @demo VALUES(66,5,15,'A','Staff Welfare & Cleaning')
INSERT INTO @demo VALUES(67,5,15,'A','Sundry Expenses')
INSERT INTO @demo VALUES(68,5,15,'A','Accountancy')
INSERT INTO @demo VALUES(69,5,15,'A','Legal & Professional')
INSERT INTO @demo VALUES(70,5,15,'A','Entertainment')
INSERT INTO @demo VALUES(71,5,15,'A','Wages')
INSERT INTO @demo VALUES(72,5,16,'A','Intangible Assets Amortisation')
INSERT INTO @demo VALUES(73,5,16,'A','Motor Vehicles Depreciation')
INSERT INTO @demo VALUES(74,5,16,'A','Buildings Depreciation')
INSERT INTO @demo VALUES(75,5,16,'A','Fixtures and Fittings Depreciation')
INSERT INTO @demo VALUES(76,5,16,'A','Office Equipment Depreciation')
INSERT INTO @demo VALUES(77,5,16,'A','Computer Software Depreciation')
INSERT INTO @demo VALUES(78,5,16,'A','Bank Charges & Interest')
INSERT INTO @demo VALUES(79,5,16,'A','Intangible Assets Amortisation')
INSERT INTO @demo VALUES(1,1,0,'G','Group1')
INSERT INTO @demo VALUES(2,2,0,'G','Group2')
INSERT INTO @demo VALUES(3,3,0,'G','Group3')
INSERT INTO @demo VALUES(4,4,0,'G','Group4')
INSERT INTO @demo VALUES(5,1,1,'S','SubGroup11')
INSERT INTO @demo VALUES(6,1,1,'S','SubGroup12')
INSERT INTO @demo VALUES(7,1,5,'S','SubSubGroup11')
INSERT INTO @demo VALUES(8,1,5,'S','SubSubGroup12')
INSERT INTO @demo VALUES(9,2,2,'S','SubGroup21')
INSERT INTO @demo VALUES(10,2,9,'S','SUBSubGroup21')
INSERT INTO @demo VALUES(11,2,9,'S','SubSubGroup22')
INSERT INTO @demo VALUES(12,3,3,'S','SubGroup31')
INSERT INTO @demo VALUES(13,4,4,'S','SubGroup41')
INSERT INTO @demo VALUES(14,3,12,'S','SubSubGroup31')
INSERT INTO @demo VALUES(15,4,13,'S','SubSubGroup41')
INSERT INTO @demo VALUES(16,3,14,'S','SubSubGroup32')
INSERT INTO @demo VALUES(17,1,5,'A','AC11')
INSERT INTO @demo VALUES(18,1,5,'A','AC12')
INSERT INTO @demo VALUES(19,1,7,'A','AC13')
INSERT INTO @demo VALUES(20,1,8,'A','AC14')
INSERT INTO @demo VALUES(21,2,9,'A','AC21')
INSERT INTO @demo VALUES(22,2,9,'A','AC22')
INSERT INTO @demo VALUES(23,2,11,'A','AC23')
INSERT INTO @demo VALUES(24,2,11,'A','AC24')
;
with cteDemo as
(
select [d].[AC_ID]
, [d].[RootId]
, [d].[parentID]
, [d].[Type]
, [d].[Name]
, convert(varchar(500), [d].[Type] + '-' + [d].[Name]) as [sort_column]
, 4 as [level]
from @demo as d
where [d].[parentID] = 0
union all
select [cd].[AC_ID]
, [cd].[RootId]
, [cd].[parentID]
, [cd].[Type]
, [cd].[Name]
, convert(varchar(500), [pd].[sort_column] + '-' + case [cd].[Type] when 'S' then '2S' when 'A' then '1A' end + '-' + [cd].[Name])
, 4 + [pd].[level]
from @demo as cd
inner join [cteDemo] as pd on [cd].[parentID] = [pd].[AC_ID]
)
select [cteDemo].[AC_ID]
, [cteDemo].[RootId]
, [cteDemo].[parentID]
, [cteDemo].[Type]
, [cteDemo].[Name]
, [cteDemo].[sort_column]
, [cteDemo].[level]
, replicate('-', [cteDemo].[level]) + [cteDemo].[Name] + '--' as [graph]
from [cteDemo]
where [cteDemo].[RootId] = 5
order by [cteDemo].[sort_column] ;
Now instead of this tree
----Expenditure--
--------Goods for resale--
------------Delivery Charges--
------------Goods--
--------Overheads--
------------Accountancy--
------------Rates--
------------Rent--
------------Repairs & Renewals--
------------Staff Welfare & Cleaning--
------------Stationery--
------------Sundry Expenses--
------------Telephone--
------------Travel and Subsistence--
------------Wages--
------------Wages--
------------Advertising--
------------Depreciation--
----------------Bank Charges & Interest--
----------------Buildings Depreciation--
----------------Computer Software Depreciation--
----------------Fixtures and Fittings Depreciation--
----------------Intangible Assets Amortisation--
----------------Intangible Assets Amortisation--
----------------Motor Vehicles Depreciation--
----------------Office Equipment Depreciation--
------------Entertainment--
------------Heat & Light--
------------Insurance--
------------Leases--
------------Legal & Professional--
------------Licenses--
------------Motor Expenses--
you get this one:
----Expenditure--
--------Goods for resale--
------------Delivery Charges--
------------Goods--
--------Overheads--
------------Accountancy--
------------Advertising--
------------Entertainment--
------------Heat & Light--
------------Insurance--
------------Leases--
------------Legal & Professional--
------------Licenses--
------------Motor Expenses--
------------Rates--
------------Rent--
------------Repairs & Renewals--
------------Staff Welfare & Cleaning--
------------Stationery--
------------Sundry Expenses--
------------Telephone--
------------Travel and Subsistence--
------------Wages--
------------Wages--
------------Depreciation--
----------------Bank Charges & Interest--
----------------Buildings Depreciation--
----------------Computer Software Depreciation--
----------------Fixtures and Fittings Depreciation--
----------------Intangible Assets Amortisation--
----------------Intangible Assets Amortisation--
----------------Motor Vehicles Depreciation--
----------------Office Equipment Depreciation--
Is that a solution to your problem?
_____________________________________________________
Do not go past the mark you aimed for, but learn when to stop.
You can find me on LinkedIn.
I support The Programmer's Bill of Rights.
MCITP, MCDBA, MCSD
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply