Is there any way to put subquery in the column list of a SQL query-CodePudding

I am trying to write a SQL query that has a column list that generates from another SQL query. I am working on Microsoft SQL Server Management Studio.

This is the SQL query that gets a columns list

SELECT COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_NAME = 'table_name'

It returns the following

|          | COLUMN_NAME    |
| -------- | -------------- |
| 1        | name1          |
| 2        | name2          |
| 3        | name3          |

The is the SQL query that I currently have and it works well

SELECT DISTINCT name1, name2 
FROM [table_catalog].[table_schema].[table_name]

This is what it returns

|          | name1          |name2          |
| -------- | -------------- |-------------- |
| 1        | value1         |a1             |
| 2        | value2         |a2             |
| 3        | value3         |a3             |
| 3        | value4         |a1             |

What I would like to do is get a table that contains all the columns from the first table only, like this

|          | name1          |name2          |name3          |
| -------- | -------------- |-------------- |-------------- |
| 1        | value1         |a1             |b1             |
| 2        | value2         |a2             |b2             |
| 3        | value3         |a3             |b1             |
| 3        | value4         |a1             |b2             |

This is my attempt so far

SELECT DISTINCT
    (SELECT COLUMN_NAME 
     FROM INFORMATION_SCHEMA.COLUMNS 
     WHERE TABLE_NAME = 'table_name') 
FROM 
    [table_catalog].[table_schema].[table_name]

It does not work as expected, I also tried to use CLE and some other ways to do it. However, none of them works. It seems like not many people did this kind of query after I did some research online, I wonder is there another way to do it? What did I do wrong?

CodePudding user response：

You will have to use dynamic sql for this.
here is a small step by step example. The complete code will be at the end.

First we need to retrieve the column names in a variable. We want this variable filled with all columns from table_name and seperated by a comma. That is why we use for XML path('') The stuff is for dumping the first comma.

declare @ColNames varchar(max)

select @ColNames = stuff(( select ', '   column_name
                           from   INFORMATION_SCHEMA.COLUMNS
                           where  table_name = 'table_name'
                           for XML path('')
                          ), 1, 2, '')

The content of the variable @ColNames will look like this

name1, name2, name3

with this we can build our sql statement, also in a variable

declare @sql varchar(max)
set @sql = 'select '   @ColNames   ' from table_name'

and finally we execute it

exec(@sql)

That should give you the results you need.

And off course, you could also put the table name table_name into a variable, making it all a bit more flexible.
Here is the complete code:

declare @TableName varchar(100) = 'SomeTableName'

declare @ColNames varchar(max)
select @ColNames = stuff(( select ', '   column_name
                           from   INFORMATION_SCHEMA.COLUMNS
                           where  table_name = @TableName
                           for XML path('')
                          ), 1, 2, '')

declare @sql varchar(max)
set @sql = 'select '   @ColNames   ' from '   @TableName

exec(@sql)