5

If I were to query our ORDERS table, I might enter the following:

 SELECT * FROM ORDERS
 WHERE ITEM_NAME = 'Fancy Pants'

In the results for this query, I might get the following:

 ----------------------------------------------------------------------
 ORDER_ID       WAIST       First_Name       Email
 ----------------------------------------------------------------------
 001            32          Jason            j-diddy[at]some-thing.com
 005            28          Pip              pirrip[at]british-mail.com
 007            28          HAL9000          olhal[at]hot-mail.com

Now, I'm also wanting to pull information from a different table:

SELECT * FROM PRODUCTS WHERE ITEM_NAME = 'Fancy Pants'

 ------------------------------------------
 PRODUCT_ID     Product       Prod_Desc
 ------------------------------------------
 008            Fancy Pants   Really fancy.

In the end, however, I'm actually wanting to condense these records into one row via SQL query:

 -----------------------------------------------------------------------------
 PRODUCT       ORDER_Merged  First_Name_Merged  Email_Merged
 -----------------------------------------------------------------------------
 Fancy Pants   001,005,007   Jason,Pip,Hal9000  j-di[...].com, pirrip[...].com

Anyway, that's how it would look. What I can't figure out is what that "merge" query would look like.

My searches here unfortunately keep leading me to results for PHP. I have found a couple of results re: merging into CSV rows via SQL but I don't think they'll work in my scenario.

Any insight would, as always, be greatly appreciated.

UPDATE:

Ah, turns out the STUFF and FOR XML functions were exactly what I needed. Thanks all!!

 Select
    A.name,
         stuff((
         select ',' + B.address
         from Addresses B
         WHERE A.id=B.name_id
         for xml path('')),1,1,'')
    From Names A
Community
  • 1
  • 1
Jason H.
  • 445
  • 1
  • 5
  • 10
  • Search for `JOIN`. Trust me, you want to learn this. – drdwilcox Nov 23 '11 at 14:50
  • 1
    possible duplicate of [SQL Server: Can I Comma Delimit Multiple Rows Into One Column?](http://stackoverflow.com/questions/2046037/sql-server-can-i-comma-delimit-multiple-rows-into-one-column) – JNK Nov 23 '11 at 14:52
  • This type of thing has been asked hundreds of times. We get at least 2 or 3 per week. Just because an existing question doesn't cover your exact scenario doesn't mean it's not a duplicate. – JNK Nov 23 '11 at 14:53
  • @drdwilcox - it's not an issue with the `JOIN` its an issue with concatenating and grouping I think. – JNK Nov 23 '11 at 14:53
  • 2
    Duplicate of: http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005 BTW: Saying something is a duplicate is not a slight against the author; we just don't want to rehash something we know is answered elsewhere. – xQbert Nov 23 '11 at 14:54
  • 2
    @JNK - It has been asked so many times, that someone actually created a tag [group-concat] for it. So, just do a quick search with the tags [sql-server] and [group-concat] and you'll see a lot of answers for this – Lamak Nov 23 '11 at 18:07
  • Ah, turns out the `STUFF` and `FOR XML` functions were exactly what I needed. Thanks all!! http://stackoverflow.com/questions/4787498/tsql-aggregate-string-for-group-by `Select A.name, stuff(( select ',' + B.address from Addresses B WHERE A.id=B.name_id for xml path('')),1,1,'') From Names A` – Jason H. Nov 29 '11 at 21:19
  • I think answers to [this question](http://stackoverflow.com/q/29586186/4519059) are what you are looking for ;). – shA.t Apr 12 '15 at 07:08

1 Answers1

1

This is an excellent article on various approaches to group concatenation with pro's and con's of each.

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

Personally however, I like the Coalesce approach as I demonstrate here:

https://dba.stackexchange.com/a/2615/1607

Community
  • 1
  • 1
RThomas
  • 10,702
  • 2
  • 48
  • 61
  • Link-only answers are discouraged. They become useless when the referenced resource disappears. Please take the time to sum up the required steps, so that this answer remains valid in the future. – Baz Guvenkaya Dec 12 '14 at 07:34