-2

I have a record in the database:

Column1          Column2

    1               a
    1               b
    1               c

and the result would be:

Column1           Result

    1              abc

i just want this query so that i would not use loop :)

Sachin Shanbhag
  • 54,530
  • 11
  • 89
  • 103
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • 1
    it seems you use SQLServer. So it's a duplicate of http://stackoverflow.com/questions/4894095/sql-group-by-with-concat and http://stackoverflow.com/questions/941103/concat-groups-in-sql-server – bluish Feb 07 '11 at 12:53
  • @bluish: im using mssql server 2005 – John Woo Feb 07 '11 at 13:02
  • Did either of the posts referenced in @bluish's comment answer your question? – Sampson Feb 07 '11 at 14:28

4 Answers4

1

For MS SQL you could use:

Declare @result varchar(1000)
Set @Result = ''

Select 
         @result = (@result  + Column2)

From MyTable
Where Column1 = 1


Select @Result
codingbadger
  • 42,678
  • 13
  • 95
  • 110
0

Aggregate concatenation is not part of the SQL standard for aggregates (SUM, COUNT, AVG, MIN, MAX, LAST, FIRST), so if your DBMS has no such function, you should probably define a new aggregate function.

PostgreSQL documentation offers a nice tutorial on the subject.

Adam Matan
  • 128,757
  • 147
  • 397
  • 562
0

Standard SQL has no CONCATENATE set function by design because all SQL data types are scalar.

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
-4

if you use MySQL or Oracle, you can use:

SELECT CONCAT(col1,col2) FROM YOURTABLE

0xAX
  • 20,957
  • 26
  • 117
  • 206
  • 3
    That's all well and good if he wanted to concat 2 columns, but he wants to concat values in one column depending on another columns value. – anothershrubery Feb 07 '11 at 12:54