I have a "small" issue with a PIVOT SQL script. I have based my current solution on a similar question here Dynamic PIVOT .
I have managed to write the PIVOT script and largely it is ok. However, my situation is this, instead of getting the unique entries onto single rows, the script outputs something like 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 the results I am looking for should look something like this
ListingEntryId Address Employees Location
1 Canal Street 3 Nottingham
2 Camden 12 London
Here's the script
DECLARE @listingId INT = 1;
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)
SELECT LEV.ListingColumnId, LEV.ListingEntryId, LE.CreatedBy, LEV.EntryValue, LD.ColumnTitle, LD.[Index]
INTO #ListingTable
FROM ListingEntryValue LEV LEFT OUTER JOIN
ListingEntry LE ON LEV.ListingEntryId=LE.Id
LEFT OUTER JOIN
ListingDefinition LD ON LEV.ListingColumnId = LD.Id
WHERE LE.ListingId = @listingId;
SELECT * FROM #ListingTable;
SELECT @cols = STUFF((SELECT DISTINCT TOP 100 PERCENT ',' + QUOTENAME(LT.ColumnTitle) FROM #ListingTable LT
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1,1,'')
SELECT @cols;
SET @query = 'SELECT ListingEntryId,' + @cols + ' FROM (SELECT ListingEntryId, ListingColumnId, EntryValue, ColumnTitle, [Index] FROM #ListingTable LT) x
PIVOT (MAX(EntryValue) FOR ColumnTitle IN (' + @cols + ')
) p'
PRINT @query
EXECUTE(@query);
DROP TABLE #ListingTable
I'll be grateful for some pointers that can help me sort this out. I've made a fiddle here which for some strange reason doesn't output anything but all the code to generate the schema is there
