[dba-SQLServer] Multiple rows to a single row

Paul Hartland paul.hartland at googlemail.com
Thu Jun 11 06:18:40 CDT 2009


To all,

I am sure I have seem this before on the list, but cant seem to find it.  I
have a query similar to the one below:

SELECT     TOP (100) PERCENT dbo.PoolCarTable.PoolCar,
                      CASE WHEN
dbo.EmployeeAvailability.[DriverOrPassenger?] = 'D' THEN
dbo.PersonnelTable.[Forename/s] + ' ' + dbo.PersonnelTable.Surname ELSE ''
END AS DriverName,
                       CASE WHEN
dbo.EmployeeAvailability.[DriverOrPassenger?] = 'D' THEN
dbo.PersonnelTable.Office ELSE '' END AS Region,
                      CASE WHEN
dbo.EmployeeAvailability.[DriverOrPassenger?] = 'D' THEN
dbo.PersonnelTable.Town ELSE '' END AS Town
FROM         dbo.JobDataTable INNER JOIN
                      dbo.EmployeeAvailability ON dbo.JobDataTable.JobNo =
dbo.EmployeeAvailability.JobNo INNER JOIN
                      dbo.PersonnelTable ON
dbo.EmployeeAvailability.PayrollNo = dbo.PersonnelTable.PayrollNo RIGHT
OUTER JOIN
                      dbo.PoolCarTable ON dbo.EmployeeAvailability.CarNo =
dbo.PoolCarTable.PoolCar
WHERE     (dbo.EmployeeAvailability.JobDate BETWEEN '06/01/09' AND
'06/07/09') AND (dbo.JobDataTable.CanDate IS NULL)
GROUP BY dbo.PoolCarTable.PoolCar,
                      CASE WHEN
dbo.EmployeeAvailability.[DriverOrPassenger?] = 'D' THEN
dbo.PersonnelTable.[Forename/s] + ' ' + dbo.PersonnelTable.Surname ELSE ''
END,
                      CASE WHEN
dbo.EmployeeAvailability.[DriverOrPassenger?] = 'D' THEN
dbo.PersonnelTable.Office ELSE '' END,
                      CASE WHEN
dbo.EmployeeAvailability.[DriverOrPassenger?] = 'D' THEN
dbo.PersonnelTable.Town ELSE '' END
ORDER BY dbo.PoolCarTable.PoolCar, DriverName DESC

An example of what it returns is:

PoolCar         DriverName               Region             Town
PC01             Smith                       North                York
PC01             Taylor                       North                Hull
PC02             Russell                     South               London
PC02             Loft                           South               London
PC02             Burns                        South               Middlesex

What I need to achieve is:

 PoolCar         DriverName               Region             Town
PC01             Smith,Taylor             North                York, Hull
 PC02             Russell,Loft,Burns     South               London,
Middlesex

Can anyone point me in the right direction please, I am starting to think
that I will have the change my query, and have sub queries etc.

Thank you in advance for any help on this.

-- 
Paul Hartland
paul.hartland at googlemail.com



More information about the dba-SQLServer mailing list