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
 Grouping by part of results

Author  Topic 

infodemers
Posting Yak Master

183 Posts
Posted - 2011-04-26 : 20:25:43
I have an SQL function that returns something like the following.249-510,249-520,249-530,705-220,705-229,705-241,705-252,705-279

Here is my function
FUNCTION [dbo].[fn_GetNPANNX_Exchange] ( @ExchangeID INT)
returns varchar(1024)

AS BEGIN
DECLARE @ReturnValue VARCHAR(1024)

--Create a temporary table.
DECLARE @TEMP Table (
ROWID INT IDENTITY(1,1),
NPANNXID CHAR(7)

)

	-- Add the T-SQL statements to compute the return value here
Insert into @TEMP
SELECT distinct  NPANNXExchange.NPANNXID
FROM Exchange INNER JOIN
	NPANNXExchange ON Exchange.ExchangeID = NPANNXExchange.ExchangeID INNER JOIN
    NPANNX ON NPANNXExchange.NPANNXID = NPANNX.NPANNXID INNER JOIN
    NPANNXAssignment ON NPANNX.NPANNXID = NPANNXAssignment.NPANNXID INNER JOIN
    NPANNXAssignmentService ON NPANNXAssignment.NPANNXAssignmentID = NPANNXAssignmentService.NPANNXAssignmentID
WHERE NPANNXExchange.ExchangeID = @ExchangeID and 
	((NPANNXExchange.StartDate <= getdate() and NPANNXExchange.EndDate is null)
	or (NPANNXExchange.StartDate <= getdate() and NPANNXExchange.EndDate >= getdate())
	or (NPANNXExchange.StartDate >= getdate() and NPANNXExchange.EndDate is null))
	GROUP BY  NPANNXExchange.NPANNXID order by NPANNXID
                      

	--Let's concatenate the NPANNX that have the wll service within the same exchange.
SELECT  @ReturnValue =  COALESCE(@ReturnValue, '') + NPANNXID + ','
FROM  (Select Rcl.NPANNXID from @TEMP Rcl) as List 
RETURN Left(@ReturnValue,Len(@ReturnValue)-1)


I wish to return something like the following instead.
249:510,520,530,705:220,229,241,252,279

Please help !

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-04-26 : 21:01:20
See changes below. I am assuming you are on SQL 2005 or higher, otherwise, the code will not work.

quote:
Originally posted by infodemers

I have an SQL function that returns something like the following.249-510,249-520,249-530,705-220,705-229,705-241,705-252,705-279

Here is my function
FUNCTION [dbo].[fn_GetNPANNX_Exchange] ( @ExchangeID INT)
returns varchar(1024)

AS BEGIN
DECLARE @ReturnValue VARCHAR(1024)

--Create a temporary table.
DECLARE @TEMP Table (
ROWID INT IDENTITY(1,1),
NPANNXID CHAR(7)

)

	-- Add the T-SQL statements to compute the return value here
Insert into @TEMP
SELECT distinct  NPANNXExchange.NPANNXID
FROM Exchange INNER JOIN
	NPANNXExchange ON Exchange.ExchangeID = NPANNXExchange.ExchangeID INNER JOIN
    NPANNX ON NPANNXExchange.NPANNXID = NPANNX.NPANNXID INNER JOIN
    NPANNXAssignment ON NPANNX.NPANNXID = NPANNXAssignment.NPANNXID INNER JOIN
    NPANNXAssignmentService ON NPANNXAssignment.NPANNXAssignmentID = NPANNXAssignmentService.NPANNXAssignmentID
WHERE NPANNXExchange.ExchangeID = @ExchangeID and 
	((NPANNXExchange.StartDate <= getdate() and NPANNXExchange.EndDate is null)
	or (NPANNXExchange.StartDate <= getdate() and NPANNXExchange.EndDate >= getdate())
	or (NPANNXExchange.StartDate >= getdate() and NPANNXExchange.EndDate is null))
	GROUP BY  NPANNXExchange.NPANNXID order by NPANNXID
                      

	--Let's concatenate the NPANNX that have the wll service within the same exchange.
SELECT  @ReturnValue =  COALESCE(@ReturnValue, '') + NPANNXID + ','
FROM  (Select Rcl.NPANNXID from @TEMP Rcl) as List 

;with cte1 as
(
   select 
      NPANNXID, 
      row_number() over (partition by left(NPANNXID,3) order by NPANNXID) rn,
      row_number() over (order by NPANNXID) ordering_id 
   from 
      @TEMP 
)
select 
   @ReturnValue = replace(
   (
      select
         case when rn = 1 then NPANNXID else right(NPANNXID,3) end as [text()],
         ',' as [text()]
      from
         cte1
      order by
         ordering_id
      for xml path('') 
   ),'-',':');

RETURN Left(@ReturnValue,Len(@ReturnValue)-1)


I wish to return something like the following instead.
249:510,520,530,705:220,229,241,252,279

Please help !
Go to Top of Page

infodemers
Posting Yak Master

183 Posts
Posted - 2011-04-26 : 21:13:47
Give the man a cigar !
I thank you so much Sunitabeck. That saves me time !

Would you also help me with the follwing function please?

It returns something like the following.
705-849-1000-1999,705-849-3000-3999,705-849-4000-4999,705-849-5000-5999,705-849-7000-7999,705-849-8000-8999,705-849-9000-9999

and I wish to get the following instead
[705-849]1000-1999,3000-3999,4000-4999,5000-5999,7000-7999,8000-8999,9000-9999

FUNCTION [dbo].[fn_GetNPANNX_MSID] ( @ExchangeID INT)
returns varchar(1024)

AS BEGIN
DECLARE @ReturnValue VARCHAR(1024)

--Create a temporary table.
DECLARE @TEMP Table (
ROWID INT IDENTITY(1,1),
ExchangeID INT,
[NPANXXID] CHAR(7),
[RangeNumber] CHAR(9)
)

	-- Add the T-SQL statements to compute the return value here
Insert into @TEMP

SELECT     NPANNXExchange.ExchangeID, MSIDLineRange.NPANXXID, MSIDLineRange.RangeNumber
FROM         NPANNXAssignmentService INNER JOIN
                      NPANNXAssignment ON NPANNXAssignmentService.NPANNXAssignmentID = NPANNXAssignment.NPANNXAssignmentID INNER JOIN
                      NPANNX ON NPANNXAssignment.NPANNXID = NPANNX.NPANNXID INNER JOIN
                      MSIDLineRange INNER JOIN
                      NPANNXExchange ON MSIDLineRange.NPANXXID = NPANNXExchange.NPANNXID ON NPANNX.NPANNXID = NPANNXExchange.NPANNXID
WHERE ((NPANNXAssignment.StartDate <= getdate() and NPANNXAssignment.EndDate is null)
		or (NPANNXAssignment.StartDate <= getdate() and NPANNXAssignment.EndDate >= getdate())
		or (NPANNXAssignment.StartDate >= getdate() and NPANNXAssignment.EndDate is null))
		and NPANNXAssignmentService.ServiceID = '132'
GROUP BY  NPANNXExchange.ExchangeID, MSIDLineRange.NPANXXID, MSIDLineRange.RangeNumber

		--Let's concatenate the NPANNX that have the wll service within the same exchange.
SELECT  @ReturnValue =  COALESCE(@ReturnValue, '') + [NPANXXID]+ '-' + [RangeNumber] + ','
FROM  (Select Rcl.ExchangeID ,Rcl.[NPANXXID] , Rcl.[RangeNumber] from @TEMP Rcl
Where Rcl.ExchangeID = @ExchangeID ) as List 
RETURN Left(@ReturnValue,Len(@ReturnValue)-1)

END
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-04-26 : 21:21:05
Not a smoker and not a man, but thank you :)

I made a change to the code to add an ordering_id. Although I think it will work just fine even without that, having it is safer.
Go to Top of Page

infodemers
Posting Yak Master

183 Posts
Posted - 2011-04-26 : 21:24:26
quote:
Originally posted by sunitabeck

Not a smoker and not a man, but thank you :)

I made a change to the code to add an ordering_id. Although I think it will work just fine even without that, having it is safer.


Would you also help me with the follwing function please?

It returns something like the following.
705-849-1000-1999,705-849-3000-3999,705-849-4000-4999,705-849-5000-5999,705-849-7000-7999,705-849-8000-8999,705-849-9000-9999

and I wish to get the following instead
[705-849]1000-1999,3000-3999,4000-4999,5000-5999,7000-7999,8000-8999,9000-9999

FUNCTION [dbo].[fn_GetNPANNX_MSID] ( @ExchangeID INT)
returns varchar(1024)

AS BEGIN
DECLARE @ReturnValue VARCHAR(1024)

--Create a temporary table.
DECLARE @TEMP Table (
ROWID INT IDENTITY(1,1),
ExchangeID INT,
[NPANXXID] CHAR(7),
[RangeNumber] CHAR(9)
)

	-- Add the T-SQL statements to compute the return value here
Insert into @TEMP

SELECT     NPANNXExchange.ExchangeID, MSIDLineRange.NPANXXID, MSIDLineRange.RangeNumber
FROM         NPANNXAssignmentService INNER JOIN
                      NPANNXAssignment ON NPANNXAssignmentService.NPANNXAssignmentID = NPANNXAssignment.NPANNXAssignmentID INNER JOIN
                      NPANNX ON NPANNXAssignment.NPANNXID = NPANNX.NPANNXID INNER JOIN
                      MSIDLineRange INNER JOIN
                      NPANNXExchange ON MSIDLineRange.NPANXXID = NPANNXExchange.NPANNXID ON NPANNX.NPANNXID = NPANNXExchange.NPANNXID
WHERE ((NPANNXAssignment.StartDate <= getdate() and NPANNXAssignment.EndDate is null)
		or (NPANNXAssignment.StartDate <= getdate() and NPANNXAssignment.EndDate >= getdate())
		or (NPANNXAssignment.StartDate >= getdate() and NPANNXAssignment.EndDate is null))
		and NPANNXAssignmentService.ServiceID = '132'
GROUP BY  NPANNXExchange.ExchangeID, MSIDLineRange.NPANXXID, MSIDLineRange.RangeNumber

		--Let's concatenate the NPANNX that have the wll service within the same exchange.
SELECT  @ReturnValue =  COALESCE(@ReturnValue, '') + [NPANXXID]+ '-' + [RangeNumber] + ','
FROM  (Select Rcl.ExchangeID ,Rcl.[NPANXXID] , Rcl.[RangeNumber] from @TEMP Rcl
Where Rcl.ExchangeID = @ExchangeID ) as List 
RETURN Left(@ReturnValue,Len(@ReturnValue)-1)

END
Go to Top of Page

infodemers
Posting Yak Master

183 Posts
Posted - 2011-04-26 : 22:10:04
From the original suggestion made by sunitabeck, I managed a solution.

Thanks!
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts
Posted - 2011-04-27 : 07:02:41
Glad you were able to adapt it - I had closed shop and gone away and so didn't see your second question.
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site