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
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,'
FROM @T
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):
EXEC(@Sql)
Source (Stackoverflow)