SQL Syntax

Hi,

I need some help with SQL to do the following:

Table1

1 John Smith
2 Jess Green

Table2

1 One
1 Two
2 Three
2 Four


What I need is a SQL statement which will give me the following.

John Smith One, Two
Jess Green Three, Four


Is this possible with just SQL Syntax?



Thanks in advance.

Google for CROSSTAB and SQL Server, it may give you a hint on how it can be done.

Hello all. This is a very interesting problem.
http://msdn.microsoft.com/en-us/library/ms177410.aspx

Hi thanks for the reply.

The examples above show me how to do this with a set number of fields, is it possible to do this with a undefined number.

By the way it suppose to look like this. however the number of rows for each person can be different also.

HTML Code:

---

<table>
  <tr>
    <td>John Smith</td>
    <td>One, Two</td>
  </tr>
  <tr>
    <td>Jess Green</td>
    <td>Three, Four</td>
  </tr>
</table>

---

I used MS Access 2000.

Code:

---

Table1
ID  Name
1  John Smith
2  Jess Green
3  Dorian Gray

---

Code:

---

Table2
idName        in_words
1      One
1      Two
2      Three
2      Four
3      Five
3      Six
3      Seven

---

This query is slow, but it works.

Code:

---

TRANSFORM First(SQL.in_words) AS FD
SELECT SQL.Name
FROM
    [SELECT T1.Name, Count(*) AS SN, T22.in_words
        FROM (Table2 AS T21
        INNER JOIN Table2 AS T22
        ON (T21.in_words<=T22.in_words) AND (T21.idName=T22.idName))
        INNER JOIN Table1 AS T1
        ON T1.ID=T22.idName
        GROUP BY T1.Name, T22.in_words].
    AS SQL
GROUP BY SQL.Name
PIVOT SQL.SN

---