CODEWITHSUNDEEP
mysqlsql
user6735515
user6735515

Reputation:

MySQL values to be in one column just separated by a comma

So I have a query that is pulling alot of data together and I would like it to only have one row vs x amount, and it just be one but the column Product ID, which is the last one, to just be all on one line, example data:

ID  FN      LN      MEM     REP     EMAIL                   PID
001 Test    User    1001    1001    [email protected]     001
001 Test    User    1001    1001    [email protected]     002
001 Test    User    1001    1001    [email protected]     003
001 Test    User    1001    1001    [email protected]     004
001 Test    User    1001    1001    [email protected]     005
001 Test    User    1001    1001    [email protected]     006
001 Test    User    1001    1001    [email protected]     007

But would like the output to be:

001 Test    User    1001    1001    [email protected]     001,002,003,004,005,006,007

My SQL knowledge is not super strong so im kinda lost, any help would be awesome. I tried GROUP BY but some of the data as different values in the fourth column so it wont always work.

Upvotes: 0

Views: 48

Answers (3)

Khris Kramer
Khris Kramer

Reputation: 68

Try GROUP_CONCAT()

It's the same thing as Stuff For XML in TSQL

Upvotes: 0

justarustyspoon
justarustyspoon

Reputation: 81

GROUP_CONCAT is probably what you're looking for. So you can group and concatenate the Product ID and group it by a common attribute of your data records.

Upvotes: 2

gen_Eric
gen_Eric

Reputation: 227240

You're looking for GROUP_CONCAT along with GROUP BY.

SELECT ID, LN, EMAIL, GROUP_CONCAT(PID) as products
FROM tableName
GROUP BY ID

Or something like that. GROUP_CONCAT(productID) will combine them into one row and GROUP BY tells it how to combine the rows together.

If you remove the GROUP BY, you will get one row with all the results found. If you add it, it tells it how to combine the rows, which field to match.

Upvotes: 3

Related Questions