This post has already been read 242 times!

Create Tables
CREATE TABLE profiles
	([ProfileID] int, [PropertyDefinitionID] int, [PropertyValue] varchar(5))
;
	
INSERT INTO profiles
	([ProfileID], [PropertyDefinitionID], [PropertyValue])
VALUES
	(1, 6, 'Jone'),
	(1, 7, 'Smith'),
	(1, 8, 'Mr'),
	(1, 3, '50000')
;

CREATE TABLE propertydefinitions
	([PropertyDefinitionID] int, [PropertyName] varchar(9))
;
	
INSERT INTO propertydefinitions
	([PropertyDefinitionID], [PropertyName])
VALUES
	(6, 'FirstName'),
	(7, 'LastName'),
	(8, 'Prefix'),
	(3, 'Salary')
;
PIVOT TABLE
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(PropertyName) 
                    from propertydefinitions
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT profileid, ' + @cols + ' from 
             (
                select p.profileid,
                  p.propertyvalue,
                  d.propertyname
                from profiles p
                left join propertydefinitions d
                  on p.PropertyDefinitionID = d.PropertyDefinitionID
            ) x
            pivot 
            (
                max(propertyvalue)
                for propertyname in (' + @cols + ')
            ) p '

execute(@query)

code source : http://sqlfiddle.com/#!3/210df/2

Leave a Reply

Post Navigation