I am using SQL Server 2014 and I have the following Table (T1):
ID HOTEL ADDRESS COORDINATES DESCRIPTION VALUE
A001 A Royal Road 20.5 GP FA MARKINGS 2
A001 A Royal Road 20.5 GP FA COMMENT All Good
A001 B Royal Road 20.5 AB TC MARKINGS 3
A001 B Royal Road 20.5 AB TC COMMENT Check Staff List
I need to convert this table so that I get the output below:
ID HOTEL ADDRESS COORDINATES DESCRIPTION MARKINGS COMMENT
A001 A Royal Road 20.5 GP FA 2 All Good
A001 B Royal Road 20.5 AB TC 3 Check Staff List
I tried the unpivot function but it did not work. I did a search on the internet and here on Stackoverflow for a similar problem but I could not anything closely related to my problem.
Any help would be much appreciated.
I guess this will give the correct output on the provided data. However the way to group by might not be correct depending on the rest of the rows, and the performance might be bad, if it is a large table.
SELECT ID, HOTEL, ADDRESS, COORDINATES, LEFT(DESCRIPTION, 5) AS DESCRIPTION,
MAX(CASE WHEN DESCRIPTION LIKE '%MARKINGS' THEN VALUE END) AS MARKINGS,
MAX(CASE WHEN DESCRIPTION LIKE '%COMMENT' THEN VALUE END) AS COMMENT
GROUP BY ID, HOTEL, ADDRESS, COORDINATES, LEFT(DESCRIPTION, 5)