CODEWITHSUNDEEP
mysqlsqlrunpivot
Tyler
Tyler

Reputation: 11

Convert Row Data into Single Column w/ Key Column

I would like to convert row data into a single column with a key column from the original row data using SQL or R.

Example Row:

---------------------------------------------------------------------
| Customer #  | Item1   |   Item2   |   Item3  |  Item4 |
---------------------------------------------------------------------
| 1111111     | A12345  |   C45567  |   G34589 | A34529 |
---------------------------------------------------------------------

This is my desired result:

   -------------------------------
   | Customer # | Columnname | Value    |
   -------------------------------
   | 1111111    | Item1      | A12345   |
   | 1111111    | Item2      | C45567   |
   | 1111111    | Item3      | G34589   |
   | 1111111    | Item4      | A34529   |

Upvotes: 1

Views: 204

Answers (2)

rjen
rjen

Reputation: 1972

In R, you can use dplyr and tidyr as follows.

library(dplyr)
library(tidyr)

data <- tibble(customer = c(11111),
               item1 = c('sfdhdshv'),
               item2 = c('dfh'),
               item3 = c('kjg'))

data

#   customer item1    item2 item3
#      <dbl> <chr>    <chr> <chr>
# 1    11111 sfdhdshv dfh   kjg 

data %>%
  pivot_longer(!customer, names_to = 'columnname', values_to = 'value')

#   customer columnname value   
#      <dbl> <chr>      <chr>   
# 1    11111 item1      sfdhdshv
# 2    11111 item2      dfh     
# 3    11111 item3      kjg

Upvotes: 1

GMB
GMB

Reputation: 222632

You want to unpivot the columns of your table to rows.

You tagged the question mysqli, so let me assume that you are using MySQL. In this database, you can use union all:

select customer, 'Item1' columname, Item1 value from mytable
union all select customer, 'Item2', Item2 from mytable
union all select customer, 'Item3', Item3 from mytable
union all select customer, 'Item4', Item4 from mytable

Other databases have neater solutions, typically using a lateral join and values(). In Postgres for example:

select t.customer, x.columname, x.value
from mytable t
cross join lateral (values 
    ('Item1', Item1), ('Item2', Item2), ('Item3', Item3), ('Item4', Item4)
) x(columname, value)

In SQL Server, you would just replace cross join lateral with cross apply.

Upvotes: 1

Related Questions