[dba-SQLServer] Multiple rows to a single row

Paul, maybe you can insert it into a temp table (Lets assume it is called
tblTemp) then create a udf which does something like this:

CREATE FUNCTION udfTableToList (@PoolCar AS VARCHAR(50), @intMode AS INT)
RETURNS VARCHAR(1000)
AS

BEGIN
    DECLARE @str VARCHAR(100)

    IF @IntMode = 1 SELECT @str = ISNULL(@str,'') + DriverName + ',' FROM
tblTemp WHERE PoolCar = @PoolCar

    ELSE IF @IntMode = 2    SELECT @str = ISNULL(@str,'') + Town + ',' FROM
tblTemp WHERE PoolCar = @PoolCar

    RETURN LEFT(@str,LEN(@Str)-1)
END


You call the udf as such:

SELECT DISTINCT PoolCar, dbo.udfTableToList(PoolCar,1) AS Driver, Region,
dbo.udfTableToList(PoolCar,2) AS Town FROM tblTemp

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