Geek Answers Handbook

Combining multiple data from rows / records into one row with comma separated fields

If I requested our ORDERS table, I could enter the following:

 SELECT * FROM ORDERS
 WHERE ITEM_NAME = 'Fancy Pants'

In the results of this query, I can 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 also want to extract information from another table:

CHOOSE * FROM PRODUCTS WHERE ITEM_NAME = 'Fancy Pants'

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

In the end, however, I really want to condense these records on a single line using an SQL query:

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

In any case, what would it look like. I can't figure out what this "merge" query looks like.

My searches here, unfortunately, continue to lead me to results for PHP. I found a couple of re results: merging into CSV strings through SQL , but I don't think they will work in my script.

, , .

UPDATE:

Ah, , STUFF FOR XML , . !

 Select
    A.name,
         stuff((
         select ',' + B.address
         from Addresses B
         WHERE A.id=B.name_id
         for xml path('')),1,1,'')
    From Names A
+5

More articles:


All Articles