0

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)
1
  • can you add the GUID to the MyTable first ?
    – Squirrel
    Feb 11, 2020 at 1:35

1 Answer 1

2

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)
1
  • Works great! Thank you!
    – jericzech
    Feb 11, 2020 at 13:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.