comma sepetaed values - 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)

comma sepetaed values

Author

Topic

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-29 : 09:13:33

hi iam having tables
emrappointmentdetails and emrappointmentdetailshistory tables

there bill_number one column is there.

now iam having one more table emrtpbilleditems where appointment_ids one column is there which is having values like 806658,806659,806660

now i have to update this column into above two tables with comma seperated .how can i do please give me the migrate query for this

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-29 : 09:17:33

1. how is the 3 tables related ?
2. Which table / column do you want to update the appointment_ids to ?

KH
[spoiler]Time is always against us[/spoiler]

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-07-29 : 09:19:47

I think this is the query you needed,

Update emrappointmentdetails
Set bill_number = B.bill_number
from emrappointmentdetails A inner join emrtpbilleditems B
on A.ColumnName = B.ColumnName

Update emrappointmentdetailshistory
Set bill_number = B.bill_number
from emrappointmentdetailshistory A inner join emrtpbilleditems B
on A.ColumnName = B.ColumnName

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-29 : 09:20:55

1)both emrappointmentdetails and emrappointmentdetailshistory tables are identical
2)table emrtpbilleditems where appointment_ids one column is there which is having values like 806658,806659,806660

3)we have to update the from emrtpbilleditems table as comma seperated values for the above two tables bill_number is the column name based on the below table colukm

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-29 : 09:24:48

same thing for oracle too
Update emrappointmentdetails
Set bill_number = B.bill_number
from emrappointmentdetails A inner join emrtpbilleditems B
on A.ColumnName = B.ColumnName

Update emrappointmentdetailshistory
Set bill_number = B.bill_number
from emrappointmentdetailshistory A inner join emrtpbilleditems B
on A.ColumnName = B.ColumnName

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-29 : 09:34:53

Update emrappointmentdetails
Set bill_number = B.bill_number
from emrappointmentdetails A inner join emrtpbilleditems B
on A.ColumnName = B.ColumnName

how to write this in oracle

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-07-29 : 09:46:29

try this although I don't oracle.

Update emrappointmentdetails set bill_number = ( select bill_number from emrtpbilleditems
where A.ColumnName = emrappointmentdetails.ColumnName )

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-29 : 09:47:25

hi can anyone send the oracle query too

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-29 : 09:53:58

HI iam getting exception can ypu please send a proper one

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-29 : 10:13:45

hi your query is working fine but i dont want commas to be included it shoud be comma seperated

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-29 : 10:19:03

how to update comma incuded values to be comma seperated into other table in mssql

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-07-29 : 11:32:40

Can you clearly explain with some sample data for the 3 tables and the expected output.

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-29 : 12:11:42

hi,
iam having two tables
a)emrappointmentdetails b)emrtpbilleditems

emrtpbilleditems

billnum ap_ids ap_id--columns
TNOO 80150,80152 12
TN11 80152,80156,80159 11

emrappointmentdetails

ap_id billnum ---columns
80150 TNOO
80152 TNOO
80152 TN11
80156 TN11
80159 TN11

BOTH TABLES ARE HAVING COMMON ap_id as common

now i have to migrate the script like this,
can anyone help me out in this to update emrtpbilleditems to emrappointmentdetailas as the above ones

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-29 : 12:41:36

hi can anyone provide any example in such scenarios.so that it will be helpful to me

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-07-29 : 13:28:32

Can you show us what your expected results are?

Some days you're the dog, and some days you're the fire hydrant.

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-29 : 22:25:07

i have mentioned in the above one know

like
emrtpbilleitems

billnum ap_ids

TN00 80150,80152

SHOULD BE UPDATED FOR

emrappointmentdetails

ap_id billnum

80150 TN00
80152 TN00

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-07-30 : 01:14:59

Hi Rajasekhar,

declare @emrtpbilleditems table
(
billnum varchar(100),
ap_ids varchar(100),
ap_id int
)
insert into @emrtpbilleditems
select 'TNOO','80150,80152',12 union all
select 'TN11', '80152,80156,80159', 11

insert into emrappointmentdetails
select billnum,cValue from @emrtpbilleditems e
cross apply fn_split(e.ap_ids,',')

here i used fn_Split function, u can get it from here.
http://www.umachandar.com/technical/SQL2000Scripts/UtilityFns/Main7.htm

But my advice is pls try to ask this question in oracle forums instead of sql forums.

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-30 : 02:42:59

hi i have written a function like this
drop function GetAPPids
GO
CREATE FUNCTION dbo.GetAPPids (@bill_no int,@str varchar(4000))
RETURNS @appids TABLE(bill_number int, appointment_id int)
BEGIN
DECLARE @appid int, @delimeter int, @beginid int, @cnt int
SET @cnt = 1
SET @beginid=1
SET @delimeter = CHARINDEX(',', @str, @cnt)
WHILE @delimeter>0
BEGIN
SET @appid = substring(@str,@beginid,@delimeter-1)
INSERT INTO @appids values(@bill_no, cast(@appid as int))
SET @delimeter = CHARINDEX(',', @str, @beginid)
SET @beginid = @delimeter+1
END
RETURN
END
GO

insert into emrappointmentdetails_bak1 values(bill_number,appointment_id)
Select bill_number,appointment_id from dbo.GetAppids(123,'8010840,8010841,8010842')

but iam facing exception with insert statement.how can i insert into the table

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-30 : 03:10:03

[code]
DECLARE @emrtpbilleditems table
(
billnum varchar(100),
ap_ids varchar(100),
ap_id int
)
INSERT INTO @emrtpbilleditems
SELECT 'TNOO','80150,80152',12 union all
SELECT 'TN11', '80152,80156,80159', 11

SELECT * FROM @emrtpbilleditems

DECLARE @finaltable TABLE (billnum VARCHAR(100),
ap_ids INT
)

INSERT INTO @finaltable
SELECT
billnum,replace(SUBSTRING(ap_ids,charindex(',',ap_ids,v.number),abs(charindex(',',ap_ids,charindex(',',ap_ids,v.number)+1)-charindex(',',ap_ids,v.number))),',','')as value
FROM @emrtpbilleditems e
INNER JOIN master..spt_values AS v ON v.Type = 'P'
AND v.number > 0
AND v.number <= len(ap_ids)
AND substring(',' + ap_ids, v.number, 1) = ','

SELECT * FROM @finaltable
[/code]

rajasekhar857
Constraint Violating Yak Guru

396 Posts

Posted - 2009-07-30 : 04:22:27

hi how can i use with my function which i have written

using this insert statement
insert into emrappointmentdetails_bak1 values(bill_number,appointment_id)
Select bill_number,appointment_id from dbo.GetAppids(123,'8010840,8010841,8010842')

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-30 : 06:31:48

insert into emrappointmentdetails_bak1(bill_number,appointment_id)
Select bill_number,appointment_id from dbo.GetAppids(123,'8010840,8010841,8010842')

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources