Split column into multiple columns by criteria

Related searches

I have a query like the following:

select
  table.date,
  table.shift,
  sum(table.value)
from
  db.table
where
  table.date >= date '2020-01-01' and
  table.filter = 'type'
group by
  table.date,
  table.shift
order by
  table.date,
  table.shift;

That returns data this way:

date       | shift | sum(value)
-----------|-------|------------
2020-01-06 | 1     | 15
2020-01-06 | 3     | 12
2020-01-07 | 1     | 20
2020-01-07 | 2     | 38
2020-01-09 | 1     |  6
2020-01-09 | 2     | 22
2020-01-09 | 3     | 14
2020-01-10 | 1     | 17
2020-01-10 | 2     |  3
2020-01-10 | 3     | 10

I'm trying to get it like this but I don't know how:

date       | 1  | 2  | 3
-----------|----|----|----
2020-01-06 | 15 |    | 12
2020-01-07 | 20 | 38 |
2020-01-09 |  6 | 22 | 14
2020-01-10 | 17 |  3 | 10

How to Parse Data in Excel (Split Column into Multiple), 4Select the Original Data type that best suits your existing data. Stack Overflow Public questions and answers; Teams Private questions and answers for your team; Enterprise Private self-hosted questions and answers for your enterprise; Talent Hire technical talent

You can do conditional aggregation :

select t.date,
       sum(case when t.shift = 1 then t.value else 0 end),
       sum(case when t.shift = 2 then t.value else 0 end),
       sum(case when t.shift = 3 then t.value else 0 end)
from db.table as t
where t.date >= date '2020-01-01' and
      t.filter = 'type'
group by t.date;

Splitting a column into multiple columns based on 2 conditions , Click the “Data” tab in the ribbon, then look in the "Data Tools" group and click "Text to Columns." The "Convert Text to Columns Wizard" will appear. In step 1 of the wizard, choose “Delimited” > Click [Next]. A delimiter is the symbol or space which separates the data you wish to split. On the Data tab, in the Data Tools group, click Text to Columns. The Convert Text to Columns Wizard opens. Choose Delimited if it is not already selected, and then click Next. Select the delimiter or delimiters to define the places where you want to split the cell content.

You can use conditional aggregation

with cte as
(
select
  table.date,
  table.shift,
  sum(table.value) as val
from
  db.table
where
  table.date >= date '2020-01-01' and
  table.filter = 'type'
group by
  table.date,
  table.shift
order by
  table.date,
  table.shift
)
select date, max(case when shift=1 then val end) as 1,
max(case when shift=1 then val end) as 2,
max(case when shift=1 then val end) as 3
from cte
group by date

How to Split Data in Long Column into Multiple Columns in Excel , Not clear about the expected output. Based on the description, we may need library(tidyverse) map(strsplit(df$Column1, "[*+^]"),� 1. Make sure you have one or more empty columns to the right. Then select the cell whose contents you want to split. In our example we select Column A. 2. On the ribbon's Data tab, find the Data Tools section and click Text to Columns. 3. The Convert Text to Columns wizard opens. Select Delimited and click Next.

You can use PIVOT for this as follows:

SELECT
    *
FROM ( SELECT
  table.date,
  table.shift,
  table.value
from
  db.table
where
  table.date >= date '2020-01-01' and
  table.FILTER = 'type' ) 
  PIVOT 
  ( SUM ( VALUE ) FOR SHIFT IN ( 1,2,3 ))
ORDER BY date;

Cheers!!

How to Make One Long Column Into Multiple Columns in Excel, Sometimes we need to split data from a long column into a table with several columns based on some criteria like X rows or X columns. Right now I am collecting a sum of times based on grouping by a part, job, machine, and type. I would like to have the summed times to be split across multiple columns rather than multiple rows ba

How To Split Text Into Multiple Columns Using Text to Column In Excel, Splitting a very long column into multiple cells can make the difference between an easy-to-read Microsoft Excel document and one with data that is poorly� In the Split Data into Multiple Worksheets dialog box, you need to: 1). Select Specific column option in the Split based on section, and choose the column value which you want to split the data based on in the drop-down list. (If your data has headers and you want to insert them into each new split worksheet, please check My data has headers option.) 2).

This excel video tutorial explains how to split text into multiple columns using the text to column Duration: 5:19 Posted: Jun 22, 2018 If you need to conditionally split values into multiple columns in Microsoft Excel, consider using the IF() function. Here's how.

The best and simple way is to write an Excel VBA macro to split a worksheet into multiple worksheets based on a specified column. And we can use an open source VBA macro from GitHub. 1# click on “ Visual Basic ” command under DEVELOPER Tab. 2# then the “ Visual Basic Editor ” window will appear.

Comments
  • This answer is the one that better fits my needs so I'll accept it. Wasn't expecting this much of a fastest gun in the west, more like close as duplicate of some question I didn't found or maybe a "have you searched?" sort of accusation. Thank you.