Connect by in SQL Server - 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 2005 Forums

Transact-SQL (2005)

Connect by in SQL Server

Author

Topic

bhushanhegde
Starting Member

14 Posts

Posted - 2008-11-28 : 01:10:55

Hi,
I need one more help in converting my oracle query into SQL Server 2005.
Query is as follows:

SELECT g.groupid GroupId,lpad(' ',5*(level-1),'-') || groupname groupname, g.parent_groupid,g.root_groupid FROM fm_profilegroup g
WHERE Active_Flag='Y' START WITH parent_groupid=0 CONNECT BY PRIOR groupid=parent_groupid ;
ORDER BY root_groupid,groupid;

can anyone solve this?

Regards
bhushan

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-28 : 09:24:00

The link below creates Rpad and Lpad functions in SQL Server.

http://dbasqlserver.blogspot.com/2006/09/sql-server-t-sql-lpad-rpad-functions.html

Here are the functions:

IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[fnPadRight]')
AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[fnPadRight]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION fnPadRight
(
@PadChar char(1),
@PadToLen int,
@BaseString varchar(100)
)
RETURNS varchar(1000)
AS
/* ****************************************************
Description:
Pads @BaseString to an exact length (@PadToLen) using the
specified character (@PadChar). Base string will not be
trimmed. Implicit type conversion should allow caller to
pass a numeric T-SQL value for @BaseString.
Unfortunately T-SQL string variables must be declared with an
explicit width, so I chose 100 for the base and 1000 for the
return. Feel free to adjust data types to suit your needs.
Keep in mind that if you don't assign an explicit width to
varchar it is the same as declaring varchar(1).
Revision History:
Date Name Description
---- ---- -----------
**************************************************** */
BEGIN
DECLARE @Padded varchar(1000)
DECLARE @BaseLen int
SET @BaseLen = LEN(@BaseString)
IF @BaseLen >= @PadToLen
BEGIN
SET @Padded = @BaseString
END
ELSE
BEGIN
SET @Padded = @BaseString + REPLICATE(@PadChar, @PadToLen - @BaseLen)
END
RETURN @Padded
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

IF EXISTS (
SELECT *
FROM dbo.sysobjects
WHERE id = object_id(N'[dbo].[fnPadRight]')
AND xtype IN (N'FN', N'IF', N'TF')
)
DROP FUNCTION [dbo].[fnPadRight]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION fnPadRight
(
@PadChar char(1),
@PadToLen int,
@BaseString varchar(100)
)
RETURNS varchar(1000)
AS
/* ****************************************************
Description:
Pads @BaseString to an exact length (@PadToLen) using the
specified character (@PadChar). Base string will not be
trimmed. Implicit type conversion should allow caller to
pass a numeric T-SQL value for @BaseString.
Unfortunately T-SQL string variables must be declared with an
explicit width, so I chose 100 for the base and 1000 for the
return. Feel free to adjust data types to suit your needs.
Keep in mind that if you don't assign an explicit width to
varchar it is the same as declaring varchar(1).
Revision History:
Date Name Description
---- ---- -----------
**************************************************** */
BEGIN
DECLARE @Padded varchar(1000)
DECLARE @BaseLen int
SET @BaseLen = LEN(@BaseString)
IF @BaseLen >= @PadToLen
BEGIN
SET @Padded = @BaseString
END
ELSE
BEGIN
SET @Padded = @BaseString + REPLICATE(@PadChar, @PadToLen - @BaseLen)
END
RETURN @Padded
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources