-2

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.

user3115933
  • 4,303
  • 15
  • 54
  • 94
  • a table is by nature unsorted so sql will never know which row belong to which row – nbk Jan 31 '23 at 12:53
  • May be there is an intermediate step that I need to do before pivoting or unpivoting the result? – user3115933 Jan 31 '23 at 12:54
  • the basic data is flawed, so there are no steps to be taken, if there is a second GP FA MARKINGS no algorithm can determine which coment belogs to which, so you need a column that joins both rows – nbk Jan 31 '23 at 12:58
  • I have added a column [Hotel] to the data. – user3115933 Jan 31 '23 at 13:06
  • that has the wsame problem when there are two hptel A with GP FA MARKINGS so how will an algorithm determine which belongs to which the additional row has to be **unique** – nbk Jan 31 '23 at 13:23
  • So may be a date column will do the trick as there will not be 2 GP FA MARKINGS on the same date for the same Hotel or a unique ID column for each Row. – user3115933 Jan 31 '23 at 13:30

1 Answers1

0

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)
P. Olesen
  • 286
  • 3
  • 4
  • that has the same flaw as described already in the comments – nbk Jan 31 '23 at 13:25
  • @nbk You mean the flaw that there might be 2 rows for hotel A with GP FA MARKINGS in the description? In that case I agree that the solution is not working (unless the VALUE is the same for both rows) I have assumed that there are no such situation. I have also assumed that the 5 leftmost characters in the DESCRIPTION is what there should be grouped by. That could be wrong. There are more assumption in the solution. But based on the data seen I think the query will solve the issue. – P. Olesen Feb 01 '23 at 14:04