Create table using data rows from select as columns on it

Question

I need to create a table using the rows of another table as the column names. The reason is that my database is not a relational one, so, I have in each case a table with the data, and another with the corresponding metadata.

Example:

Table 1:

Person
ID | Info
===================================================
1  | <John Smith><1st Avenue><Miami,Florida><33101>
2  | <Mary Walton><83th Street><New York, NY><1001>

Table 2: 

Person_Desc
Field   | Info
===================================================
ID      | Sequential identifier
Name    | Persons full name
Address | Physical location detail
City    | City
ZIP_C   | Postal office code

I would like to create a stored procedure that receives those two table names as parameter and the creates a third table, like this (bear with me and pseudocode please):

CREATE STORED PROCEDURE sp_relationalTable 
@dataTable nvarchar(50), 
@metadataTable nvarchar(50) , 
@TmpTable nvarchar(50)

    AS

    SELECT * FROM @metadataTable 

    CREATE TABLE @TmpData 
( @metadataTable_Field1 nvarchar(100), 
 ,@metadataTable_Field2 nvarchar(100),
 ,@metadataTable_Field3 nvarchar(100)....
)

    END

Thats the first part. Then I would run a SELECT statement against Table1: Person, breaking the data by a known delimiter, and INSERT all data into the newly created table.

INSERT INTO @TmpData (SELECT * FROM @dataTable)

Ideally, it could be run all into one SP as I said at the beginning, so, when you would run such SP it would be like:

EXEC sp_relationalTable Person, Person_Desc, RPerson

And I would end up with:

Table 3: 

RPerson
ID | Name        | Address       | City          | ZIP_C   |
============================================================
1  | John Smith  | 1st Avenue    | Miami,Florida |33101    |
2  | Mary Walton | 83th Street   | New York, NY  |1001     |

What does "my database is not a relational one" mean, if youre using SQLServer? — Caius Jard, Mar 25 '19 at 20:35
Greetings. Beside the fact that my RDBMS is MSSQL, as you can see in the table examples given, my data does not follow a relational model, as in, there are no foreign keys, and the data is only stored in 2 columns, using another table just to hold my metadata. — Me_, Mar 25 '19 at 20:39
Hi in your table2 you doesn’t have fkey to link thoses with table1 ? Other you have to take look at pivot, dynamic sql and exec — Sanpas, Mar 26 '19 at 06:27
I hesitate to call it a duplicate, because you're really asking two questions, but here's the answer to one of them: https://stackoverflow.com/questions/46217397/split-column-with-delimiter-into-multiple-columns This isn't a PIVOT by the way, so don't bother researching that, but you DO need to use `Dynamic SQL` to get the column names of your results from the second table. — Tab Alleman, Mar 26 '19 at 14:16

score 0 · Answer 1 · answered Mar 26 '19 at 07:13

May be this might help you. change the code as per your table names.

DECLARE @TABLE TABLE (
    ID INT IDENTITY(1, 1)
    ,Info VARCHAR(MAX)
    )

INSERT INTO @TABLE
VALUES ('<John Smith><1st Avenue><Miami,Florida><33101>')

INSERT INTO @TABLE
VALUES ('<Mary Walton><83th Street><New York, NY><1001>')

SELECT ID
    ,MAX(CASE 
            WHEN RNO = 1
                THEN INFO
            ELSE ''
            END) AS [PERSON]
    ,MAX(CASE 
            WHEN RNO = 2
                THEN INFO
            ELSE ''
            END) AS [Address]
    ,MAX(CASE 
            WHEN RNO = 3
                THEN INFO
            ELSE ''
            END) AS [City]
    ,MAX(CASE 
            WHEN RNO = 4
                THEN INFO
            ELSE ''
            END) AS [ZIP_C]
FROM (
    SELECT ID
        ,REPLACE(VALUE, '<', '') INFO
        ,ROW_NUMBER() OVER (
            PARTITION BY ID ORDER BY ID
            ) RNO
    FROM @TABLE
    CROSS APPLY string_split(Info, '>')
    WHERE VALUE <> ''
    ) A
GROUP BY ID

Create table using data rows from select as columns on it

1 Answers1