split data in a single column into multiple columns in oracle
Use ROW_NUMBER and then PIVOT:
SELECT item_1,
item_2,
item_3,
item_4
FROM (
SELECT t.*,
ROW_NUMBER() OVER (PARTITION BY name ORDER BY ROWNUM) AS rn
FROM table_name t
)
PIVOT (
MAX(value) FOR name IN (
'item_1' AS item_1,
'item_2' AS item_2,
'item_3' AS item_3,
'item_4' AS item_4
)
)
Which, for the sample data:
CREATE TABLE table_name (Name, Value) AS
SELECT 'item_1', 'AB' FROM DUAL UNION ALL
SELECT 'item_2', '2' FROM DUAL UNION ALL
SELECT 'item_3', 'B1' FROM DUAL UNION ALL
SELECT 'item_1', 'CD' FROM DUAL UNION ALL
SELECT 'item_1', 'EF' FROM DUAL UNION ALL
SELECT 'item_2', '3' FROM DUAL UNION ALL
SELECT 'item_3', 'B2' FROM DUAL UNION ALL
SELECT 'item_4', 'ZZ' FROM DUAL;
Outputs:
ITEM_1 ITEM_2 ITEM_3 ITEM_4 AB 2 B1 ZZ CD 3 B2 null EF null null null
Split multiple values from a string in one column, into multiple columns using SQL Server
With a bit of JSON and assuming you have a known or maximum number of tags
Select A.CompanyName
,A.CompanyNumber
,Tag1 = JSON_VALUE(S,'$[0]')
,Tag2 = JSON_VALUE(S,'$[1]')
,Tag3 = JSON_VALUE(S,'$[2]')
From YourTable A
Cross Apply ( values ( '["'+replace(STRING_ESCAPE(Tags,'json'),';','","')+'"]' ) ) B(S)Splitting a single column into multiple columns in R
A possible solution, based on
tidyverse:library(tidyverse)
df %>%
filter(table != "_________________________________________________" ) %>%
mutate(table = str_trim(table)) %>%
separate(table, sep = "\\s+(?=\\d+)",
into = c("Characteristic", "Urban", "Rural", "Total"), fill = "right") %>%
filter(Characteristic != "") %>%
slice(-1)
#> # A tibble: 54 × 4
#> Characteristic Urban Rural Total
#>
#> 1 Electricity
#> 2 Yes 99.8 94.4 98.9
#> 3 No 0.2 5.6 1.1
#> 4 Total 100.0 100.0 100.0
#> 5 Source of drinking water
#> 6 Piped into residence 97.1 81.4 94.4
#> 7 Public tap 0.0 0.3 0.1
#> 8 Well in residence 1.1 3.7 1.6
#> 9 Public well 0.0 0.4 0.1
#> 10 Spring 0.0 2.3 0.4
#> # … with 44 more rowsHow to split a single column values to multiple column values?
Your approach won't deal with lot of names correctly but...
SELECT CASE
WHEN name LIKE '% %' THEN LEFT(name, Charindex(' ', name) - 1)
ELSE name
END,
CASE
WHEN name LIKE '% %' THEN RIGHT(name, Charindex(' ', Reverse(name)) - 1)
END
FROM YourTablesplit one column into multiple columns usining delimiter
Use
str.splitto splitdf[['date', 'date2', 'date3']] = df['date'].replace('NULL', np.nan).str.split('+', expand=True)and
countto countdf['number of dates'] = df[['date', 'date2', 'date3']].count(axis=1)
print(df)
ID date date2 date3 number of dates
0 3009 2016 2017 None 2
1 129 2015 None None 1
2 119 2014 2019 2020 3
3 120 2020 None None 1
4 121 NaN NaN NaN 0Split PostgreSQL table column values into multiple columns
Perhaps a bit verbose, but the following seems to do it. Your
SPLIT_PART()to retrieve the city frommanager_nameneeded some tweaking; also wrap them inTRIM()to remove leading and trailing spaces.CREATE TABLE manager2
(
manager_id SERIAL PRIMARY KEY,
manager_name VARCHAR(250)
);
INSERT INTO manager2 (manager_name)
VALUES ('ANGLO EASTERN SHIPMANAGEMENT - HONG KONG, CHINA');
INSERT INTO manager2 (manager_name)
VALUES ('HARTMANN REEDEREI - LEER, GERMANY');
-- add columns to table
ALTER TABLE manager2 ADD COLUMN m_name VARCHAR(250);
ALTER TABLE manager2 ADD COLUMN city VARCHAR(250);
ALTER TABLE manager2 ADD COLUMN country VARCHAR(250);
-- populate tables with vals
WITH results AS (
SELECT manager_id AS id, TRIM(SPLIT_PART(manager_name, '-', 1)) AS m_name,
TRIM(SPLIT_PART(SPLIT_PART(manager_name, '-', 2), ',', 1)) AS city,
TRIM(SPLIT_PART(manager_name, ',', 2)) AS country
FROM manager2
)
UPDATE manager2
SET
m_name = (SELECT m_name FROM results WHERE manager_id = results.id),
city = (SELECT city FROM results WHERE manager_id = results.id),
country = (SELECT country FROM results WHERE manager_id = results.id);Table after update:
manager_id | manager_name | m_name | city | country
------------+-------------------------------------------------+------------------------------+-----------+---------
1 | ANGLO EASTERN SHIPMANAGEMENT - HONG KONG, CHINA | ANGLO EASTERN SHIPMANAGEMENT | HONG KONG | CHINA
2 | HARTMANN REEDEREI - LEER, GERMANY | HARTMANN REEDEREI | LEER | GERMANYUpdate to handle cases like 'PSA - SINGAPORE' as
PSA | NULL | SINGAPORE. Instead of the CTE used above (WITH results AS (...)), you could use the following code:WITH results AS (
SELECT manager_id AS id, TRIM(SPLIT_PART(manager_name, '-', 1)) AS m_name,
CASE
WHEN SPLIT_PART(manager_name, '-', 2) ~ ',' THEN
TRIM(SPLIT_PART(SPLIT_PART(manager_name, '-', 2), ',', 1))
ELSE
NULL
END AS city,
CASE
WHEN SPLIT_PART(manager_name, '-', 2) ~ ',' THEN
TRIM(SPLIT_PART(SPLIT_PART(manager_name, '-', 2), ',', 2))
ELSE
TRIM(SPLIT_PART(SPLIT_PART(manager_name, '-', 2), ',', 1))
END AS country
FROM manager2
)Result:
manager_id | manager_name | m_name | city | country
------------+-------------------------------------------------+------------------------------+-----------+-----------
1 | ANGLO EASTERN SHIPMANAGEMENT - HONG KONG, CHINA | ANGLO EASTERN SHIPMANAGEMENT | HONG KONG | CHINA
2 | HARTMANN REEDEREI - LEER, GERMANY | HARTMANN REEDEREI | LEER | GERMANY
3 | PSA - SINGAPORE | PSA | | SINGAPORESplit single column into multiple columns based on Rank Number
Sort of odd that you have ties. But you can use conditional aggregation with strings:
select student,
string_agg(case when program_rn = 1 then program_id end, ', '),
string_agg(case when program_rn = 2 then program_id end, ', '),
string_agg(case when program_rn = 3 then program_id end, ', ')
from t
group by student;If you know the maximum that need to be concatenated, you can use conditional aggregation:
select student,
concat(max(case when program_rn = 1 and seqnum = 1 then program_id + '; ' end),
max(case when program_rn = 1 and seqnum = 2 then program_id + '; ' end),
max(case when program_rn = 1 and seqnum = 3 then program_id + '; ' end)
),
concat(max(case when program_rn = 2 and seqnum = 1 then program_id + '; ' end),
max(case when program_rn = 2 and seqnum = 2 then program_id + '; ' end),
max(case when program_rn = 2 and seqnum = 3 then program_id + '; ' end)
),
concat(max(case when program_rn = 3 and seqnum = 1 then program_id + '; ' end),
max(case when program_rn = 3 and seqnum = 2 then program_id + '; ' end),
max(case when program_rn = 3 and seqnum = 3 then program_id + '; ' end)
),
from (select t.*,
row_number() over (partition by student, program_rn order by program_id) as seqnum
from t
) t
group by student;This is cumbersome, but possibly simpler than
FOR XML PATH.Note that I changed the delimiter to a semicolon, because that seems more natural for leaving it at the end of the list. Although it can be removed, that just further complicates the logic, perhaps unnecessarily.
How to split a pandas dataframe column with key/value pairs into multiple columns?
You need to specify the regular expression like this (with two slashes, and as a raw string):
df['sentiment'].str.split(pat=r"\\n", expand=True)Here
dfanddf['sentiment']evaluate to:df
index text02 sentiment 1 Max Muncy is great! magnitude: 0.8999999761581421\nscore: 0.89999... 2 The worst Dodger is Max muncy. magnitude: 0.800000011920929\nscore: -0.80000... 3 Max Muncy was great, but not so much now. magnitude: 0.4000000059604645\nscore: -0.4000... 4 What a fantastic guy, that Max muncy. magnitude: 0.8999999761581421\nscore: 0.89999...
Related Topics
Required to Join 2 Tables With Their Fks in a 3Rd Table
Is There Any Difference Between Is Null and =Null
MySQL Delete from With Subquery as Condition
Check Constraint in MySQL Is Not Working
How to Find Duplicate Values in a Table in Oracle
SQL - Subtracting a Depleting Value from Rows
Safely Rename Tables Using Serial Primary Key Columns
Pivoting Rows into Columns Dynamically in Oracle
Exporting Data in SQL Server as Insert Into
Is There Something Like a Zip() Function in Postgresql That Combines Two Arrays
Optimise Postgresql For Fast Testing
Custom Serial/Autoincrement Per Group of Values
How to Select Unique Records by Sql
Ms Access Query: Concatenating Rows Through a Query
Constraint Defined Deferrable Initially Immediate Is Still Deferred