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

Question

A few months ago, our supplier added the possibility of our ticketing system, which allows you to add any number of custom fields to your ticket. I would like to request these fields along with other call information for reporting purposes, but each expandable 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 

I would like to request back:

 1001 Test test2 moretest 

I tried using PIVOT, but it pretty much required things like using an aggregate function. Any other ideas on how to do this?

EDIT: I also tried to query each row separately in the main query and use the function ... but both methods are too slow. I need something to immediately get all the lines, PIVOT and then join the main request.

Answer 1

Try to see this answer .

He does exactly what you want to do.

Answer 2

See here. Merge values ​​from multiple rows into a single column ordered by SQL 2005 +

or for version 2000 Combine values ​​from several rows into one column

Answer 3

What you want to do is a vault (some systems call it a cross-tab query). This will help you google for more help, but usually you need to know which columns you expect before writing a query.

Answer 4

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

Using the script on the previous page.

     DECLARE @Values ​​VARCHAR (1000)

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

     SELECT @ValuesEND

EDIT: I don't want to be rude. But you can find this by doing a search for "merging multiple lines into one."

Answer 5

returns data as XML

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