May 29, 2008 at 4:57 pm
need help do this
i have table table_index like this
fld_index
-----------------
103
103
103
104
104
104
105
105
105
........
need to convert it like this
'103','104','105','106'
declare @STR varchar(100)
set @STR =(SELECT fld_index FROM table_index GROUP BY fld_index)
select @STR = substring( @STR,',' + 1, len( @STR))
select @STR
i get an error
Msg 512, Level 16, State 1, Line 3
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, = or when the subquery is used as an expression.
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value ',' to data type int.
May 29, 2008 at 7:22 pm
Take a look at Performance Tuning: Concatenation Functions and Some Tuning Myths
By Jeff Moden, 2008/01/01
http://www.sqlservercentral.com/articles/Test+Data/61572/
The first error message is self-explanatory. (SELECT fld_index FROM table_index GROUP BY fld_index) returns 3 rows and set @xyz can only accept 1.
The second error message comes from ',' + 1 in the substring(). You need a number there.
This works ok for a tiny set
create table #table_index (fld_index int)
insert into #table_index
select 103 union all
select 103 union all
select 103 union all
select 104 union all
select 104 union all
select 104 union all
select 105 union all
select 105 union all
select 105
select fld_index
into #table_index2
from #table_index
group by fld_index
declare @STR varchar(100)
set @STR = ''
update t
set @STR = @STR + ',' + char(39) + convert(varchar(10), fld_index) + char(39)
from #table_index2 t
select @STR = substring( @STR, charindex(',', @STR) + 1, len(@str))
select @STR
May 29, 2008 at 10:59 pm
Hi,
The Following May be useful
declare @Table table (c int)
insert into @Table
select 103 union
select 104 union
select 105
insert into @Table
select 103 union
select 104 union
select 105
insert into @Table
select 103 union
select 104 union
select 105
declare @Table1 table (c int)
insert into @Table1
select Distinct * from @Table
select * from @Table
select * from @Table1
declare @Testemail varchar(100)
select @testEmail = coalesce(@testEmail + ';','') + CAST(c AS varchar(20))
from @Table1
select @testEmail
Rajesh
May 29, 2008 at 11:29 pm
Hi.. What about this
declare @STR varchar(100)
select @STR = @STR +','+ + fld_index FROM table_index where table_index in (select distinct fld_index FROM table_index )
you will hv to remove the extra "," that is there at the beginig of the string.
Check performance impact if you are working with large number of rows.
"Keep Trying"
May 30, 2008 at 3:43 pm
Chirag (5/29/2008)
Hi.. What about thisdeclare @STR varchar(100)
select @STR = @STR +','+ + fld_index FROM table_index where table_index in (select distinct fld_index FROM table_index )
you will hv to remove the extra "," that is there at the beginig of the string.
Check performance impact if you are working with large number of rows.
ok tnx
i have problem
i need to do this but use the your code
update [dbo].[tb_pivot_big]
set fld1 = CASE WHEN fld1 in('101' ,'102','103','104') then '*' else fld1 end
, fld2 = CASE WHEN fld2 in('101' ,'102','103','104') then '*' else fld2 end
, fld3 = CASE WHEN fld3 in('101' ,'102','103','104') then '*' else fld3 end
, fld4 = CASE WHEN fld4 in('101' ,'102','103','104') then '*' else fld4 end
, fld5 = CASE WHEN fld5 in('101' ,'102','103','104') then '*' else fld5 end
from [dbo].[tb_pivot_big]
where val_orginal = 1
and whan i do this i get an error
declare @STR varchar(100)
select @STR = @STR +','+ + fld_index FROM [nili].[dbo].[table_index] where table_index in (select distinct fld_index FROM [nili].[dbo].[table_index] )
update [dbo].[tb_pivot_big]
set fld1 = CASE WHEN fld1 in(@str) then '*' else fld1 end
, fld2 = CASE WHEN fld2 in(@str) then '*' else fld2 end
, fld3 = CASE WHEN fld3 in(@str) then '*' else fld3 end
, fld4 = CASE WHEN fld4 in(@str) then '*' else fld4 end
, fld5 = CASE WHEN fld5 in(@str) then '*' else fld5 end
from [dbo].[tb_pivot_big]
where val_orginal = 1
and i get an error
Msg 207, Level 16, State 1, Line 4
Invalid column name 'table_index'.
and this olso not working in update
declare @Table table (c int)
insert into @Table
select 103 union
select 104 union
select 105
insert into @Table
select 103 union
select 104 union
select 105
insert into @Table
select 103 union
select 104 union
select 105
declare @Table1 table (c int)
insert into @Table1
select Distinct * from @Table
declare @Testemail varchar(100)
select @testEmail = coalesce(@testEmail + ',','') + CAST(c AS varchar(20))
from @Table1
--select @testEmail
update [dbo].[tb_pivot_big]
set fld1 = CASE WHEN fld1 in(@testEmail) then '*' else fld1 end
, fld2 = CASE WHEN fld2 in(@testEmail) then '*' else fld2 end
, fld3 = CASE WHEN fld3 in(@testEmail) then '*' else fld3 end
, fld4 = CASE WHEN fld4 in(@testEmail) then '*' else fld4 end
, fld5 = CASE WHEN fld5 in(@testEmail) then '*' else fld5 end
from [dbo].[tb_pivot_big]
where val_orginal = 1
can you see waht is the problem TNX
May 30, 2008 at 4:11 pm
select @STR = @STR +','+ + fld_index FROM [nili].[dbo].[table_index] where table_index in (select distinct fld_index FROM [nili].[dbo].[table_index] )
should be
select @STR = @STR +','+ + fld_index FROM [nili].[dbo].[table_index] where fld_index in (select distinct fld_index FROM [nili].[dbo].[table_index])
May 30, 2008 at 4:46 pm
tnx you
and for all
for the help and the support
it working
May 31, 2008 at 1:31 pm
Just to provide an additional example:
SET NOCOUNT ON
DECLARE @table_index TABLE (fld_index tinyint NOT NULL)
INSERT @table_index VALUES(103)
INSERT @table_index VALUES(103)
INSERT @table_index VALUES(103)
INSERT @table_index VALUES(104)
INSERT @table_index VALUES(104)
INSERT @table_index VALUES(104)
INSERT @table_index VALUES(105)
INSERT @table_index VALUES(105)
INSERT @table_index VALUES(105)
SELECT SUBSTRING((
SELECT ',''' + CAST(fld_index AS char(3)) + '''' AS "data()"
FROM@table_index
GROUP BY fld_index
FOR XML PATH ('')
)
,2,8000)
Best Regards,
Chris Büttner
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply