Correcting the SQL syntax?

Question

Thanks very much for those responded. Is there a way in SQL server that takes the data from table1 and outputs the data like table2?

Thanks!

Table1:

+---------+-----------+----------+------------------+
|  Name   |    DOB    | Agent ID |    Agent Name    |
+---------+-----------+----------+------------------+
| subject | 4/20/1960 | 4444     | Smith            |  
+---------+-----------+----------+------------------+
| subject | 4/20/1960 | 4444     | John             |
+---------+-----------+----------+------------------+
| subject | 4/20/1960 | 4444     | Larry            |
+---------+-----------+----------+------------------+

Table2:

+---------+-----------+----------+------------------+
|  Name   |    DOB    | Agent ID |    Agent Name    |
+---------+-----------+----------+------------------+
| subject | 4/20/1960 | 4444     | Smith,John,Larry |
+---------+-----------+----------+------------------+

First off, you didnt tell us the error and second - that is not valid T-SQL — keithwarren7
– keithwarren7, Commented Jun 23, 2010 at 0:47
You need to describe what the error is before anyone can help. — Benny
– Benny, Commented Jun 23, 2010 at 0:48
The function is PLSQL, as in coded for Oracle or PostgreSQL - not TSQL for SQL Server. If this is to be for SQL Server, what version? Hopefully 2005+? — OMG Ponies
– OMG Ponies, Commented Jun 23, 2010 at 0:52
possible duplicate of SQL Server: Can I Comma Delimit Multiple Rows Into One Column? — OMG Ponies
– OMG Ponies, Commented Jun 23, 2010 at 0:55
Table2 violates 1NF because column Agent Name uses a non-scalar data type. Better done elsewhere e.g. in a reporting tool. — onedaywhen
– onedaywhen, Commented Jun 25, 2010 at 14:54

OMG Ponies · Accepted Answer · 2010-06-23 00:58:55Z

2

For SQL Server 2005+, use the STUFF & FOR XML PATH to create a comma separated list:

SELECT DISTINCT
       t.name,
       t.dob,
       t.agentid,
       STUFF(ISNULL(SELECT ', ' + x.agentname
                      FROM TABLE1 x
                     WHERE x.agentid = t.agentid
                  GROUP BY x.agentname
                   FOR XML PATH ('')), ''), 1, 2, '')
  FROM TABLE1 t

answered Jun 23, 2010 at 0:58

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Scot Hauder · Accepted Answer · 2010-06-23 02:31:21Z

1

SELECT DISTINCT t.Name, t.DOB, t.AgentID, x.AgentName 
FROM Table1 t
CROSS APPLY (SELECT CASE WHEN ROW_NUMBER() OVER (ORDER BY AgentName) = 1 
                         THEN '' ELSE ', ' END + AgentName 
             FROM Table1
             WHERE AgentID = t.AgentID
             AND AgentName IS NOT NULL
             FOR XML PATH(''))x(AgentName)

edited Jun 23, 2010 at 2:31

answered Jun 23, 2010 at 2:19

Scot Hauder

2461 silver badge4 bronze badges

Collectives™ on Stack Overflow

Correcting the SQL syntax?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related