Sql-server – Rows to EAV structure with row identifier

sql servert-sql

Would you help me with this, please?

Imagine any SQL table or query results like…

+-----------+----------+-----+
| FirstName | LastName | Age |
+-----------+----------+-----+
| Peter     | Whatever |  31 |
| George    | McDonald |  55 |
| ...                        |
+-----------+----------+-----+

And I want to transform each row to EAV structure (column name, column value) and add a unique row identifier, like…

+--------------------------------------+-----------+----------+
|                 GUID                 |  ColName  | ColValue |
+--------------------------------------+-----------+----------+
| 8db43ebb-a4c2-4a0c-8113-f3ceb930712a | FirstName | Peter    |
| 8db43ebb-a4c2-4a0c-8113-f3ceb930712a | LastName  | Whatever |
| 8db43ebb-a4c2-4a0c-8113-f3ceb930712a | Age       | 31       |
| 7a6bc8eb-2261-4c8e-a42b-59b06aee8818 | FirstName | George   |
| 7a6bc8eb-2261-4c8e-a42b-59b06aee8818 | LastName  | McDonald |
| 7a6bc8eb-2261-4c8e-a42b-59b06aee8818 | Age       | 55       |
| ...                                                         |
+--------------------------------------+-----------+----------+

Preferably using FOR XML PATH clause (more tables with a different structure to transform like this).

Script for creating test table goes here:

DECLARE @MyTable TABLE (
    FirstName NVARCHAR(100)
    ,LastName NVARCHAR(100)
    ,Age INT)
INSERT INTO @MyTable (FirstName,LastName,Age) 
VALUES(N'Peter',N'Whatever',31),(N'George',N'McDonald',55);

I know the solution without GUID so the row-identifier is the point of my question:

SELECT T2.N.value('local-name(.)', 'nvarchar(128)') as ColName,
       T2.N.value('text()[1]', 'nvarchar(max)') as ColValue
FROM (SELECT *
      FROM @MyTable
      FOR XML PATH(''), TYPE) as T1(X)
CROSS APPLY T1.X.nodes('/*') as T2(N)

Best Answer

You do need to include a GUID higher in the query.

This looks like it might do the job:

SELECT N.value('../@GUID', 'nvarchar(max)') as c1,
       N.value('local-name(.)', 'nvarchar(max)') as ColName,
       T2.N.value('text()[1]', 'nvarchar(max)') as ColValue
FROM (SELECT NEWID() as "@GUID", * 
      FROM @MyTable
      FOR XML PATH('T'), TYPE) as T1(X)
CROSS APPLY T1.X.nodes('/T/*') as T2(N)
Related Question