Need help on SQL statement - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Need help on SQL statement

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2011-05-23 : 03:26:01

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','javascript:void(0)');
insert into @tMenuCollection values(2,0,'Amil','javascript:void(0)');
insert into @tMenuCollection values(3,0,'Kira Zakat & Simpan','javascript:void(0)');
insert into @tMenuCollection values(4,3,'Tambah','javascript:void(0)');
insert into @tMenuCollection values(5,3,'Papar','javascript:void(0)');
insert into @tMenuCollection values(6,3,'Carian','javascript:void(0)');
insert into @tMenuCollection values(7,2,'Urus Cek','javascript:void(0)');
insert into @tMenuCollection values(7,7,'Debit','javascript:void(0)');
insert into @tMenuCollection values(8,0,'Surat Majikan','javascript:void(0)');

I want to generate output as following,


idx   | parentID    | menuNme                          | navigateUrl
----------------------------------------------------------------------------
1    | 0    | [Bayar Zakat]                          | javascript:void(0)
2    | 0    | [Amil]                          | javascript:void(0)
3    | 0    | [Kira Zakat & Simpan]                          | javascript:void(0)
4    | 3    | [Kira Zakat & Simpan] - Tambah                         | javascript:void(0)
5    | 3    | [Kira Zakat & Simpan] - Papar                         | javascript:void(0)
6    | 3    | [Kira Zakat & Simpan] - Carian                         | javascript:void(0)
7    | 2    | [Amil] - Urus Cek                         | javascript:void(0)
8    | 7    | [Amil] - Urus Cek - Debit                         | javascript:void(0)
9    | 0    | [Surat Majikan]                         | javascript:void(0)

*above output is main menu, menu and sub menu

How my SQL statement looks like?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-05-23 : 03:29:31

Read about recursive common table expression (recursive cte).
Or search SQLTEAM, they have many examples.

N 56°04'39.26"
E 12°55'05.63"

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2011-05-24 : 01:53:41

Correction. My table and data as following,


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

insert into @tMenuCollection values(1,0,'Bayar Zakat','javascript:void(0)');
insert into @tMenuCollection values(2,0,'Amil','javascript:void(0)');
insert into @tMenuCollection values(3,0,'Kira Zakat & Simpan','javascript:void(0)');
insert into @tMenuCollection values(4,3,'Tambah','javascript:void(0)');
insert into @tMenuCollection values(5,3,'Papar','javascript:void(0)');
insert into @tMenuCollection values(6,3,'Carian','javascript:void(0)');
insert into @tMenuCollection values(7,2,'Urus Cek','javascript:void(0)');
insert into @tMenuCollection values(8,7,'Debit','javascript:void(0)');
insert into @tMenuCollection values(9,0,'Surat Majikan','javascript:void(0)');

After got technical advice from Peso. This is my query,


;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;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources