seperate in sql - 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

General SQL Server Forums

New to SQL Server Programming

seperate in sql

Author

Topic

wizard_oz
Starting Member

4 Posts

Posted - 2011-04-06 : 10:09:36

Hey
I have very simple function


ALTER function [dbo].[fnc_RPT_0451_GetCarts]
(@id as int)
returns varchar(100)
As
Begin

declare @string varchar(100)
select @string = ''
select @string = @string + EAE_EquipNo + '  '
from AL_EqAllEquip
where EAE_ID = @id

return @string
End

the result is without the id (the is is to make you understand where is it coming from
equip id
1000 394
1001 394
2000 398
2001 298
3500 302
3502 302

when I'm calling to this function it gives me in one line all the equip number for example 1000 1001 2000 2001 3500 3502
I would like to group the equip by id but the put comma "," sign after every id changes
for example 1000 1001 , 2000 2001 , 3500 3502

anyone can help me with that?

thanks, Elen

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-06 : 10:16:37

your function seems to be limiting itself to a particular EAE_ID... is this a different Id than the one you want to group by?

are you in MS SQL 2005 or MS SQL 2008??

Corey

I Has Returned!!

wizard_oz
Starting Member

4 Posts

Posted - 2011-04-07 : 09:25:08

the EAE_ID suppose filter by @id

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-04-07 : 10:25:51

Start with this and see if this works for all set of data



declare @t table(equip int, id int)
insert into @t
select 1000, 394 union all
select 1001, 394 union all
select 2000, 398 union all
select 2001, 298 union all
select 3500, 302 union all
select 3502, 302 

declare @string varchar(100), @equip_change int
select @string = ''
select 
	@string = @string + cast(equip as varchar(100))+ 
	case when @equip_change=left(equip,1) then ' , ' else ' ' end, @equip_change=LEFT(equip,1)
from @t

select @string

Madhivanan

Failing to plan is Planning to fail

wizard_oz
Starting Member

4 Posts

Posted - 2011-04-10 : 03:06:02

the example that I gave you was just an example, it can used in any other numbers. what I meant is is I have 2 or 3 quipment to the same ID , how can I seperate them by comma for example 9944,9945

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-10 : 05:49:43

If you are on SQL 2005 or higher, you can do this as shown below. I tested the code using Madhivanan's test data, and then inserted the code into your function, but didn't test the function. If it does not work as you expect, it is probably something silly I did while inserting the code into your function.

ALTER function [fnc_RPT_0451_GetCarts]
   (@id as int)
returns varchar(100)
As
Begin

   declare @string varchar(100);
   with cte as
   (
      select *, row_number() over (partition by id order by equip asc) as rownum
      from AL_EqAllEquip
      where EAE_ID = @id
   )
   select @string = stuff ((
      select
         case when rownum = 1 then ' , ' else ' ' end as [text()],
         equip as [text()]
      from
         cte
      order by
         id, equip
      for xml path(''), type
      ).value('.','varchar(100)'),1,3,'');
   return @string
End

wizard_oz
Starting Member

4 Posts

Posted - 2011-04-10 : 08:15:50

thanks maybe I didn't explain my self so let me try again. the @id is the id that I send to the function and it does'nt relate to this table what I mean is i send to the function id=403 then there is a table EqAllEquip which has
id (the id that i sent from the main proceudre)
EAE_EquipNo - the quipment number like 8845,8846,9876,9877
the cart number like 343,345,346
id EquipNo cartid
403 8811 343
403 9912 343
403 9877 345
403 9878 345
403 6788 346
403 6789 346
403 6790 346
what i need is on every cartid that changed it should be seperated by comma for example
8811 9912,9877 9889

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-10 : 10:34:47

quote:
Originally posted by wizard_oz

thanks maybe I didn't explain my self so let me try again. the @id is the id that I send to the function and it does'nt relate to this table what I mean is i send to the function id=403 then there is a table EqAllEquip which has
id (the id that i sent from the main proceudre)
EAE_EquipNo - the quipment number like 8845,8846,9876,9877
the cart number like 343,345,346
id EquipNo cartid
403 8811 343
403 9912 343
403 9877 345
403 9878 345
403 6788 346
403 6789 346
403 6790 346
what i need is on every cartid that changed it should be seperated by comma for example
8811 9912,9877 9889

I am struggling to relate your sample data to your desired output, and I am failing miserably. Your sample input data, which I assume is in the AL_EqAllEquip table:


    403 8811 343
    403 9912 343
    403 9877 345
    403 9878 345
    403 6788 346
    403 6789 346
    403 6790 346

And the corresponding output that you are looking for:


8811 9912,9877 9889

I have questions about two aspects of the input data and the corresponding output:

1. The EquipNo I have highlighted in red - 9889 - is not in the input data, but it is in the output.

2. The EquipNo from the last 4 rows in the input - 9878, 6788, 6789 and 6790 - are not in the output.

What are the rules you are using for eliminating/aggregating the four numbers missing in the output and replacing it with one number - 9889?

Based on your input data, I would have thought that the output you are looking for is this:


    8811 9912 , 9877 9878 , 6788 6789 6790

If that is what you are looking for, the function I posted earlier would return that if invoked it like this:


select dbo.fnc_RPT_0451_GetCarts(403);

If that is not what you are looking for, can you provide the rules for generating the numbers I highlighted in red?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources