[Solved] T-SQL query on dynamic field with pivot

by


Maybe something like this:

Test data:

CREATE TABLE #PhoneBook(ID INT,Name VARCHAR(100))
INSERT INTO #PhoneBook VALUES(1,'Reza'),(2,'Ali')

CREATE TABLE #DynamicField(ID INT,Caption VARCHAR(100))
INSERT INTO #DynamicField VALUES(1,'Job'),(2,'Level')

CREATE TABLE #PhoneBook_DynamicField_Rel(ID INT,PhoneBookID INT,
                                         DynamicFieldID INT,Value VARCHAR(100))
INSERT INTO #PhoneBook_DynamicField_Rel
VALUES(1,1,1,'Emp'),(2,1,2,'1'),(3,2,1,'SomeJob')

Getting the colums

DECLARE @cols VARCHAR(MAX)
SELECT @cols=STUFF
(
    (
        SELECT 
            ',' +QUOTENAME(tbl.Caption)
        FROM
            #DynamicField AS tbl
        FOR XML PATH('')
    )
,1,1,'')

Then the query like this:

DECLARE @query NVARCHAR(4000)=
N'SELECT
    *
FROM
(
    SELECT
        *
    FROM
    (
        SELECT
            PhoneBook.ID,
            PhoneBook.Name,
            field.Caption,
            rel.Value
        FROM
            #PhoneBook AS PhoneBook
            JOIN #PhoneBook_DynamicField_Rel AS rel
                ON PhoneBook.ID = rel.PhoneBookID
            JOIN #DynamicField AS field
                ON rel.DynamicFieldID=field.ID
    ) AS SourceTable
) AS p
PIVOT
(
    MAX(Value) FOR Caption IN('+@cols+')
) AS pvt'
EXECUTE(@query)

Then in my case I will drop the temp tables:

DROP TABLE #PhoneBook
DROP TABLE #DynamicField
DROP TABLE #PhoneBook_DynamicField_Rel

0

solved T-SQL query on dynamic field with pivot