Getting values from multiple rows into a single row

I want to get the values of multiple rows of a single column to different column in a single row based on the condition of another column.

I want to get the values of the field_value_name in same row but different columns based on values present in the field_name column as they belong to the same id.

How to get this through sql or pyspark?

I tried using CASE WHEN but it will scan for every row and return the output for every row. [

I'd rather want those values to be in a single row for every id.

You need pivoting logic here, e.g.

<!-- language: sql -->

SELECT
    parent_id,
    MAX(CASE WHEN properties_field_name = 'Status'
             THEN properties_field_value_name END) AS REQ_STATUS,
    MAX(CASE WHEN properties_field_name = 'Type'
             THEN properties_field_value_name END) AS REQ_TYPE,
    MAX(CASE WHEN properties_field_name = 'Description'
             THEN properties_field_value_name END) AS REQ_DESC
FROM yourTable
GROUP BY parent_id
ORDER BY parent_id;

You can do it with MAX like Tim shows or you can do it with joins like this:

SELECT
  parent_id,
  status.properties_field_value_name as status,
  type.properties_field_value_name as type,
  desc.properties_field_value_name as desc
FROM (
  SELECT distinct partent_id 
  FROM thetableyoudidnotname
) as base
LEFT JOIN thetableyoudidnotname as status on base.parent_id = status.parent_id and status.properties_field_name = 'Status'
LEFT JOIN thetableyoudidnotname as type on base.parent_id = type.parent_id and type.properties_field_name = 'Type'
LEFT JOIN thetableyoudidnotname as desc on base.parent_id = desc.parent_id and desc.properties_field_name = 'Description'

Simple pivot problem I think.

data = [
    [7024549, 'Status', 'Approved'],
    [7024549, 'Type', 'Jama Design'],
    [7024549, 'Description', 'null']
]

df = spark.createDataFrame(data, ['parent_id', 'properties_field_name', 'properties_field_value_name'])

df.withColumn('id', f.expr('uuid()')) \
  .withColumn('properties_field_name', f.concat(f.lit('REQ_'), f.upper(f.col('properties_field_name')))) \
  .groupBy('id', 'parent_id') \
  .pivot('properties_field_name') \
  .agg(f.first('properties_field_value_name')) \
  .drop('id') \
  .show()

+---------+---------------+----------+-----------+
|parent_id|REQ_DESCRIPTION|REQ_STATUS|   REQ_TYPE|
+---------+---------------+----------+-----------+
|  7024549|           null|  Approved|       null|
|  7024549|           null|      null|Jama Design|
|  7024549|           null|      null|       null|
+---------+---------------+----------+-----------+