SQL: Select As Columns

Question

Hi I have a table with data in it as such:

Cars:
id | name
----------
1  | Buick
2  | Honda
3  | Toyota

What I would like to do in a Stored Proecdure is get the result like this:

Temp Table:
Buick | Honda | Toyota
----------------------

I realize it has no values, but I just want to get this part first.

I am guessing this would involve some sort of temporary table. Thank you.

Using MS SQL 2005, thank you.

Answer 1

Are you looking for Pivot?

Here's a (slightly contrived) example based on the MSDN example modified for your example:

SELECT 'Count' AS Header,
 [Toyota], [Buick], [Honda]
FROM (SELECT id, Name FROM Cars) AS SourceTable
 PIVOT( COUNT(ID) FOR Name IN ([Toyota], [Buick], [Honda])) AS PivotTable

Answer 2

You want string concatenation, or PIVOT as above suggested? question was ambiguous, you want row values to turn into columns (PIVOT), or output?

just read this like 1 hour ago on MSDN forum Use FOR XML

SELECT 
STUFF(
(
SELECT ' ' + name
FROM @table
FOR XML PATH('')
), 1, 1, '') as concatenated_string

Answer 3

You could create a temporary table, then create a cursor for a selection from the Cars table and for each row dynamically create and execute an SQL query that adds a field to the temporary table.

But why are you trying to do something like this? You are mixing data with meta data, and that is rarely a good idea. The code for populating the temporary table with data will be even worse than the code for creating it.

I think that you are too much focused on getting the output from the stored procedure to look like how you are going to display it. Instead try to figure out how the data should be arranged to be convenient to work with.