Hanaka Hanaka - 1 year ago 75
SQL Question

Create table with column names from another tables column data

I have table with a single column like this:

| col |
| A |
| B |
| C |

I want to create a new table with the following column names like this:

| A | B | C |

Any suggestions? Thanks a lot.

Answer Source

One way is to use dynamic sql.
Assuming data type int for all columns, you can do something like this:

Create and populate sample table (Please save us this step in your future questions):

DECLARE @T table
    col char(1)

INSERT INTO @T VALUES ('a'), ('b'), ('c')

Build the dynamic sql:

DECLARE @Sql nvarchar(max) = 'CREATE TABLE YourNewTableName ('

SELECT @Sql = @Sql + col +' int,'

SET @Sql = LEFT(@Sql, LEN(@Sql) - 1) +');'
--Print @sql

Execute it (You will probably want to print it before to make sure it's correct):

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download