Convert Data from Many Rows to Many Columns

Convert data from many rows to many columns

You want to use the reshape() function.

reshape(data, idvar="State", timevar="Year", direction="wide")

Convert multiple rows into one row with multiple columns in T-SQL

Bracket [] your column names

Example or dbFiddle

SELECT *
FROM
(
  SELECT  CompanyName, Price, Project
  FROM dbo.MyTable
) src
PIVOT
(
  SUM(Price)
  FOR Project IN ([Cleaning], [Vaccuming], [Painting], [VendingMachines], [Wallpapers], [Carpeting])
) piv;

Convert multiple rows to multiple columns (long to wide format does not work (?))

You can first keep only unique rows using distinct, group_by WoS_No to create an unique identifier column and get data in wide format.

library(dplyr)

df %>%
  distinct(WoS_No, Author, .keep_all = TRUE) %>%
  group_by(WoS_No) %>%
  mutate(row = row_number()) %>%
  tidyr::pivot_wider(names_from = row, values_from = c(Author, Faculty))

#  WoS_No               CNCI Author_1        Author_2   Faculty_1 Faculty_2
#  <chr>               <dbl> <chr>           <chr>      <chr>     <chr>    
#1 WOS:000352315900021 10.5  CHENEVIX, Georg DOLCE, Ric Medicine  HASS     
#2 WOS:000362644700013 37.5  CLOUST, A       STEVEN, A  HABS      Medicine 
#3 WOS:000382460200025  0.84 WANG, Zhi       NA         Medicine  NA       
#4 WOS:000381736200014  8.05 COIN, L         NA         IMB       NA       
#5 WOS:000371540200019 29.4  BARL, Kare      NA         NA        NA 

Note that, I also transformed Faculty into different columns. If you want to keep them in one column as shown in your expected output you can do it with minimal change in the code.

Convert multiple rows for one column into multiple columns (on the fly)

I would do this using conditional aggregation, but the key is row_number():

SELECT patientId,
       MAX(CASE WHEN seqnum = 1 THEN DiagnosisCode END) as DiagnosisCode_1,
       MAX(CASE WHEN seqnum = 2 THEN DiagnosisCode END) as DiagnosisCode_2,
       MAX(CASE WHEN seqnum = 3 THEN DiagnosisCode END) as DiagnosisCode_3,
       MAX(CASE WHEN seqnum = 4 THEN DiagnosisCode END) as DiagnosisCode_4
FROM (SELECT p.patientId, d.DiagnosisCode,
             ROW_NUMBER() OVER (PARTITION BY p.patientId ORDER BY d.DiagnosisCode) as seqnum
      FROM Patient p JOIN
           Course c 
           ON p.PatientSer = c.CourseSer JOIN
           CourseDiagnosis cd 
           ON cd.CourseSer = c.CourseSer JOIN
           Diagnosis d
           ON cd.DiagnosisSer = d.DiagnosisSer
     ) d
GROUP BY patientId;

---

Related Topics