Need help on SQL statement
-
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?
-
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.navigateUrlFROM @tMenuCollection t1WHERE t1.parentID = 0UNION ALLSELECT t1.idx,t1.parentID,menuNme = CONVERT(VARCHAR(1000), cte.menuNme + ' - [' + t1.menuNme + ']'),t1.navigateUrlFROM @tMenuCollection t1JOIN cteON t1.parentID = cte.idxWHERE t1.parentID > 0)SELECT *FROM cteORDER 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 -
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 DISTINCTROW_NUMBER () OVER(order by main.idx, main.parentid) AS idx,main.parentid,'[' + CASE WHEN parent.menuNme IS NOT NULLTHEN parent.menuNme + ']' + ISNULL(' - ' + main.menuNme,'')ELSE main.menuNme + ']' END AS menuNme,navigateUrlFROM (SELECTidx, parentID, menuNme, navigateUrlFROM @tMenuCollection) main--Get parentOUTER APPLY (SELECTidx AS menuid, menuNmeFROM @tMenuCollectionWHERE idx = main.parentID) parent--Get childOUTER APPLY (SELECTidx AS menuid, menuNmeFROM @tMenuCollectionWHERE main.idx = parentID) child)SELECT * FROM CTEWhich 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/ -
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
-
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 -
Sir Waynes,
Your query working awesome. tq sir
-
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 DISTINCTmain.idx,main.parentid,'[' + CASE WHEN parent.menuNme IS NOT NULLTHEN parent.menuNme + ']' + ISNULL(' - ' + main.menuNme,'')ELSE main.menuNme + ']' END AS menuNme,navigateUrlFROM (SELECTidx, parentID, menuNme, navigateUrlFROM @tMenuCollection) main--Get parentOUTER APPLY (SELECTidx AS menuid, menuNmeFROM @tMenuCollectionWHERE idx = main.parentID) parent--Get childOUTER APPLY (SELECTidx AS menuid, menuNmeFROM @tMenuCollectionWHERE 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/ -
tq skcadavre for your effort
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply