Multiple Column Values in a Single Row

Changing multiple column values at once for a single row in my dataframe

You can use the new rows_update function written for such tasks.

library(dplyr)

row <- tibble(ID = 'C', State = 'StateB', Town = 'Town3', Street = 'StreetZ')
result <- rows_update(df, row, by = 'ID')

#  ID  State  Town  Street
#1  A StateA Town1 StreetX
#2  B StateA Town2 StreetY
#3  C StateB Town3 StreetZ
#4  D StateB Town4 StreetQ
#5  E StateC Town5 StreetK
#6  F StateC Town6 StreetN

How to convert multiple column values to a single column having multiple rows in Postgres

You can use a values clause to achieve the unpivot:

select t.name, c.category_mix, t.tag
from table1 t
   cross join lateral ( 
       values (category1), (category2), (category3)
   ) as c(category_mix);

Online example

Multiple column values in a single row

SELECT  ID,
        MAX(CASE WHEN status = 5 THEN Status ELSE NULL END) col1,
        MAX(CASE WHEN status = 6 THEN Status ELSE NULL END) col2,
        MAX(CASE WHEN status = 7 THEN Status ELSE NULL END) col3
FROM    tableNAME
GROUP   BY ID

• SQLFiddle Demo

using PIVOT

SELECT *
FROM   (
          SELECT ID, Status, CASE Status 
                              WHEN 5 THEN 'Col1'
                              WHEN 6 THEN 'Col2'
                              WHEN 7 THEN 'Col3'
                            END Stat
          FROM tableName
        ) src
        PIVOT
        (
          MAX(Status)
          FOR Stat IN ([Col1],[Col2],[Col3])
        ) pivotTbl

• SQLFiddle Demo

Convert Multiple columns into a single row with a variable amount of columns

Try something as below -

Input DataFrame

df = spark.createDataFrame([('bus1', '082...'), ('bus1', '087...'), ('bus2', '076...'), ('bus3', '081...'),('bus3', '084...'),('bus3', '086...')], schema=["Name", "Phone"])
df.show()

+----+------+
|Name| Phone|
+----+------+
|bus1|082...|
|bus1|087...|
|bus2|076...|
|bus3|081...|
|bus3|084...|
|bus3|086...|
+----+------+

Collecting all the Phone values into an array using collect_list

from pyspark.sql.functions import *
from pyspark.sql.types import *

df1 = df.groupBy("Name").agg(collect_list(col("Phone")).alias("Phone")).select( "Name", "Phone")
df1.show(truncate=False)

+----+------------------------+
|Name|Phone                   |
+----+------------------------+
|bus1|[082..., 087...]        |
|bus2|[076...]                |
|bus3|[081..., 084..., 086...]|
+----+------------------------+

Splitting Phone into multiple columns

df1.select(['Name'] + [df1.Phone[x].alias(f"Phone{x+1}") for x in range(0,3)]).show(truncate=False)

+----+------+------+------+
|Name|Phone1|Phone2|Phone3|
+----+------+------+------+
|bus1|082...|087...|null  |
|bus2|076...|null  |null  |
|bus3|081...|084...|086...|
+----+------+------+------+

Merge multiple columns values in one column in one row Oracle SQL

You don't need a listagg for this, you can just concat all the columns as follows -

select colnum, col1||','||col2||','||col3 as col1234
from columnMerger

COLNUM  COL1234
1       a,b,c

Pivot multiple rows (2 columns) into a single row

You can use pivot clause for that purpose, like below (Your table has only 2 columns and I assume you don't have any duplicate code)

select *
from Yourtable
pivot (
max(value) for code in (
        'AGE' as AGE
    ,   'FIRST_NAME' as FIRST_NAME
    ,   'LAST_NAME' as LAST_NAME
    ,   'STATUS' as STATUS
    )
)

---

Related Topics