Join myself and display level - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Join myself and display level

Author

Topic

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-03-17 : 03:42:29

Hi all,
I have table with structure:

ID--------Name---------ParrentID
1---------A--------------NULL
2---------A1-------------1
3---------A2-------------1
4---------A11------------2
5---------A22------------3
.....
Now i want to display as :
ID--------Name---------ParrentID------Alias
1---------A--------------NULL---------A
2---------A1-------------1------------A_A1
3---------A2-------------1------------A_A2
4---------A11------------2------------A_A1_A11
5---------A22------------3------------A_A2_A22
...

Anyone help me ?
Thank you very much :)

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-17 : 03:52:30

You should look up "recursive cte" in books online (http://msdn.microsoft.com/en-us/library/ms186243.aspx)...

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 03:52:34

[code];WITH CTE(ID,Name,ParrentID,Alias)
AS
(
SELECT ID,Name,ParrentID,Name
FROM Table
UNION ALL
SELECT t.ID,t.Name,t.ParrentID,c.Alias + '_'+t.Name
FROM CTE c
JOIN Table t
ON t.ParrentID = c.ID
)

SELECT *
FROM CTE

OPTION (MAXRECURSION 0)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-03-17 : 03:55:02

Visakh created the solution for you in just 4 seconds longer than it took me to find the link in msdn. There is a reason some people are MVP's and some are not

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-17 : 04:56:04

quote:
Originally posted by Lumbago

Visakh created the solution for you in just 4 seconds longer than it took me to find the link in msdn. There is a reason some people are MVP's and some are not

- Lumbago
If the facts don't fit the theory, change the facts. Albert Einstein

Yes. He is very much specialised in T-SQL

Madhivanan

Failing to plan is Planning to fail

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-03-17 : 05:34:48

quote:
Originally posted by visakh16

;WITH CTE(ID,Name,ParrentID,Alias)
AS
(
SELECT ID,Name,ParrentID,Name
FROM Table
UNION ALL
SELECT t.ID,t.Name,t.ParrentID,c.Alias + '_'+t.Name
FROM CTE c
JOIN Table t
ON t.ParrentID = c.ID
)

SELECT *
FROM CTE

OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sorry, I get error message :
Types don't match between the anchor and the recursive part in column "Alias" of recursive query "CTE".

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-03-17 : 12:37:27

Anyone help me ? thank you very much :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 13:01:52

quote:
Originally posted by pamyral_279

Anyone help me ? thank you very much :)

what are datatypes of Alias and Name fields?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-03-17 : 23:02:36

quote:
Originally posted by visakh16

quote:
Originally posted by pamyral_279

Anyone help me ? thank you very much :)

what are datatypes of Alias and Name fields?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Both of them are also nvarchar type !
Can you edit help me ? Thank you !!

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-03-18 : 10:10:29

quote:
Originally posted by pamyral_279

quote:
Originally posted by visakh16

quote:
Originally posted by pamyral_279

Anyone help me ? thank you very much :)

what are datatypes of Alias and Name fields?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Both of them are also nvarchar type !
Can you edit help me ? Thank you !!

visakh16 please help me ! i really need your help !
Thank you very much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-18 : 11:52:17

then try casting second part also to nvarchar


;WITH CTE(ID,Name,ParrentID,Alias)
AS
(
SELECT ID,Name,ParrentID,CAST(Name AS nvarchar(1000))
FROM Table
UNION ALL
SELECT t.ID,t.Name,t.ParrentID,CAST(c.Alias + '_'+t.Name AS nvarchar(1000))
FROM CTE c
JOIN Table t
ON t.ParrentID = c.ID
)

SELECT *
FROM CTE

OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-03-18 : 12:59:36

quote:
Originally posted by visakh16

then try casting second part also to nvarchar

;WITH CTE(ID,Name,ParrentID,Alias)
AS
(
SELECT ID,Name,ParrentID,CAST(Name AS nvarchar(1000))
FROM Table
UNION ALL
SELECT t.ID,t.Name,t.ParrentID,CAST(c.Alias + '_'+t.Name AS nvarchar(1000))
FROM CTE c
JOIN Table t
ON t.ParrentID = c.ID
)

SELECT *
FROM CTE

OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thank visa very much but i find that in your query is wrong with my data.I test with following data:

Data in my table :
1------ A------ NULL
2------ B------ NULL
3------ C------ NULL
4------ D------ NULL
5------ A1----- 1
6------ A2----- 1
7------ B1----- 2
8------ B2----- 2
9------ B12-----7
10----- B123----9

Expected output:
ID------Name-----ParrentID-----Alias
1------ A------ NULL-----------A
2------ B------ NULL-----------B
3------ C------ NULL-----------C
4------ D------ NULL-----------D
5------ A1----- 1------------- A_A1
6------ A2----- 1------------- A_A2
7------ B1----- 2------------- B_B1
8------ B2----- 2------------- B_B2
9------ B12-----7------------- B_B1_B12
10----- B123----9------------- B_B1_B12_B123

Can you review for me ? Thank you again !!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-18 : 13:35:06

wat about this?


;WITH CTE(ID,Name,ParrentID,Alias)
AS
(
SELECT ID,Name,ParrentID,CAST(Name AS nvarchar(1000))
FROM Table
WHERE ParrentID IS NULL
UNION ALL
SELECT t.ID,t.Name,t.ParrentID,CAST(c.Alias + '_'+t.Name AS nvarchar(1000))
FROM CTE c
JOIN Table t
ON t.ParrentID = c.ID
)

SELECT *
FROM CTE

OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-18 : 14:53:20

With cte(id,name,p_id,Alias) as
(
select id, name,p_id,cast(name as nvarchar(50)) from rec_cte where p_id is null
union all
select t.id,t.name,t.p_id,cast(c.Alias + '_'+t.Name as nvarchar(50))
from rec_cte t
join cte c on t.p_id=c.p_id
)
select * from cte
option(maxrecursion 15)

table
1 A NULL
2 A1 1
3 A2 1
4 A11 2
5 A21 3

Result
1 A NULL A

pamyral_279
Posting Yak Master

143 Posts

Posted - 2010-03-18 : 22:39:53

quote:
Originally posted by visakh16

wat about this?

;WITH CTE(ID,Name,ParrentID,Alias)
AS
(
SELECT ID,Name,ParrentID,CAST(Name AS nvarchar(1000))
FROM Table
WHERE ParrentID IS NULL
UNION ALL
SELECT t.ID,t.Name,t.ParrentID,CAST(c.Alias + '_'+t.Name AS nvarchar(1000))
FROM CTE c
JOIN Table t
ON t.ParrentID = c.ID
)

SELECT *
FROM CTE

OPTION (MAXRECURSION 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Special thank visakh16 :) ! that's good sentence sql.

AAAV
Posting Yak Master

152 Posts

Posted - 2010-03-19 : 10:35:18

quote:
Originally posted by AAAV

With cte(id,name,p_id,Alias) as
(
select id, name,p_id,cast(name as nvarchar(50)) from rec_cte where p_id is null
union all
select t.id,t.name,t.p_id,cast(c.Alias + '_'+t.Name as nvarchar(50))
from rec_cte t
join cte c on t.p_id=c.p_id
)
select * from cte
option(maxrecursion 15)

table
1 A NULL
2 A1 1
3 A2 1
4 A11 2
5 A21 3

Result
1 A NULL A

visakh... it didnt work can you correct me what i am doing wrong?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-19 : 11:17:09

quote:
Originally posted by AAAV

quote:
Originally posted by AAAV

With cte(id,name,p_id,Alias) as
(
select id, name,p_id,cast(name as nvarchar(50)) from rec_cte where p_id is null
union all
select t.id,t.name,t.p_id,cast(c.Alias + '_'+t.Name as nvarchar(50))
from rec_cte t
join cte c on ~~t.p_id=c.p_id~~t.p_id=c.id
)
select * from cte
option(maxrecursion 15)

table
1 A NULL
2 A1 1
3 A2 1
4 A11 2
5 A21 3

Result
1 A NULL A

visakh... it didnt work can you correct me what i am doing wrong?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-19 : 11:17:34

quote:
Originally posted by visakh16

quote:
Originally posted by AAAV

quote:
Originally posted by AAAV

With cte(id,name,p_id,Alias) as
(
select id, name,p_id,cast(name as nvarchar(50)) from rec_cte where p_id is null
union all
select t.id,t.name,t.p_id,cast(c.Alias + '_'+t.Name as nvarchar(50))
from rec_cte t
join cte c on ~~t.p_id=c.p_id~~t.p_id=c.id
)
select * from cte
option(maxrecursion 15)

table
1 A NULL
2 A1 1
3 A2 1
4 A11 2
5 A21 3

Result
1 A NULL A

visakh... it didnt work can you correct me what i am doing wrong?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

your join condition is wrong

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources