Search code examples
sql-servert-sqlpivotunpivot

How to pivot or unpivot this table to achieve this specific output?


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.


Solution

  • 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)