sql - TSQL Dynamic Pivot -

i have "small" issue pivot sql script. have based current solution on similar question here dynamic pivot .

i have managed write pivot script , largely ok. however, situation this, instead of getting unique entries onto single rows, script outputs this

listingentryid   address        employees   location 1                null           null        nottingham 1                canal street   null        null 1                null           3           null 2                null           null        london 2                camden         null        null 2                null           12          null 

whereas results looking should

listingentryid   address        employees   location 1                canal street   3           nottingham 2                camden         12          london 

here's script

declare @listingid int = 1; declare @cols nvarchar(max), @query nvarchar(max)  select lev.listingcolumnid, lev.listingentryid, le.createdby, lev.entryvalue, ld.columntitle, ld.[index] #listingtable listingentryvalue lev left outer join  listingentry le on lev.listingentryid=le.id left outer join  listingdefinition ld on lev.listingcolumnid = ld.id le.listingid = @listingid;  select * #listingtable;  select @cols = stuff((select distinct top 100 percent ',' + quotename(lt.columntitle) #listingtable lt xml path(''), type).value('.', 'nvarchar(max)'), 1,1,'') select @cols;  set @query = 'select listingentryid,' + @cols + ' (select listingentryid, listingcolumnid, entryvalue, columntitle, [index] #listingtable lt) x pivot (max(entryvalue) columntitle in (' + @cols + ') ) p'  print @query execute(@query);   drop table #listingtable 

i'll grateful pointers can me sort out. i've made fiddle here strange reason doesn't output code generate schema there

limit fields in pivot required elements (x, y, value)

set @query = 'select listingentryid,' + @cols + ' (select listingentryid, entryvalue, columntitle #listingtable lt) x pivot (max(entryvalue) columntitle in (' + @cols + ') ) p' 

returns

listingentryid  address         employees   location 1               canal street    3           nottingham 2               camden          12          london