SQL table transformation

Question

I have two table:

1) Saving input field name and id

2) Table saving that data was wrote in field.

In example table (2) saving two record. I want transformation into 3) table. But I want to do it dynamically, because in future I am add more field name?

1) 
+-------------------+-----------------+
| UserDefinedFieldId| FieldTitle      |
+-------------------+-----------------+
| 266               | Change at       |
| 267               | User name       |
| 268               | Message         |
+-------------------+-----------------+
2)
+-------------------+-----------------+-------------------+
| UserDefinedFieldId| UserDefinedRowId|FieldValue         |
+-------------------+-----------------+-------------------+
| 266               | 1               |2014-09-01         |
| 266               | 2               |2014-09-02         |
| 267               | 1               |my name            |
| 267               | 2               |another name       |
| 268               | 1               |My message         |
| 268               | 2               |Another message    |
+-------------------+-----------------+-------------------+
3)
+-------------------+-----------------+-------------------+
| Change at         | User name       |Message            |
+-------------------+-----------------+-------------------+
| 2014-09-01        | my name         |My message         |
| 2014-09-04        | another name    |Another message    |
+-------------------+-----------------+-------------------+

Answer 1

You do this by using dynamic sql to build set of columns/values to use with pivot. If the tables are names Table1 and Table2 this query should do what you want:

DECLARE @SQL AS NVARCHAR(MAX)
DECLARE @Cols AS NVARCHAR(MAX)
DECLARE @Values AS NVARCHAR(MAX)

SELECT 
    @Cols = ISNULL(@Cols + ',','') + QUOTENAME(FieldTitle),
    @Values= ISNULL(@Values + ',','') 
    + CHAR(13) + CHAR(9) 
    + 'MAX(' + QUOTENAME(FieldTitle) + ') AS '+ QUOTENAME(FieldTitle)
FROM (SELECT DISTINCT FieldTitle FROM Table1) AS Source

SET @SQL = N'
SELECT 
    UserDefinedRowId, ' 
    + @Values +' 
FROM (
    SELECT 
        t2.UserDefinedRowId, 
        t1.UserDefinedFieldId, 
        t2.FieldValue, 
        t1.FieldTitle 
    FROM Table1 t1
    INNER JOIN Table2 t2 ON t1.UserDefinedFieldId = t2.UserDefinedFieldId
    ) a
PIVOT(
    MAX(FieldValue)
    FOR FieldTitle IN (' + @Cols + ')
) AS [Pivot] 
GROUP BY UserDefinedRowId'

--PRINT @SQL -- you can use this line to print the generated sql statement.
EXEC (@SQL)

I did include theUserDefinedRowIdcolumn to use for grouping, if you don't want it, just remove it.

Sample SQL Fiddle.

Another SQL Fiddle allowing for date range selection.