concatenation

SQL Server Developer >> concatenation

need assistance.

create table t1( c1 int , c2 int , c3 char(2) )

insert into t1
select '1','1','one'
union
select '1','2','two'
union
select '2','1','three'
union
select '2','2','four'
union
select '3','1','five'
union
select '3','2','six'

desired output :-

c1 c3
1 one+two
2 three+four
3 five+six

thanks,
nj

SQL Server138

SQL Server Developer >> concatenation here you go

select rs.c1, rs.c3 + rs1.c3 from
(select c1,c2,c3 from #t1 where c2=1
)rs
inner join
(
select c1,c2,c3 from #t1 where c2=2
)rs1 on rs.c1=rs1.c1

VT
Knowledge is power,share it
http://oneplace4sql.blogspot.com/

> need assistance.
>
> create table t1( c1 int , c2 int , c3 char(2) )
>
> insert into t1
> select '1','1','one'
> union
> select '1','2','two'
> union
> select '2','1','three'
> union
> select '2','2','four'
> union
> select '3','1','five'
> union
> select '3','2','six'
>
> desired output :-
>
> c1 c3
> 1 one+two
> 2 three+four
> 3 five+six
>
> thanks,
> nj
>

SQL Server Developer >> concatenation DROP table #tmp
GO
create table #tmp( c1 int , c2 int , c3 VARCHAR(5) )
GO

insert into #tmp
select '1','1','one'
union
select '1','2','two'
union
select '2','1','three'
union
select '2','2','four'
union
select '3','1','five'
union
select '3','2','six'
GO

-- Deal with two levels
SELECT a.c1, a.c3 + '+' + b.c3
FROM #tmp a
INNER JOIN #tmp b ON a.c1 = b.c1
AND a.c2 < b.c2

-- Deal with multiple levels
SELECT c1, STUFF( ( SELECT '+' + c3 FROM #tmp a WHERE b.c1 = a.c1 FOR XML
PATH('')),1 ,1, '')
FROM #tmp b
GROUP BY c1

Hope that helps.

wBob

SQL Server Developer >> concatenation There are no built-in operators for aggregate concatenation in transact-SQL.
So you'll have to devise one; some ideas can be found at:
www.projectdmx.com/tsql/rowconcatenate.aspx

--
Anith