Table1
ID | Name |
1  | Joe  |
2  | Ron  |

Table2
Table1_ID  | Language 
1          | English
1          | ASL
2          | Spanish
2          | English
2          | French

Result
ID  | Name | Language 1| Language 2
1   | Joe  | English   | ASL
2   | Ron  | Spanish   | English 

quote:

---

Originally posted by cgraus

And here's a better way. This uses FOR XML to build a comma seperated list. It will work for any number of records for the same user.

SELECT id, name,
STUFF((Select ',' + CAST(language AS VARCHAR(1000))
FROM table2 t2
WHERE t1.id = t2.table1_id
FOR XML PATh ('')
),1,1,'')
from table1 t1

---

quote:

---

Originally posted by cgraus

Note that one of your users has three values, and both the pivot solution, and the one I am offering, will only find as many values as you write code for. Here's a way of doing it without pivots.

SELECT id, name,
MAX(CASE WHEN t.row = 1 THEN language ELSE NULL END) as language1,
MAX(CASE WHEN t.row = 2 THEN language ELSE NULL END) as language2,
MAX(CASE WHEN t.row = 3 THEN language ELSE NULL END) as language3
FROM
(
select id, name, language, row_number() OVER (partition by id order by name) as row
FROM table1 t1 inner join table2 t2 on t1.id = t2.table1_id
) t
GROUP BY id, name

---