Convert Data with One Column and Multiple Rows into Multi Column Multi Row Data

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 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>

convert one row/n-columns to multi-rows/3-columns python-pandas

with open('~\datatest\datatest2.csv', 'r') as f:
    v = f.readline().split(',')

with open('~\datatest\out.csv', 'w') as f:
    for n in range(len(v)//3):
        f.write(','.join(v[n*3:(n+1)*3]) + '\n')

Group by column and multiple Rows into One Row multiple columns

As I mention in the comments, what you need here is a PIVOT or Cross tab; I prefer the latter so what I am going to use.

The non-dynamic solution to this would be as follows:

WITH RNs AS(
    SELECT WorkOrder,
           TestType,
           Result,
           ROW_NUMBER() OVER (PARTITION BY WorkOrder, TestType ORDER BY (SELECT NULL)) AS RN --ORDER BY should be your ID/always ascending column
    FROM dbo.Result)
SELECT WorkOrder,
       TestType,
       MAX(CASE RN WHEN 1 THEN Result END) AS Result1,
       MAX(CASE RN WHEN 2 THEN Result END) AS Result2,
       MAX(CASE RN WHEN 3 THEN Result END) AS Result3
FROM RNs R
GROUP BY WorkOrder,
         TestType;

The problem, however, is that this "locks" you into 3 results, but you suggest there is an indeterminate number of results. Therefore you need a dynamic solution.

The below will work up to 100 results. if you do need more columns than than, then add more CROSS JOINs to N in the CTE Tally. This results is something like this (which is quite messy).

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10),
        @MaxTally int;

SELECT @MaxTally = MAX(C)
FROM (SELECT COUNT(*) AS C
      FROM dbo.Result
      GROUP BY WorkOrder,
               TestType) R;

WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
    SELECT TOP (@MaxTally) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS I
    FROM N N1, N N2) --100 rows, add more Ns for more rows
SELECT @SQL = N'WITH RNs AS(' + @CRLF +
              N'    SELECT WorkOrder,' + @CRLF +
              N'           TestType,' + @CRLF +
              N'           Result,' + @CRLF +
              N'           ROW_NUMBER() OVER (PARTITION BY WorkOrder, TestType ORDER BY (SELECT NULL)) AS RN --ORDER BY should be your ID/always ascending column' + @CRLF +
              N'    FROM dbo.Result)' + @CRLF +
              N'SELECT WorkOrder,' + @CRLF +
              N'       TestType,' + @CRLF +
              --Using FOR XML PATH due to not knowing SQL Server version
              STUFF((SELECT N',' + @CRLF +
                            CONCAT(N'       MAX(CASE RN WHEN ',T.I,N' THEN Result END) AS Result',T.I)
                     FROM Tally T
                     ORDER BY T.I ASC
                     FOR XML PATH(N''),TYPE).value('(./text())[1]','nvarchar(MAX)'),1,3,N'') + @CRLF +
              N'FROM RNs R' + @CRLF +
              N'GROUP BY WorkOrder,' + @CRLF +
              N'         TestType;';

PRINT @SQL; --Your best friend.

EXEC sys.sp_executesql @SQL;

Convert multiple rows into one row with multiple columns

You can use CASE statements for simple queries.

Or use the crosstab() function of the tablefunc module for more complex cases and better performance.

You can find examples for both cases under this related question:

PostgreSQL Crosstab Query

Converting multiple rows into single row with multiple columns

One method i conditional aggregation:

select card_id,
       max(iif(role = 'Reconciler', col, NULL)) as reconciler_col,
       max(iif(role = 'Approver', col, NULL)) as approver_col,
       max(iif(role = 'Cardholder', col, NULL)) as cardholder_col
from t
group by card_id;

col is a column that you want to pivot. You can add more than one column, just by adding more max(iif . . .) to the select.

---

Related Topics