Transpose Rows to Columns Based on Id Column

Transpose rows to columns based on ID column

you can use SQL Server pivot clause for this:

select
    p.*
from Table1
pivot(
    max([Field Selection])
    for [Field Name] in ([Rating 1], [Rating 2], [Rating 3])
) as p

or you can pivot manually:

select
    ID,
    max(case when [Field Name] = 'Rating 1' then [Field Selection] end) as [Rating 1], 
    max(case when [Field Name] = 'Rating 2' then [Field Selection] end) as [Rating 2],
    max(case when [Field Name] = 'Rating 3' then [Field Selection] end) as [Rating 3]
from Table1
group by ID

sql fiddle demo

Redshift SQL -- transposing rows by id into columns

You can use LISTAGG() to do this:

SELECT Id1, LISTAGG(Weight, '-') WITHIN GROUP (ORDER BY Id2)
FROM yourtable
GROUP BY Id1; 

SQL Server Transpose Rows into Columns using IDs as Column Names

You can use a dynamic sql query as below.

Query

declare @sql as varchar(max);

select @sql = 'select t1.[User_Id], ' + stuff((select +
    ', max(case t2.[Key_Id] when ' + cast([Key_Id] as varchar(100)) + 
    ' then t1.[Value] end) as [' + [Key] + '] '
    from Table2 
    for xml path('')
), 1, 2, '') + 
'from Table1 t1 left join Table2 t2 on t1.[Key_Id] = t2.[Key_Id] group by t1.[User_Id];'

exec(@sql);

Find a demo here

Reshape the dataframe using unique ID and transpose the row to column based on the corresponding value

We can concatenate the 'Parameter' and 'Unit' columns to a single column before doing the reshape to 'wide' format with pivot_wider

library(dplyr)
library(stringr)
library(tidyr)
df1 %>% 
   mutate(Parameter = sprintf('%s_value_unit(%s)', Parameter, Unit),
        .keep = "unused") %>%
   pivot_wider(names_from = Parameter, values_from = Value)

-output

# A tibble: 3 × 4
  SampleID `apple_value_unit(g)` `pear_value_unit(lb)` `orange_value_unit(kg)`
     <int>                 <int>                 <int>                   <int>
1        1                    30                    15                      20
2        2                     2                     3                      10
3        3                    15                    23                      12

data

df1 <- structure(list(SampleID = c(1L, 1L, 1L, 2L, 2L, 2L, 3L, 3L, 3L
), Parameter = c("apple", "pear", "orange", "apple", "pear", 
"orange", "apple", "pear", "orange"), Value = c(30L, 15L, 20L, 
2L, 3L, 10L, 15L, 23L, 12L), Unit = c("g", "lb", "kg", "g", "lb", 
"kg", "g", "lb", "kg")), class = "data.frame", row.names = c(NA, 
-9L))

python - transpose rows to columns, and calculate new column value as sum of another column

You can use .groupby() with .agg():

x = (
    df.groupby(["ID", "channels"])
    .agg({"INTR_COUNT": "sum", "PERSONA": "first"})
    .set_index("PERSONA", append=True)
    .unstack(level=1)
    .droplevel(0, axis=1)
    .fillna(0)
    .astype(int)
    .reset_index()
)
x.columns.name = None
print(x)

Prints:

library(dplyr)
library(tidyr)

df %>%
  mutate(index_row = cumsum(Event == 'Enter')) %>%
  pivot_wider(names_from = Event, values_from = Date) %>%
  select(-index_row)

#     ID Enter    Leave    
#  <dbl> <chr>    <chr>    
#1     1 1/1/2021 1/5/2021 
#2     2 5/4/2021 5/30/2021
#3     2 6/2/2021 6/5/2021