Getting wrong when i am using stuff

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)

Getting wrong when i am using stuff

Author

Topic

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-07 : 02:39:47

i am having two records in table as below
col1 col2 col3
221 test null
221 test other
221 test2 null
221 test2 other
218 Ascendingorder other
218 Ascendingorder select
864 1&2 3&4

expected output:-
col1 col2 col3
221 test;test2 null;other
218 Ascendingorder other;select
864 1&2 3&4

I want values col2 and col3 seperated with ';'
for its col1 values ( i mean group by col1 )

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-07 : 03:22:44

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

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

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-07 : 06:54:15

[code]

Try this to eliminate amp for &

declare @table table (col1 int,col2 varchar(66),col3 varchar(877))
insert into @table
select 221, 'test', 'null' union all
select 221, 'test', 'other' union all
select 221, 'test2', 'null' union all
select 221, 'test2', 'other' union all
select 218, 'Ascendingorder', 'other' union all
select 218, 'Ascendingorder', 'dff' union all
select 864, '1&2', '3&4' union all
select 864, '&asdf&' ,'asdf'

select distinct col1,REPLACE(stuff((select distinct ';'+ col2 from @table where col1 = t.col1 for xml path('')),1,1,''),'&','&'),
replace(stuff((select distinct ';'+ col3 from @table where col1 = t.col1 for xml path('')),1,1,''),'&','&') from @table t

[/code]

Jai Krishna

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-09 : 01:13:01

Hi jai thanks for response

i used ur query , the output will differ from my expected output

plz check my output, i want that format

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-09 : 01:24:26

[code]
hai jai,
i got the solution

declare @table table (col1 int,col2 varchar(66),col3 varchar(877))
insert into @table
select 221, 'test', 'null' union all
select 221, 'test', 'other' union all
select 221, 'test2', 'null' union all
select 221, 'test2', 'other' union all
select 218, 'Ascendingorder', 'other' union all
select 218, 'Ascendingorder', 'select' union all
select 864, '1&2', '3&4'

select distinct col1,replace(stuff((select distinct ';'+ case when charindex('&',col2,1) >0 then replace(col2,'&',',') else col2 end from @table where col1 = t.col1 for xml path('')),1,1,''),',','&'),
replace(stuff((select distinct ';'+ case when charindex('&',col3,1) >0 then replace(col3,'&',',') else col3 end from @table where col1 = t.col1 for xml path('')),1,1,''),',','&') from @table t
order by col1

[/code]

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-09 : 01:26:39

My Solution too works Nageswar but it is being edited automatically

Jai Krishna

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-09 : 01:27:16

Ok Ok Jai, Thanks any way for response

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-02-09 : 01:27:56

Welcome Nageswar

Jai Krishna