What's the syntax to pivot this?

Question

I want to get a set of columns from a temp table as a row. The following:

 select name from tempdb.sys.columns where object_id = object_id('tempdb..##myTempTable')

yields something like

|name|
------
Col1
Col2
Col3
Col4
...

Can I pivot this to get col1 | Col2 | Col3 | Col4 | ... ? In columns, NOT as a single value.

I won't know the names of the columns ahead of time.

Possible duplicate of [get a comma delimited string from rows](https://stackoverflow.com/questions/41787834/get-a-comma-delimited-string-from-rows) — Zohar Peled, May 08 '18 at 16:02
To clarify, I don't want comma seperated values (as the other questions returned), but columns. — Patrick Schomburg, May 08 '18 at 17:14
But it might be a duplicate of this one: [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Zohar Peled, May 08 '18 at 19:17

Joel Coehoorn · Accepted Answer · 2018-05-10T14:10:04.993

There is no single syntax that will pivot this data in that way.

The SQL language (for any database) has firm requirement that you know the number and type of each column in the results at query compile time. This is needed in order for the database engine to determine correct permissions and compute the execution plan. Even SELECT * queries meet this requirement, as the number and types of columns in the tables used by the query are fixed and known (at least for the life of a single query).

What this question asks to do breaks that requirement. The column headers are determined by the data, and therefore the database engine can't know how many columns it needs for the results until after the query begins to execute.

The only way to get around this is to break the query into three separate steps:

Run a simpler query to determine the column headings for the final results.
Use the results from #1 to build a new SQL string on the fly
Execute the query from #2 and return it's results to the user.

You're right. Even the answer from @Horaciux gives a row of column names. — Patrick Schomburg, May 10 '18 at 14:03

Horaciux · Answer 2 · 2018-05-10T18:07:07.527

-1

 declare @cols varchar(max)=''
 declare @query varchar(max)
 select @cols=''''+name+''','+@cols from tempdb.sys.columns 
 where object_id = object_id('tempdb..##myTempTable')

 select @cols=left(@cols,len(@cols)-1)

set @query= 'select ' + @cols 

execute(@query)

If you need the name column, change this line

 select @cols=''''+name+''''+ name  +','+@cols from  sys.columns 
  where object_id = object_id('hrc.dbo.car')

edited May 10 '18 at 18:07

answered May 10 '18 at 01:00

Horaciux

6,322
2
22
41

Why the downvote? Also, I don´t get your comment @PatrickSchomburg – Horaciux May 10 '18 at 14:16

What's the syntax to pivot this?

2 Answers2