Convert One Row into Multiple Rows with Fewer Columns

Postgres: convert single row to multiple rows (unpivot)

A single SELECT with a LATERAL join to a VALUES expression does the job:

SELECT p.id, v.*
FROM   price_list p
     , LATERAL (
   VALUES
      ('type_a', p.price_type_a)
    , ('type_b', p.price_type_b)
    , ('type_c', p.price_type_c)
   ) v (price_type, price);

Related:

• Convert one row into multiple rows with fewer columns
• SELECT DISTINCT on multiple columns

Convert one column into multiple rows using python

Use GroupBy.cumcount for new column for counter columns created MultiIndex in df3:

df['g'] = df.groupby(['Student Id','Name']).cumcount().add(1)

df3 = (df.pivot_table(index=['Student Id','Name'], 
                     columns='g', 
                     values='Marks', 
                     aggfunc = 'max')
        .add_prefix('Marks')
        .rename_axis(None, axis=1)
        .reset_index())
print (df3)
  Student Id    Name  Marks1  Marks2  Marks3
0       id_1    John   112.0     NaN     NaN
1       id_2    Rafs   181.0   182.0   183.0
2       id_3    Juan   222.0   312.0     NaN
3       id_3  Roller    21.0     NaN     NaN

If need integers with missing values:

df['g'] = df.groupby(['Student Id','Name']).cumcount().add(1)

df3 = (df.pivot_table(index=['Student Id','Name'], 
                     columns='g', 
                     values='Marks', 
                     aggfunc = 'max')
        .add_prefix('Marks')
        .astype('Int64')
        .rename_axis(None, axis=1)
        .reset_index())
print (df3)
  Student Id    Name  Marks1  Marks2  Marks3
0       id_1    John     112    <NA>    <NA>
1       id_2    Rafs     181     182     183
2       id_3    Juan     222     312    <NA>
3       id_3  Roller      21    <NA>    <NA>

How to split a row into multiple rows based on number of certain columns with data

Dim shtIn As Worksheet, shtOut As Worksheet
Dim c As Range, c2 As Range

Set shtIn = ThisWorkbook.Sheets("Input")
Set shtOut = ThisWorkbook.Sheets("Output")

Set c = shtIn.Range("A2") 'start of your data
'do while "ID" not empty
Do While Len(c.Value) > 0
    Set c2 = c.EntireRow.Cells(1, "E") 'first "book time" for this row
    'do while "book time" not empty
    Do While Len(c2.Value) > 0
        With shtOut.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
            c.Resize(1, 4).Copy .Cells(1)
            c2.Resize(1, 2).Copy .Cells(1).Offset(0, 4)
        End With
        Set c2 = c2.Offset(0, 2) 'next book time
    Loop
    Set c = c.Offset(1, 0)
Loop

Convert multiple rows with columns in a single row in SQL Server

You can try this.

DECLARE @SqlText  NVARCHAR(MAX) = 'SELECT * FROM MyTable T1 '

SELECT @SqlText  = @SqlText  + ' INNER JOIN MyTable AS T'+ CONVERT(VARCHAR, sn) +' ON T'+CONVERT(VARCHAR, sn) +'.sn =  ' + CONVERT(VARCHAR, sn) 
FROM MYTable WHERE sn > 1
order by sn 

SET @SqlText  = @SqlText  + ' WHERE T1.sn = 1'

EXEC ( @SqlText )

ıt generates this query as result.

SELECT * FROM MyTable T1  
     INNER JOIN MyTable AS T2 ON T2.sn =  2 
     INNER JOIN MyTable AS T3 ON T3.sn =  3
 WHERE T1.sn = 1

Result:

sn          Name       city       cntry      conti      sn          Name       city       cntry      conti      sn          Name       city       cntry      conti
----------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------
1           abc        NYC        USA        NA         2           def        LON        UK         EU         3           xyz        DUB        UAE        ASIA

---

Related Topics