I have table where I need to compare two values based on their names in another column to see which one is bigger. Basically:
x
If **name1** -> value1
If **name2** -> value2
Compare **value1 > value2**
I have done summations but I cannot find a way to use the CASE WHEN
approach to this situation.
Advertisement
Answer
You can PIVOT
the data and have all values in current row. Then, compare them as you like:
SELECT [Id]
,[name1], [name2], [name3], [name4]
FROM [my_table]
PIVOT
(
MAX([value]) FOR [column] IN ([name1], [name2], [name3], [name4])
) PVT
or
SELECT [Id]
,MAX(CASE WHEN [column] = 'name1' THEN [value] END) AS [name1]
,MAX(CASE WHEN [column] = 'name2' THEN [value] END) AS [name2]
,MAX(CASE WHEN [column] = 'name3' THEN [value] END) AS [name3]
,MAX(CASE WHEN [column] = 'name4' THEN [value] END) AS [name4]
FROM [my_table]
GROUP [Id];