Sql table with multiple rows as a comma delimeted field in another table?

Question

Possible Duplicate:
SQL Server: Can I Comma Delimit Multiple Rows Into One Column?

Is this possible? i have a temp table that can have many rows ex:

  interaction type     name   points
        6             India     50
        8             India     100

in my main table CategoriesTable i have one field called HintText

How can i get those to rows as HintText = 6,India,50|6,India,100?

here my code so far:

 UPDATE #CategoriesTable
  SET HintText =  t.Name + ',' + t.Points + ',' + t.interactiontype
 FROM #CategoriesTable
 INNER JOIN #temp1 t ON t.userId = #CategoriesTable.UserId
 WHERE t.userId = #CategoriesTable.UserId

in my sproc:

 HintText nvarchar(256),

and in my repository:

 trainingModuleProgressState.HintText = row["HintText"].ToString();

in my datamodel:

 public string HintText { get; set; }

this will give me one row, how do i get the second row in?

thanks just one other thing, when the stored proc returns to my repository where i call this sproc i get an error: Conversion failed when converting the varchar value ',' to data type smallint. HintText in my sproc is nvarchar(256) and in reposotory it is string please se OP — charlie_cat, Jan 04 '13 at 08:10
I don't see userID in your temp table, and do you want CategoriesTable to store only hints for each userID but not the actual userID? — Bernhard Barker, Jan 04 '13 at 08:13
Looks like your t.points is smallint, hence the error. That can be resolved by + CAST(t.points AS varchar) + — Raj, Jan 04 '13 at 08:16
oooo let me see quick, thanks i tried it but still get same error? — charlie_cat, Jan 04 '13 at 08:18
Use `CONCAT(a,b,c)` not a + b + c or `CONCAT_WS(',',a,b,c)` instead of a + ',' + b + ',' + c — Popnoodles, Jan 04 '13 at 08:19
CONCAT_WS with a comma by the look of your code. `SET HintText =(SELECT CONCAT_WS(',', Name, Points, interactiontype) FROM thetable AS t )` but you still have to work out how to select the correct row — Popnoodles, Jan 04 '13 at 08:22
oops with the concat/concat_ws i get that it is not a recognized built in function, yikes — charlie_cat, Jan 04 '13 at 08:24

score 0 · Answer 1 · edited May 23 '17 at 12:27

0

I think you want something similar to GROUP_CONCAT in MySQL. Unfortunately there seems to be no easy way to do this in SQL Server. Here's one of many discussions on it.

MySQL GROUP_CONCAT would look something like: (I know the syntax is 100%)

UPDATE #CategoriesTable
SET HintText = (SELECT GROUP_CONCAT(t.Name + ',' + t.Points + ',' + t.interactiontype)
                FROM #temp1 t
                WHERE t.userId = #CategoriesTable.UserId)

edited May 23 '17 at 12:27

Community

1
1

answered Jan 04 '13 at 08:26

Bernhard Barker

54,589
14
104
138

im using sql server 2008 r2 not mysql :) – charlie_cat Jan 04 '13 at 08:40
@charlie_cat I know, I'm just mentioning MySQL because it has the function which allows you do this very easily (and resultantly many people look for an SQL Server alternative, thus knowing this makes an SQL Server solution easier to find online), and providing the code to do it with `GROUP_CONCAT` makes it easier to apply an SQL Server algorithm in the link provided to your problem. – Bernhard Barker Jan 04 '13 at 08:44
@Dukeling: Fair warning, but answers to using unrelated tech/products risk downvoting. That said, see: http://stackoverflow.com/questions/2046037/sql-server-can-i-comma-delimit-multiple-rows-into-one-column – OMG Ponies Jan 04 '13 at 13:13

Sql table with multiple rows as a comma delimeted field in another table?

1 Answers1