add semicolon in one column - 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)

add semicolon in one column

Author

Topic

palak
Yak Posting Veteran

55 Posts

Posted - 2009-02-12 : 09:46:58

i have this one code...it gives me more than 1000 records...but also some of the duplication means...just only media_type i have different values and so i m getting dupliaction of records..

i have different values for only one contac person in media_type
Television
Friend
Other [Please Specify]
Radio
Conference
Colleague
State Health Department
Newspaper
Television
We Can! State Coordinator
We Can! Partner
We Can! Community Site
We Can! in Action e-newsletter
NHLBI Health Information Network
We Can! Invitation

and i want it in one row..can anyone tell me how can i get all these values by seperating semiclons ';' in mediatype column
like i want
Television;Friend;Other [Please Specify];Radio;Conference;Colleague;State Health Department;Newspaper;We Can! State Coordinator ;We Can! Partner;We ;Can! Community Site;We Can! in Action e-newsletter;NHLBI ;Health Information Network;We Can! Invitation

so i will get in one row..can anyone help me to add ':' in media_type column

SELECT dbo.CONTACT.contact_id, dbo.CONTACT.contact_fname, dbo.CONTACT.contact_mi, dbo.CONTACT.contact_lname, dbo.CONTACT.prog_title,
dbo.CONTACT.contact_organization, dbo.CONTACT.contact_address1, dbo.CONTACT.contact_address2, dbo.CONTACT.contact_city,
dbo.STATE.stateDescription, dbo.CONTACT.contact_stateOther, dbo.CONTACT.contact_country, dbo.CONTACT.contact_zip,
dbo.CONTACT.contact_phone, dbo.CONTACT.contact_phext, dbo.CONTACT.contact_fax, dbo.CONTACT.contact_email, dbo.MEDIA.media_type,
dbo.CONTACT_MEDIA.other_text, dbo.ACTIVITY.date_created, dbo.ACTIVITY.commType, dbo.ACTIVITY.commTypeOther,
dbo.ACTIVITY.intent_level_effort
FROM dbo.STATE INNER JOIN
dbo.CONTACT INNER JOIN
dbo.ACTIVITY ON dbo.CONTACT.contact_id = dbo.ACTIVITY.contact_id ON dbo.STATE.stateID = dbo.CONTACT.stateId INNER JOIN
dbo.MEDIA INNER JOIN
dbo.CONTACT_MEDIA ON dbo.MEDIA.media_id = dbo.CONTACT_MEDIA.media_id ON dbo.CONTACT.contact_id = dbo.CONTACT_MEDIA.contact_id

many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-12 : 09:51:25

replace code in red by:-


 STUFF((SELECT ';'+dbo.MEDIA.media_type FROM dbo.MEDIA INNER JOIN
dbo.CONTACT_MEDIA ON dbo.MEDIA.media_id = dbo.CONTACT_MEDIA.media_id 
WHERE contact_id = dbo.CONTACT.contact_id FOR XML PATH('')),1,1,'')

palak
Yak Posting Veteran

55 Posts

Posted - 2009-02-12 : 09:55:04

thanks visakh for replying..

but i am getting an error -

Incorrect syntax near 'XML'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-12 : 09:57:39

show your query please

palak
Yak Posting Veteran

55 Posts

Posted - 2009-02-12 : 09:59:37

SELECT dbo.CONTACT.contact_id, dbo.CONTACT.contact_fname, dbo.CONTACT.contact_mi, dbo.CONTACT.contact_lname, dbo.CONTACT.prog_title,
dbo.CONTACT.contact_organization, dbo.CONTACT.contact_address1, dbo.CONTACT.contact_address2, dbo.CONTACT.contact_city,
dbo.STATE.stateDescription, dbo.CONTACT.contact_stateOther, dbo.CONTACT.contact_country, dbo.CONTACT.contact_zip,
dbo.CONTACT.contact_phone, dbo.CONTACT.contact_phext, dbo.CONTACT.contact_fax, dbo.CONTACT.contact_email,

STUFF((SELECT ';'+dbo.MEDIA.media_type FROM dbo.MEDIA INNER JOIN
dbo.CONTACT_MEDIA ON dbo.MEDIA.media_id = dbo.CONTACT_MEDIA.media_id
WHERE contact_id = dbo.CONTACT.contact_id FOR XML PATH('')),1,1,''),

dbo.CONTACT_MEDIA.other_text, dbo.ACTIVITY.date_created, dbo.ACTIVITY.commType, dbo.ACTIVITY.commTypeOther,
dbo.ACTIVITY.intent_level_effort
FROM dbo.STATE INNER JOIN
dbo.CONTACT INNER JOIN
dbo.ACTIVITY ON dbo.CONTACT.contact_id = dbo.ACTIVITY.contact_id ON dbo.STATE.stateID = dbo.CONTACT.stateId INNER JOIN
dbo.MEDIA INNER JOIN
dbo.CONTACT_MEDIA ON dbo.MEDIA.media_id = dbo.CONTACT_MEDIA.media_id ON dbo.CONTACT.contact_id = dbo.CONTACT_MEDIA.contact_id

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-12 : 10:00:22

I think you are running this on sql server 2000.. is it ?

palak
Yak Posting Veteran

55 Posts

Posted - 2009-02-12 : 10:01:52

no its sql 2005

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-12 : 10:02:36

Or do not have his compatibility level set to 90 or higher.

E 12°55'05.63"
N 56°04'39.26"

palak
Yak Posting Veteran

55 Posts

Posted - 2009-02-12 : 10:06:11

ok..now got it..thanks to all for replying..thanks visakh, sakets_2000 and peso.

yes, i change the compatibility level to 90 thanks peso..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-12 : 10:06:41

what does this return?


SELECT @@VERSION

EXEC sp_dbcmptlevel,'your db name here'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources