0

I am trying to use the stuff function in MS SQL to stuff certain info. Here is the example:

Number          Value 
1                 1
2                 1
3                 1
1                 2
2                 2
3                 2
1                 3
2                 3

I would like to stuff the column so that only one record will display as following:

Value             Number
1                 1,2,3
2                 1,2,3
3                 1,2

Please note that there are a like n-Numbers and n-Values.

Taryn
  • 242,637
  • 56
  • 362
  • 405
user2664537
  • 1
  • 1
  • 1
  • [group_concat](http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat) function in MySQL will work. You have tagged this as MySQL but you stated MS SQL, what DB are you using? – Taryn Aug 08 '13 at 14:15

1 Answers1

1

You can use GROUP_CONCAT for this. For example:

SELECT `Value`, GROUP_CONCAT(DISTINCT `Number` ORDER BY `Number`)
FROM `yourTable`
GROUP BY `Value`
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
  • Hey, thanks for the help. Still I get an error. My query looks like this: SELECT 'value' ,GROUP_CONCAT(distinct 'number' order by 'number') FROM 'table1' JOIN 'table2' -- i need to join the table in order to get the data together JOIN 'table3' -- i need to join the table in order to get the data together GROUP BY 'value' Something seems to be wrong with the ' GROUP_CONCAT(DISTINCT `Number` ORDER BY `Number`)' - because the rest of the query seems fine and i can excute. thanks for every help! – user2664537 Aug 12 '13 at 08:45
  • @user2664537 You should really post a different question for that and include the error. – Mark Rotteveel Aug 12 '13 at 08:57