Convert Row to Column

Python Pandas: Convert Rows as Column headers

You're looking for pivot_table:

In [11]: medals = df.pivot_table('no of medals', ['Year', 'Country'], 'medal')

In [12]: medals
Out[12]:
medal             Bronze  Gold  Silver
Year Country
1896 Afghanistan       3     5       4
     Algeria           3     1       2

and if you want to reorder the columns:

In [12]: medals.reindex_axis(['Gold', 'Silver', 'Bronze'], axis=1)
Out[12]:
medal             Gold  Silver  Bronze
Year Country
1896 Afghanistan     5       4       3
     Algeria         1       2       3

Convert rows to columns and group

Why not a conditional aggregation

SELECT Comedor      = com.NombrePuntoEntrega 
      ,APLICACIONES = SUM(case when a.NombreArea = 'APLICACIONES' then s.CantidadRaciones else 0 end) 
      ,ARANDANOS    = SUM(case when a.NombreArea = 'ARANDANOS'    then s.CantidadRaciones else 0 end) 
      ,PALTO        = SUM(case when a.NombreArea = 'PALTO'        then s.CantidadRaciones else 0 end) 
  FROM PPA_SolicitudRaciones c inner join  
       PPA_SolicitudRacionesDET S on c.IdSolicitud =s.IdSolicitud and s.IdLocalidad =c.IdLocalidad 
 INNER JOIN PPA_AREAS A ON A.IDLOCALIDAD=S.IDLOCALIDAD  and a.IdArea =s.IdArea 
 INNER JOIN PPA_PuntosEntrega com on com.IdLocalidad =s.IdLocalidad and com.IdPuntoEntrega =s.IdPuntoEntrega 
 WHERE (s.IdLocalidad =@IdLocalidad or @IdLocalidad =0)
    AND (s.IdArea =@IdArea or @IdArea =0)
    AND (c.FechaPedido between @FechaDel and @FechaAl   or   @FechaDel ='1900.01.01' )
    AND (c.IdTipoComida  =@TipoComida or @TipoComida=0)
 GROUP BY com.NombrePuntoEntrega

Convert combinations of row+column as Column headers

Let us do pivot_table then swaplevel

s = df.pivot_table(index= ['Machine','Time'], 
                   columns = df.Part.astype(str).radd('Part'),
                   values=['PowerA','PowerB'],
                   fill_value=-1).swaplevel(1,0, axis=1).sort_index(level=0, axis=1)

s.columns = s.columns.map('_'.join)
s.reset_index(inplace=True)
s
Out[751]: 
   Machine   Time  Part1_PowerA  Part1_PowerB  Part2_PowerA  Part2_PowerB
0        1  20:30           0.1           0.4           0.9           0.7
1        1  20:31           0.3           0.1           0.2           0.3
2        2  20:30           0.2           0.5          -1.0          -1.0
3        2  20:31           0.8           0.4          -1.0          -1.0

convert row values to single column as array (or to multiple columns)

Consider below approach

select empid, totaltax, totaldeductions, taxes, deductions
from emptbl e 
left join (
  select empid, array_agg(t.tax order by tax_line_item) taxes
  from tax t group by empid
) using (empid)
left join (
  select empid, array_agg(t.deduction order by ded_line_item) deductions
  from deductions t group by empid
) using (empid)

if applied to sample data in your question - output is

Convert Rows to Columns SQL

You will have to go for a dynamic query, check if this will suit your needs.

I created a common table expression to be able to use distinct and then order by in the stuff function:

DECLARE @QUERY NVARCHAR(MAX)
DECLARE @Columns NVARCHAR(MAX)

WITH cte_unique_inspection_unit_number AS
(
SELECT DISTINCT QUOTENAME('TestResults' + CAST(inspection_unit_number AS VARCHAR)) TestResultsN, 
       inspection_unit_number
FROM IQC_Tensile_TF
)

SELECT @Columns = STUFF((SELECT ', ' + TestResultsN
            FROM cte_unique_inspection_unit_number
            ORDER BY inspection_unit_number
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,2,''),

        @query = 'SELECT batch, node_number, characteristic, ' + @Columns + ' from 
            (
                select batch,
                       node_number,
                       characteristic,
                       measured_value,
                       ''TestResults'' + CAST(inspection_unit_number AS VARCHAR) TestResultsN
                from IQC_Tensile_TF
           ) x
            pivot 
            (
                 max(measured_value)
                for TestResultsN in (' + @Columns + ')
            ) p '

EXEC(@query)

To view the execution in fiddle:

https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=7898422e4422faacb25d7f3c2285f14a

If you find my answer useful, i would appreciate if you vote up and mark as accepted =D

Convert rows to columns in SQL in teradata

You want a pivot query here, which means you should aggregate by name and then take the max of the CASE expressions:

SELECT 
    NAME,
    MAX(CASE WHEN DATE = '2022-01-01' THEN EOD_BALANCE END) AS "01-Jan-22",
    MAX(CASE WHEN DATE = '2022-01-02' THEN EOD_BALANCE END) AS "02-Jan-22",
    MAX(CASE WHEN DATE = '2022-01-03' THEN EOD_BALANCE END) AS "03-Jan-22" 
FROM TABL1
GROUP BY NAME;

How to transpose some columns in row in power query?

In powerquery, click select the first three columns, then right click unpivot other columns, then file close and load

---

Related Topics