SQL Syntax

[Developer2007]

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.

[dee-u]

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

[klen_]

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

[Developer2007]

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.

[Developer2007]

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

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

[klen_]

I used MS Access 2000.

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

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.

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