Styjun

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 |

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