Dynamic table display

Question

We have our source table from other users have different number of columns each time. for e.g, today the table reads:

Name   Eye   tail
-------------------
Dog     Blue  long
Cat     Red   short

but tomorrow is:

Name   eye   tail skin
-------------------
Dog    blue  long  white
Lion   green short  brown

and our object is to transfer data into such a fixed schema table:

name   property    value
-------------------------
Dog    Eye         blue
Dog    tail        long
Dog    skin        white
Lion   Eye         green
Lion   Tail        short
Lion   skin        brown  

To achieve this, now we are using a semi-manual modified stored procedure to make such conversion:

SELECT * 
INTO #T 
FROM table.original

INSERT INTO table.target(name, property, value)
    SELECT name, property, value 
    FROM
        --below is manually modified each time
        (SELECT name = a.name, property = 'eye', value = a.eye 
         FROM #T a
         UNION ALL
         SELECT name, 'tail', tail 
         FROM #T
         UNION ALL
         SELECT name, 'skin', skin 
         FROM #T
         UNION ALL
         .........
       )

Is there any way to automatically make such conversion? I think FOR XML might solve this, but how?

Answer 1

Looks like great candidate for dynamic UNPIVOT:

DECLARE @columns NVARCHAR(MAX);

SELECT @columns = STUFF ((SELECT ',' + QUOTENAME(COLUMN_NAME)
                          FROM INFORMATION_SCHEMA.COLUMNS
                          WHERE [table_name] = 'tab'
                            AND column_name <> 'Name'
                          FOR XML PATH ('')),1,1,'');

DECLARE @sql NVARCHAR(MAX) = 
N'INSERT INTO target(name, property, [value])
SELECT Name, property, [value]
FROM tab t
UNPIVOT
(
  [value] FOR property IN (<placeholder>)
) unpvt;';

SET @sql = REPLACE(@sql, '<placeholder>', @columns);  

EXEC dbo.sp_executesql
       @sql;

SELECT *
FROM target;

SqlFiddleDemo SqlFiddleDemo2

Output:

╔═══════╦═══════════╦═══════╗
║ name  ║ property  ║ value ║
╠═══════╬═══════════╬═══════╣
║ Dog   ║ eye       ║ blue  ║
║ Dog   ║ tail      ║ long  ║
║ Dog   ║ skin      ║ white ║
║ Lion  ║ eye       ║ green ║
║ Lion  ║ tail      ║ short ║
║ Lion  ║ skin      ║ brown ║
╚═══════╩═══════════╩═══════╝

How it works:

1. Get columns list from metadata for input table and concatenate them [eye],[tail],[skin]

2. Prepare main query with placeholder for columns

3. Replace placeholder with actual column names

4. Execute dynamic-SQL

5. Check EAV table