SQL - Parent/Child Query

Dear experts,

I have got the attached SQL Table, and I need to have a query that outputs the columns in yellow from that table (groupLevel1, groupLevel2, etc…)
The idea is to have each column of these display the category name for all following ancestors, until it reaches the last ancestor. This means the columns are not going to be only four. If there are 10 levels, there will be groupLevel10!

Hope that is clear!
Thank you in advance,
   
ee-parentchild.png
feesuAsked:
Who is Participating?
 
ralmadaCommented:
try step 2 again
;with CTE as (
	select id, parentid, category, [order], level, cast(category as varchar(max)) as tree from yourtable
	where parentid is null
	union all
	select b.id, b.parentid, b.category, b.[order], b.level, cast(a.tree + '\' + b.category as varchar(max))
	from CTE a
	inner join yourtable b on b.parentid = a.id
)
select * 
into #hier2
from CTE
order by id
option(maxrecursion 0)

Open in new window

0
 
z_alexCommented:
In that case you can use the sql tree.
Try to check the link below
http://www.codeproject.com/KB/database/Trees_in_SQL_databases.aspx
0
 
feesuAuthor Commented:
I don't think I need that complexity. I only want my query to return these extra columns based on the criteria above!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
feesuAuthor Commented:
rashmi,

I still don't know how to create those dynamic columns I mentioned above in the query!

Thanks,
0
 
ralmadaCommented:
feesu, can you please rephrase your question? Also please post: (1) some sample data of your current table and (2) what is the expected result (what you want from the query) It is important that you post both the input and the out along with the rationale in order for us to give you an accurate resposne.
 
0
 
ralmadaCommented:
In the meantime, you will find it easier to do it this way (in just one column). See example below along with resultset:
declare @hier table (
	id int,
	parentid int,
	category varchar(20),
	[order] int,
	level int
)

insert @hier values(1, 5, 'rent', 1, 3)
insert @hier values(2, 1, 'House Rent', 1, 4)
insert @hier values(3, 1, 'Office Rent', 2, 4)
insert @hier values(4, 1, 'Car Rent', 3, 4)
insert @hier values(5, 9, 'Revenue', 1, 2)
insert @hier values(6, 9, 'Expenses', 2, 2)
insert @hier values(7, 6, 'Salaries', 1, 3)
insert @hier values(8, 6, 'Others', 2, 3)
insert @hier values(9, null, 'Operations', 1, 1)
insert @hier values(10, null, 'Investment', 2, 1)


select * from @hier

;with CTE as (
	select id, parentid, category, [order], level, cast(category as varchar(max)) as tree from @hier
	where parentid is null
	union all
	select b.id, b.parentid, b.category, b.[order], b.level, a.tree + '\' + b.category
	from CTE a
	inner join @hier b on b.parentid = a.id
)
select * from CTE
order by id
option(maxrecursion 0)

-And here is the result
id          parentid    category             order       level       tree
----------- ----------- -------------------- ----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           5           rent                 1           3           Operations\Revenue\rent
2           1           House Rent           1           4           Operations\Revenue\rent\House Rent
3           1           Office Rent          2           4           Operations\Revenue\rent\Office Rent
4           1           Car Rent             3           4           Operations\Revenue\rent\Car Rent
5           9           Revenue              1           2           Operations\Revenue
6           9           Expenses             2           2           Operations\Expenses
7           6           Salaries             1           3           Operations\Expenses\Salaries
8           6           Others               2           3           Operations\Expenses\Others
9           NULL        Operations           1           1           Operations
10          NULL        Investment           2           1           Investment

(10 row(s) affected)

Open in new window

0
 
feesuAuthor Commented:
Hi ralmada,

My table structure is as the snapshot above without the output columns in yellow. So it is (ID, ParentID, Category, Order, Level).

The data is same as the data I have in my table.

And I want using that structure and data a select statement that generates those extra columns in yellow (GroupHeader_1,2,3 etc...)

Thanks!
0
 
ralmadaCommented:
In my opinion, my suggestion above will be your best bet to get that information.
If you still want separate columns instead of just one, then you will have to do something more complex, so:
 
 

----------------------------------------------------
1) Create the parmstolist function below:
----------------------------------------------------

create FUNCTION [dbo].[ParmsToList] (@Parameters varchar(8000), @delimiter varchar(10) ) 
returns @result TABLE (Value varchar(500), rn int identity) 
AS 
begin 
declare @dx varchar(9) 
-- declare @loops int 
--set @loops = 0 

DECLARE @TempList table 
( 
Value varchar(500) 
) 

if @delimiter is null set @delimiter = ' ' 
if len(@delimiter) < 1 set @delimiter = ' ' 
set @dx = left(@delimiter, case when @delimiter = ' ' then 1 else len(@delimiter) end -1) 

DECLARE @Value varchar(8000), @Pos int 

SET @Parameters = LTRIM(RTRIM(@Parameters))+ @delimiter 
SET @Pos = CHARINDEX(@delimiter, @Parameters, 1) 

IF REPLACE(@Parameters, @delimiter, @dx) <> '' 
BEGIN 
WHILE @Pos > 0 -- AND @Loops < 100 
BEGIN 
--set @loops = @loops + 1 
SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1))) 
IF @Value <> '' 
BEGIN 
INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion 
END 
SET @Parameters = SUBSTRING(@Parameters, @Pos+ case when @delimiter = ' ' then 1 else len(@delimiter) end, 8000) 
SET @Pos = CHARINDEX(@delimiter, @Parameters, 1) 

END 
END 
INSERT @result 
SELECT value 
FROM @TempList 
RETURN 
END

----------------------------------------------------
2) Run the follow query against your table to create a stage temp table called #hier2
----------------------------------------------------
;with CTE as (
	select id, parentid, category, [order], level, cast(category as varchar(max)) as tree from yourtable
	where parentid is null
	union all
	select b.id, b.parentid, b.category, b.[order], b.level, a.tree + '\' + b.category
	from CTE a
	inner join yourtable b on b.parentid = a.id
)
select * 
into #hier2
from CTE
order by id
option(maxrecursion 0)


----------------------------------------------------
3) Use the following dynamic SQL with the information from the temp table created in 2
----------------------------------------------------

declare @strSQL varchar(max)
declare @cols varchar(max)

set @cols = stuff((select distinct ', [' + cast(level as varchar) + ']' from yourtable order by 1 for xml path('')), 1, 2, '')



set @strSQL = 'select id, parentid, category, [order], level, ' + @cols + 
	' from (
		select a.id, a.parentid, a.category, a.[order], a.level, b.rn, b.Value
		from #hier2 a
		cross apply dbo.parmstolist(a.tree, '\') b
	) o
	pivot(max(Value) for rn in (' + @cols + ')) as p
	order by id'

exec(@strSQL)

Open in new window

0
 
z_alexCommented:

select groupLevel1, groupLevel2, groupLevel3, groupLevel4
from TABLENAME
group by groupLevel1, groupLevel2, groupLevel3, groupLevel4
0
 
feesuAuthor Commented:
Hi ralmada,

I shall try your code and get back to you. Thank you!
0
 
feesuAuthor Commented:
Hi ralmada,

I was able to create the function, but on executing the following query, I got the error below:

Msg 240, Level 16, State 1, Line 1
Types don't match between the anchor and the recursive part in column "tree" of recursive query "CTE".
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.