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

Question

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...

Does this answer your question? [Oracle SQL pivot query](https://stackoverflow.com/questions/4841718/oracle-sql-pivot-query) — astentx, Jan 23 '23 at 10:48
Oracle doesn't use brackets for quoted identifiers, so `[Name]` is an invalid identifier — astentx, Jan 23 '23 at 10:49
And `pivot` clause goes before the `where` clause. See [`SELECT`](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html#GUID-CFA006CA-6FF1-4972-821E-6996142A51C6) statement: `pivot` is part of `table_reference`, which is placed before `where_clause` — astentx, Jan 23 '23 at 10:51
would this be possible? Here I found a website with WHERE, but before PIVOT or is this an error? https://www.techonthenet.com/oracle/pivot.php — Bob der Bauer, Jan 23 '23 at 12:51
Where clause in the above link is applied inside a subquery. But I've missed the count of brackets in your code, it is okay in this part. Just remove square brackets and it will be fine — astentx, Jan 23 '23 at 13:32

score 0 · Answer 1 · answered Jan 23 '23 at 09:24

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

Have to solve this with PIVOT sadly, but nevertheless thanks — Bob der Bauer, Jan 23 '23 at 12:09

score 0 · Answer 2 · answered Jan 23 '23 at 10:25

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

score 0 · Accepted Answer · answered Jan 31 '23 at 09:05

0

wrong Syntax, use Oracle db
syntax railroad diagramm error
syntax error

I fixed it.

answered Jan 31 '23 at 09:05

Bob der Bauer

1
2

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

3 Answers3