November 2, 2012 at 1:27 am
Hi All,
This is my table & its values.
Create Table Sample1
(
AutoID int Primary Key Identity(1,1),
SampleIDs nVarchar(Max)
)
Insert Into Sample1 Values('1,2,3')
Select * From Sample1
AutoIDSampleIDs
11,2,3
Update Sample1 Set SampleIDs='1,3,4' where AutoID=1
Suppose if I update SampleIDs to '1,3,4' Then the result comes like this,
AutoIDSampleIDs
11,3,4
But I don’t want like above one, I want like below one
AutoIDSampleIDs
1 1,2,3,4
Is it possible to get the above output????
if I want to update the value with '1,3,4' I want to get the output like
'1,2,3,4'.
if we see the previous value('1,2,3') & wants to update value ('1,3,4')
the new value is 4.Simply says that like UNION.
Example like,
(1,2,3) UNION (1,3,4) = (1,2,3,4)
Please Give The Solution.
Regards,
Venkatesh Desai.
November 2, 2012 at 1:45 am
i am doing query part
but for the quick response below are the steps
1)use split function fn_Split(search google you will get this one)
2)apply this split function on both 1. Existing Column value 2. for new value to update
Like : Select value from fn_split(@newvalue,',') and
Select value from fn_split(select sampleid from sampletable,',')
3)Union both table which gives you distinct value
Select value from fn_split(@newvalue,',') and
UNION
Select value from fn_split(select sampleid from sampletable,',')
4)Union will return distinct and multiple rows,make it in single row comma separated value store in any variable (you can do this with help of coalesce)
5)update column with variable which you have set from above operation.
try this step mean while query post.
November 2, 2012 at 3:47 am
Thanks for replying BriPan..
Declare @SampleIDs Varchar(100)
Set @SampleIDs=(Select SampleIDs From Sample1 where AutoID=1)
Select Item from dbo.DelimitedSplit('1,3,4',',') UNION Select Item from dbo.DelimitedSplit(@SampleIDs,',')
So the output for this is like,
Item
-----
1
2
3
4
then how to convert it like '1,2,3,4' ??????
Please give the solution...
November 2, 2012 at 3:59 am
great now just you want to convert multiple row into single row using coalesce
you can do it
something like this
declare @sql varchar(max)
set @sql=COALESCE(@test + ',', '') + item
FROM
(
Select Item from dbo.DelimitedSplit('1,3,4',',') UNION Select Item from dbo.DelimitedSplit(@SampleIDs,',')
)a
then update table with @sql
November 2, 2012 at 4:02 am
You can get it as follows:
Declare @SampleIDs Varchar(100)
Set @SampleIDs=(Select SampleIDs From Sample1 where AutoID=1)
;With CTE
As
(
Select Item from dbo.dbo.DelimitedSplit('1,3,4',',')
UNION
Select Item from dbo.dbo.DelimitedSplit(@SampleIDs,',')
)
Select STUFF((Select ',' + String From CTE FOR XML PATH('')),1,1,'') As New_String
November 2, 2012 at 4:27 am
Thanks Pan & Vinu Its working... & once again thanks for replying...
November 2, 2012 at 6:49 pm
BriPan (11/2/2012)
i am doing query partbut for the quick response below are the steps
1)use split function fn_Split(search google you will get this one)
2)apply this split function on both 1. Existing Column value 2. for new value to update
Like : Select value from fn_split(@newvalue,',') and
Select value from fn_split(select sampleid from sampletable,',')
3)Union both table which gives you distinct value
Select value from fn_split(@newvalue,',') and
UNION
Select value from fn_split(select sampleid from sampletable,',')
4)Union will return distinct and multiple rows,make it in single row comma separated value store in any variable (you can do this with help of coalesce)
5)update column with variable which you have set from above operation.
try this step mean while query post.
Be REAL careful about using fn_split... it has a WHILE loop in it and is actually one of the worst methods performance wise for doing a split in the VARCHAR(8000) realm that there is.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 2, 2012 at 10:09 pm
Jeff Moden (11/2/2012)
BriPan (11/2/2012)
i am doing query partbut for the quick response below are the steps
1)use split function fn_Split(search google you will get this one)
2)apply this split function on both 1. Existing Column value 2. for new value to update
Like : Select value from fn_split(@newvalue,',') and
Select value from fn_split(select sampleid from sampletable,',')
3)Union both table which gives you distinct value
Select value from fn_split(@newvalue,',') and
UNION
Select value from fn_split(select sampleid from sampletable,',')
4)Union will return distinct and multiple rows,make it in single row comma separated value store in any variable (you can do this with help of coalesce)
5)update column with variable which you have set from above operation.
try this step mean while query post.
Be REAL careful about using fn_split... it has a WHILE loop in it and is actually one of the worst methods performance wise for doing a split in the VARCHAR(8000) realm that there is.
I completely agree with Mr. Moden.
But, there is still hope......:-)
You can use Jeff Moden's splitter instead....it replaces the While Loops with a Tally Table[/url]. Here is a very good link where you can understand the use of the Splitter and see comparisons with other Splitters. Hope it helps you...
November 3, 2012 at 7:09 am
Thanks, Vinu. I must be getting old 'cause I always forget to link to my own articles. I appreciate the cover.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 4, 2012 at 9:13 pm
The pleasure is all mine Mr. Moden......I have learnt a lot from the articles here at SSC.....and its a pleasure that i could recommend a few of those to other learners like me 🙂
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply