Oracle Database SQL - Turn Column into a row - String values only

ID (PrimaryKey)	HumanAttribut	attributValue
1	Name	John
1	Country	USA

I want to turn a column into a row

like this:

ID (PrimaryKey)	Name	Country
1	John	USA

SELECT ID, (*pink*) [Name], [Country]
FROM
(SELECT ID, HumanAttribut as Ahuman, attributValue
FROM tableA
WHERE ID = 1
AND HumanAttribut IN ('Name', 'Country')) as SourceTabele
PIVOT
(Max(attributeValue)
For
Ahuman in ([Name], [Country])
) as PIVOT_TABLE

I get the error missing expression. Oracle shows me with pink that there is an error.

pink error

If someone already posted something similar like this, with easy values like name or country then pls link it to me.

Thanks for the help in advance!

I followed this video https://www.youtube.com/watch?v=uT_Z2VP2Z24, but I dont know why my syntax is wrong, I know that something is missing through the error message, but I dont know what I forgot...

CodePudding user response：

I guess, Oracle does not like HumanAttribut as Ahuman, please try HumanAttribut Ahuman

You can use not only PIVOT-operator, but the other approach (hope, it will help)

WITH CTE(ID,HumanAttribut,attributValue)
AS
(
   SELECT 1,    'Name',     'John' FROM DUAL UNION ALL
   SELECT 1,    'Country',  'USA' FROM DUAL
)
SELECT C.ID,
 MAX
  (
      CASE
        WHEN C.HumanAttribut='Name'THEN C.attributValue
        ELSE ''
      END
  )NAME,
  MAX
  (
      CASE
       WHEN C.HumanAttribut='Country'THEN C.attributValue
       ELSE ''
     END
  )COUNTRY
  FROM CTE C
 GROUP BY C.ID

https://dbfiddle.uk/AKl-2nK7

CodePudding user response：

Below is some examples that can help you

SELECT *
FROM   your_table
PIVOT  (MAX(column_name)
       FOR new_row_name IN ('value1', 'value2', 'value3'))

You can also use the UNPIVOT operator to convert rows into columns.

SELECT *
FROM   your_table
UNPIVOT (column_name FOR new_row_name IN (value1, value2, value3))