CODEWITHSUNDEEP
sql-server-2005t-sql
CodeRedick
CodeRedick

Reputation: 7415

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

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.

Upvotes: 4

Views: 34506

Answers (5)

Allan Simonsen
Allan Simonsen

Reputation: 1238

Try to look at this answer.

It does exactly what you want to do.

Upvotes: 3

shahkalpesh
shahkalpesh

Reputation: 33474

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".

Upvotes: 0

Bartek Szabat
Bartek Szabat

Reputation: 3014

return data as XML

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

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 135011

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

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 415755

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.

Upvotes: 0

Related Questions