How can I return multiple rows as a single row in T-SQL?

Question

A few months ago our vendor added a capability to our ticketing system which lets us add any number of custom fields to a ticket. I'd like to query these fields out along with the other call information for reporting purposes, but each extensible field is stored as a row in the database. So basically you have something like this:

ext_doc_no    call_record    value
1             1001           Test
2             1001           test2
3             1001           moretest

What I'd like is to query back:

1001    Test    test2    moretest

I've tried to use PIVOT, but that's rather demanding about things like using an aggregate function. Any other ideas on how to do this?

EDIT: I also tried querying each row separately into the main query, and using a function... but both methods are way too slow. I need something to get all the rows at once, PIVOT them and then join into the main query.

Answer 1

What you want to do is a pivot (some systems call it a crosstab query). That should help you google for additional help, but generally you need to know what columns you expect before writing the query.

Answer 2

See here Concatenate Values From Multiple Rows Into One Column Ordered SQL 2005+

or for a 2000 version Concatenate Values From Multiple Rows Into One Column

Answer 3

http://www.sqlservercentral.com/scripts/Miscellaneous/32004/

Using the script from above page.

    DECLARE @Values VARCHAR(1000)

    SELECT @Values = COALESCE(@Values + ', ', '') + Value
    FROM .....
    WHERE ....

    SELECT @ValuesEND

EDIT: I don't want to be rude. But you could find this by searching "combining multiple rows into one".

Answer 4

return data as XML

SELECT ...
FROM ...
...JOIN....
FOR XML AUTO