1

is there anywhere to get values like this?

from

id        column_1        column_2
1         abc             hello 
1         abc             world

to

id        column_1        column_2
1         abc             hello world

Thanks alot :)

Bob Kaufman
  • 12,864
  • 16
  • 78
  • 107
Chia Yong
  • 11
  • 1
  • 3
    Yes, but it's horribly inefficient compared to designing your schema better, or letting the presentation layer do it rather than the database layer :-) – paxdiablo Aug 22 '11 at 02:54
  • @paxdiablo thanks, canyou provide me solution? – Chia Yong Aug 22 '11 at 02:58
  • @andrain thanks, may this use for mssql? – Chia Yong Aug 22 '11 at 02:59
  • Does an ID always correspond to the same column_1 value or? i.e. can you have 1 for ID and 'def' for column_1? Regardless this can be done with XML Path with an empty string and a group by clause. The example provided by Adrian is perfect – deutschZuid Aug 22 '11 at 03:00
  • @Adrian Dude, stop spamming the same link. – deutschZuid Aug 22 '11 at 03:02
  • possible duplicate of [How to use GROUP BY to concatenate strings in SQL Server?](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) – Bohemian Aug 22 '11 at 03:02
  • Sorry guys, I'm on my phone and my answer kept going as comment. I'll try to get rid of those – Adriano Carneiro Aug 22 '11 at 03:04
  • 2
    How do you know that these two values will be combined to `Hello World` and not to `World Hello` ?? I don't see any "sequence" in there..... – marc_s Aug 22 '11 at 04:40

4 Answers4

2

I am assuming that a given value in column_1 doesn't always correspond to the same value in ID and vice versa.

DECLARE @temptable TABLE (id integer, column_1 varchar(20), column_2 varchar(20))

INSERT into @temptable 
select 1 ,'abc' ,'hello' 
UNION 
select 1 ,'abc','world' 
union 
select 1, 'def', 'blah' 
union 
select 2, 'abc', 'world'
union
select 2, 'abc', 'blah'

select id, column_1, 
(SELECT column_2 + ' ' 
 FROM @temptable t2
    where t2.id = t1.id and t2.column_1 = t1.column_1
 FOR XML PATH('') 
 ) new_column_2 
from @temptable t1
group by id, column_1

Results:

id  column_1    new_column_2
1   abc         hello world 
1   def         blah 
2   abc         blah world
deutschZuid
  • 1,028
  • 2
  • 15
  • 33
0

In Mysql, you can use GROUP_CONCAT, something like this:

SELECT id, column_1, GROUP_CONCAT(column2 SEPARATOR ' ') FROM some_table GROUP BY id, column_1
xdazz
  • 158,678
  • 38
  • 247
  • 274
0

Well, to be quite literal about it: 

DECLARE @t TABLE (id integer, column_1 varchar(20), column_2 varchar(20))

INSERT into @t
    SELECT 1 ,'abc' ,'hello' 
    UNION  
    SELECT 1 ,'abc','world' 

SELECT DISTINCT 
     id, column_1, 
     (SELECT column_2 + ' ' FROM @t FOR XML PATH('') ) column_2 
FROM @t
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
aquinas
  • 23,318
  • 5
  • 58
  • 81
  • I am sure this table has more data in it than just the two rows. With your query, the column_2 column will display the values of every row in column_2 concatenated together for every combination of id and column_1, which I am sure is not what the original poster wants. – deutschZuid Aug 22 '11 at 03:09
  • If you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Aug 22 '11 at 05:23
0

In Sql Server, you use the FOR XMLcontruct for that.

Look at this example here:

SQL Query to get aggregated result in comma seperators along with group by column in SQL Server

Community
  • 1
  • 1
Adriano Carneiro
  • 57,693
  • 12
  • 90
  • 123