Need help on SQL statement

Question

Post reply

Need help on SQL statement

miss.delinda

SSCommitted

Points: 1616
More actions
May 23, 2011 at 7:37 am

#389571

My sql as following,
declare @tMenuCollection table
(idx int,parentID int,menuNme varchar(50),navigateUrl varchar(50));
insert into @tMenuCollection values(1,0,'Bayar Zakat','void(0)');
insert into @tMenuCollection values(2,0,'Amil','void(0)');
insert into @tMenuCollection values(3,0,'Kira Zakat & Simpan','void(0)');
insert into @tMenuCollection values(4,3,'Tambah','void(0)');
insert into @tMenuCollection values(5,3,'Papar','void(0)');
insert into @tMenuCollection values(6,3,'Carian','void(0)');
insert into @tMenuCollection values(7,2,'Urus Cek','void(0)');
insert into @tMenuCollection values(8,7,'Debit','void(0)');
insert into @tMenuCollection values(9,0,'Surat Majikan','void(0)');
I want to generate output as following,
idx | parentID | menuNme | navigateUrl
----------------------------------------------------------------------------
1 | 0 | [Bayar Zakat] | void(0)
2 | 0 | [Amil] | void(0)
3 | 0 | [Kira Zakat & Simpan] | void(0)
4 | 3 | [Kira Zakat & Simpan] - Tambah | void(0)
5 | 3 | [Kira Zakat & Simpan] - Papar | void(0)
6 | 3 | [Kira Zakat & Simpan] - Carian | void(0)
7 | 2 | [Amil] - Urus Cek | void(0)
8 | 7 | [Amil] - Urus Cek - Debit | void(0)
9 | 0 | [Surat Majikan] | void(0)
*above output is main menu, menu and sub menu
How my SQL statement looks like?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

How does this work out? Check out this BOL link for how it this recursive common-table-expression works.

;

WITH cte AS

(

SELECT t1.idx,

t1.parentID,

menuNme = CONVERT(VARCHAR(1000), '[' + t1.menuNme + ']'),

t1.navigateUrl

FROM @tMenuCollection t1

WHERE t1.parentID = 0

UNION ALL

SELECT t1.idx,

t1.parentID,

menuNme = CONVERT(VARCHAR(1000), cte.menuNme + ' - [' + t1.menuNme + ']'),

t1.navigateUrl

FROM @tMenuCollection t1

JOIN cte

ON t1.parentID = cte.idx

WHERE t1.parentID > 0

)

SELECT *

FROM cte

ORDER BY idx;

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

WayneS (5/23/2011)
How does this work out? Check out this BOL link for how it this recursive common-table-expression works.

I can't get your solution to work Wayne (which is irritating because mine doesn't get the correct result! :-P)

So far, I got to this point: -

;WITH CTE AS (

SELECT DISTINCT

ROW_NUMBER () OVER(order by main.idx, main.parentid) AS idx,

main.parentid,

'[' + CASE WHEN parent.menuNme IS NOT NULL

THEN parent.menuNme + ']' + ISNULL(' - ' + main.menuNme,'')

ELSE main.menuNme + ']' END AS menuNme,

navigateUrl

FROM (SELECT

idx, parentID, menuNme, navigateUrl

FROM @tMenuCollection) main

--Get parent

OUTER APPLY (SELECT

idx AS menuid, menuNme

FROM @tMenuCollection

WHERE idx = main.parentID) parent

--Get child

OUTER APPLY (SELECT

idx AS menuid, menuNme

FROM @tMenuCollection

WHERE main.idx = parentID) child

)

SELECT * FROM CTE

Which fails to get idx 7 correctly. When I run yours, I get a max recursion error.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

miss.delinda SSCommitted Points: 1616 More actions · Answer 3

My mistake. My table as following,

declare @tMenuCollection table(idx int,parentID int,menuNme varchar(50),navigateUrl varchar(50));

insert into @tMenuCollection values(1,0,'Bayar Zakat','void(0)');

insert into @tMenuCollection values(2,0,'Amil','void(0)');

insert into @tMenuCollection values(3,0,'Kira Zakat & Simpan','void(0)');

insert into @tMenuCollection values(4,3,'Tambah','void(0)');

insert into @tMenuCollection values(5,3,'Papar','void(0)');

insert into @tMenuCollection values(6,3,'Carian','void(0)');

insert into @tMenuCollection values(7,2,'Urus Cek','void(0)');

insert into @tMenuCollection values(8,7,'Debit','void(0)');

insert into @tMenuCollection values(9,0,'Surat Majikan','void(0)');

Thanks to both of you

WayneS SSC Guru Points: 95460 More actions · Answer 4

miss.delinda (5/23/2011)
My mistake. My table as following,
declare @tMenuCollection table(idx int,parentID int,menuNme varchar(50),navigateUrl varchar(50));
insert into @tMenuCollection values(1,0,'Bayar Zakat','void(0)');
insert into @tMenuCollection values(2,0,'Amil','void(0)');
insert into @tMenuCollection values(3,0,'Kira Zakat & Simpan','void(0)');
insert into @tMenuCollection values(4,3,'Tambah','void(0)');
insert into @tMenuCollection values(5,3,'Papar','void(0)');
insert into @tMenuCollection values(6,3,'Carian','void(0)');
insert into @tMenuCollection values(7,2,'Urus Cek','void(0)');
insert into @tMenuCollection values(8,7,'Debit','void(0)');
insert into @tMenuCollection values(9,0,'Surat Majikan','void(0)');
Thanks to both of you

:blush: Oh yeah... I forgot to mention this...

miss.delinda, is my query working properly for you?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

miss.delinda SSCommitted Points: 1616 More actions · Answer 5

miss.delinda

SSCommitted

Points: 1616

May 23, 2011 at 11:52 pm

#1328841

Sir Waynes,

Your query working awesome. tq sir

Cadavre SSC-Forever Points: 41690 More actions · Answer 6

miss.delinda (5/23/2011)
My mistake. My table as following,
declare @tMenuCollection table(idx int,parentID int,menuNme varchar(50),navigateUrl varchar(50));
insert into @tMenuCollection values(1,0,'Bayar Zakat','void(0)');
insert into @tMenuCollection values(2,0,'Amil','void(0)');
insert into @tMenuCollection values(3,0,'Kira Zakat & Simpan','void(0)');
insert into @tMenuCollection values(4,3,'Tambah','void(0)');
insert into @tMenuCollection values(5,3,'Papar','void(0)');
insert into @tMenuCollection values(6,3,'Carian','void(0)');
insert into @tMenuCollection values(7,2,'Urus Cek','void(0)');
insert into @tMenuCollection values(8,7,'Debit','void(0)');
insert into @tMenuCollection values(9,0,'Surat Majikan','void(0)');
Thanks to both of you

D'Oh! 🙂

I had struggled to understand your ordering before that correction. Glad Wayne's query worked. This is how I'd have looked at it.

SELECT DISTINCT

main.idx,

main.parentid,

'[' + CASE WHEN parent.menuNme IS NOT NULL

THEN parent.menuNme + ']' + ISNULL(' - ' + main.menuNme,'')

ELSE main.menuNme + ']' END AS menuNme,

navigateUrl

FROM (SELECT

idx, parentID, menuNme, navigateUrl

FROM @tMenuCollection) main

--Get parent

OUTER APPLY (SELECT

idx AS menuid, menuNme

FROM @tMenuCollection

WHERE idx = main.parentID) parent

--Get child

OUTER APPLY (SELECT

idx AS menuid, menuNme

FROM @tMenuCollection

WHERE main.idx = parentID) child

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

miss.delinda SSCommitted Points: 1616 More actions · Answer 7

miss.delinda

SSCommitted

Points: 1616

May 24, 2011 at 10:05 am

#1329207

tq skcadavre for your effort