Dynamic "INSERT INTO" Query with User Defined Type

Question

I have got a SQL-Table were eache line consists a singel Value of some kind of Virtuel-Tabel - means the real existig SQL-Table looks like this:

-----------------------------------------
|DataRecordset | DataField | DataValue  |
-----------------------------------------
| 1            | Firstname | John       |
| 1            | Lastname  | Smith      |
| 1            | Birthday  | 18.12.1963 |
| 2            | Firstname | Jane       |
| 2            | Lastname  | Smith      |
| 2            | Birthday  | 14.06.1975 |
-----------------------------------------

and I need to get something that feels like this:

-------------------------------------
| Firstname | Lastname | Birthday   |
-------------------------------------
| John      | Smith    | 18.12.1963 |
| Jane      | Smith    | 14.06.1975 |
-------------------------------------

the reason why the real existing SQL-Table is stored like the first one is, that there are a lot more information around the core-data... like who write the data... when was the data written... from which to which time was the data significant... so there are a lot of diffrent variabels which decides which line from the first table i use to generate the second one.

I created a User-Defined-Tabletype on the SQL-Server which looks like the second table.

Then i start writing a procedure...

DECLARE @secondTable secondTable_Typ

DECLARE firstTable_Cursor CURSOR FOR SELECT DataRecordset, ... WHERE...lot of Text
OPEN firstTable_Cursor

FETCH NEXT FROM firstTable_Cursor
INTO @DataRecordset, @...

WHILE @@FETCH_STATUS = 0
BEGIN

  IF NOT EXISTS(SELECT * FROM @secondTable WHERE DataRecordset= @DataRecordset)
  BEGIN

the Problem i have... now i need some kind of dynamic Query, because i want do something like this:

INSERT INTO @secondTable (DataRecordset, @DataField ) VALUES (@DataRecordset, @DataValue)

but i cant use the variable @DataField like this... so i used google and found the function sp_executesql... i wrote the following code:

SET @sqlString = 'INSERT INTO @xsecondTable  (DataRecordset, ' + @DataField + ') VALUES (@xDataRecordset, @xDataValue)'
EXEC sp_executesql @sqlString, N'@xsecondTable secondTable_Typ, @xDataRecordset smallint, @xDataValue sql_variant', @secondTable , @DataRecordset, @DataValue

but when i run the procedure i got an error that means i have to add a parameter "READONLY" to "@xsecondTable"...

i think the problem is, that sp_executesql can use variables as input or as outup... but i am not shure if its possiple to get this user defined table type into this procedure...

someone any idea how to get this code to run?

thank you very much

Answer 1

Have you considered doing a PIVOT on the data? Something along the lines of:

SELECT
    [Firstname]
  , [Lastname]
  , [Birthday]
FROM
    (
        SELECT
            [DataRecordset]
          , [DataField]
          , [DataValue]
        FROM [Table]
    ) DATA
    PIVOT
    (
        MIN ([DataValue]) FOR [DataField] IN
        (
            [Firstname]
          , [Lastname]
          , [Birthday]
        )
    ) PVT