0

I am working on SQL Server 2016.
I want to know is there anyway I can create a permanent or temporary table with column names present in another table?
See the table: MAINTAGS and its code below. I want DOB as my first column, POB as 2nd and so on...
Currently there are 12 names in my MAINTAGS table, so new table will have total 12 columns.

More names can be added as well in MAINTAGS

ID_MAINTAGS NAMES_MAINTAGS
============== ==============
1 DOB
2 POB
3 citizen
4 nationality
5 Additional Sanctions Information
6 Passport
7 National ID No.
8 Email Address
9 Gender
10 a.k.a.
11 Linked To:
12 Phone Number

IF OBJECT_ID('dbo.MAINTAGS', 'U') IS NOT NULL 
  DROP TABLE dbo.MAINTAGS; 

CREATE TABLE MAINTAGS(ID_MAINTAGS INT IDENTITY(1,1), NAMES_MAINTAGS VARCHAR(MAX));

INSERT INTO MAINTAGS (NAMES_MAINTAGS)
VALUES
('DOB'),('POB'),('citizen'),('nationality'),
('Additional Sanctions Information'), ('Passport'),('National ID No.'),
('Email Address'),('Gender'),('a.k.a.'),('Linked To: ')
Tom
  • 15
  • 1
  • 8
  • What data types should all these columns be? Where does that information come from? Should they be `NULL`able or not? Again, where is this defined? – Thom A Feb 03 '22 at 10:59
  • I want them all to be VARCHAR(MAX). AS to where this info comes from, thats what I am asking in my post whether something LIKE this even POSSIBLE? – Tom Feb 03 '22 at 11:04
  • 1
    *"I want them all to be VARCHAR(MAX)"* I will admit, that is a poor idea. Does your new table *really* need to be up to 24GB in size per row? DOB, for example, normally means "Date of Birth"; why do you need **~2 billion** characters to store a single DOB value? – Thom A Feb 03 '22 at 11:05
  • Ok lets assume I just need 3 columns, all type INT. Point is to get the "names" of "columns" stored in another table. Because these names can be changed too so i need to store them in a table. – Tom Feb 03 '22 at 11:11
  • Is my answer to another question help you? https://stackoverflow.com/a/71078580/6690823 – Maxim Feb 14 '22 at 10:01

1 Answers1

1

Let me start by stating that this smells strongly of an XY Problem and the fact you want to do this very strongly suggests a design flaw.

I'm also going to assume that you have something that defines the data type; I'm going for some columns with the data type, length, precision and scale properties. If you don't have this, add them.

Anyway, you can achieve this with some simple string aggregation, and then execute the dynamic statement:

DECLARE @SchemaName sysname = N'dbo',
        @TableName sysname = N'NewTableName';

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

DECLARE @Delimiter = N',' + @CRLF;

SELECT @SQL = N'CREATE TABLE ' + QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName) + N'(' + @CRLF +
              STRING_AGG(N'    ' + QUOTENAME(YT.ColumnName) + N' ' + QUOTENAME(YT.DataType) + 
              CASE WHEN YT.Length IS NOT NULL OR YT.Precision IS NOT NULL OR YT.Scale IS NOT NULL THEN N' (' + CONCAT_WS(',',YT.Length,YT.Precision,YT.Scale) + N')' ELSE N'' END,@Delim)
FROM dbo.YourTable YT;

--PRINT @SQL --Your best friend.
EXEC sys.sp_executesql @SQL;

Note that in your example the column NAMES_MAINTAGS allows up to ~2 billion characters; object names cannot be this long. As a result these values will be truncated to 128 characters; if you therefore have duplicate names due to this, the CREATE statement will fail.

Thom A
  • 88,727
  • 11
  • 45
  • 75