How to split the data in a single row into multiple rows in SQL?
If data type of locationID is varchar then:
create table projects (ProjectID int, LocationID varchar(50));
insert into projects values(1, '[1,2,3,4]');
insert into projects values(2, '[2,3]');
Query:
select projectid, value
from projects
CROSS APPLY STRING_SPLIT(replace(replace(locationid,'[',''),']',''),',')
Output:
projectid | value |
---|---|
1 | 1 |
1 | 2 |
1 | 3 |
1 | 4 |
2 | 2 |
2 | 3 |
SQL Split One Row To Multiple Rows Based on Column Number
One simple method is a recursive CTE:
with cte as (
select ID, FKID, Product, QTY
from t
union all
select ID, FKID, Product, QTY - 1
from t
where qty > 1
)
select id, fkid, product, 1 as qty
from cte;
The only caveat is that if qty
can be 100 or greater, you'll need option (maxrecursion 0)
.
SQL : Split one row into two rows depending on column
You could do something like this using UNION ALL
:
SELECT
qc.FileID AS [FileID1]
,qc.QID1 AS [QID1]
,xqs.SID1 AS [SID1]
,NULL AS [CID1] --assigning default value as null
,xqs.Comments AS [SComments]
FROM QCTable(NOLOCK) qc
JOIN QCSectionTable(NOLOCK) xqs ON qc.QCID = xqs.QCID
LEFT JOIN QCChargeTable(NOLOCK) xqc ON xqc.QCXrefID = xqs.QCXrefID
UNION ALL
SELECT
qc.FileID AS [FileID1]
,qc.QID1 AS [QID1]
,xqs.SID1 AS [SID1]
,xqc.CID1 AS [CID1]
,xqc.Comments AS [CComments]
FROM QCTable(NOLOCK) qc
JOIN QCSectionTable(NOLOCK) xqs ON qc.QCID = xqs.QCID
LEFT JOIN QCChargeTable(NOLOCK) xqc ON xqc.QCXrefID = xqs.QCXrefID
How to split a row in multiple rows SQL Server?
You can CROSS JOIN or CROSS APPLY the table to a list of numbers.
Then use IIF or CASE to get the correspondent numbered fields.
Or CROSS APPLY on the address values directly on the table.
Example snippet:
declare @UserTable table (UserId int, Address1Line varchar(30), Address1City varchar(30), Address1State varchar(30), Address2Line varchar(30), Address2City varchar(30), Address2State varchar(30));
insert into @UserTable (UserId, Address1Line, Address1City, Address1State, Address2Line, Address2City, Address2State) values
(1,'Wonder Lane 42','WonderTown', 'WonderState', 'Somewhere 1 B', 'Nowhere', 'Anywhere'),
(2,'Backstreet 69','Los Libros', 'Everland', 'Immortal Cave 777', 'Ghost City', 'The Wild Lands');
-- Cross Join on numbers
select UserId,
case n when 1 then Address1Line when 2 then Address2Line end as [Address],
case n when 1 then Address1City when 2 then Address2City end as [City],
case n when 1 then Address1State when 2 then Address2State end as [State]
from @UserTable u
cross join (values (1),(2)) as nums(n);
-- Cross Apply on Adress values
select UserId, [Address], [City], [State]
from @UserTable Usr
cross apply (values
(1, Address1Line, Address1City, Address1State),
(2, Address2Line, Address2City, Address2State)
) AS Addr(n, [Address], [City], [State]);
Both return:
UserId Address City State
------ ----------------- ---------- --------------
1 Wonder Lane 42 WonderTown WonderState
1 Somewhere 1 B Nowhere Anywhere
2 Backstreet 69 Los Libros Everland
2 Immortal Cave 777 Ghost City The Wild Lands
Split single row value to multiple rows in Snowflake
I was able to resolve this by using LATERAL FLATTERN like a joining table and selecting the value from it.
SELECT DISTINCT A.VALUE AS COL_NAME
FROM "DB"."SCHEMA"."TABLE",
LATERAL SPLIT_TO_TABLE(COL_NAME,';')A
SQL Server : splitting a row into several rows based off it's start and end date
If you don't have a Calendar Table (highly recommended), you can use an ad-hoc tally table in concert with a simple JOIN.
You may notice Top 1000
, this can be adjusted to a more reasonable level.
Example or dbFiddle
Select [Person Ref]
,[Start Date] = dateadd(DAY,N,[Start Date])
,[End Date] = dateadd(DAY,N,[Start Date])
From YourTable A
Join (
Select Top 1000 N=-1+Row_Number() Over (Order By (Select Null))
From master..spt_values n1,master..spt_values n2
) B on N <= datediff(DAY,[Start Date],[End Date])
Returns
Person Ref Start Date End Date
1234 2021-01-01 2021-01-01
1234 2021-01-02 2021-01-02
1234 2021-01-03 2021-01-03
1234 2021-01-04 2021-01-04
1234 2021-01-05 2021-01-05
Update --- Shifts Lost
Select [Person Ref]
,[Start Date] = dateadd(DAY,N,[Start Date])
,[End Date] = dateadd(DAY,N,[Start Date])
,[Shifts Lost]= case when N<[Shifts Lost] then 1 else 0 end
From YourTable A
Join (
Select Top 10000 N=-1+Row_Number() Over (Order By (Select Null))
From master..spt_values n1,master..spt_values n2
) B on N <= datediff(DAY,[Start Date],[End Date])
Results
Person Ref Start Date End Date Shifts Lost
1234 2021-01-01 2021-01-01 1
1234 2021-01-02 2021-01-02 1
1234 2021-01-03 2021-01-03 1
1234 2021-01-04 2021-01-04 0
1234 2021-01-05 2021-01-05 0
Related Topics
Display Each Department's Number and Name and the Number of Employees Employed in Each Department
Sql. How to Check If Record Exists in Table
How to Insert Null into the Datetime Coulmn Instead 1900-01-01 00:00:00.000 in SQL Server
Sql Server Pass Column Name as Where Clause Parameter
Mysql - Operand Should Contain 1 Column(S)
How to Select Rows With Only Numeric Characters in Oracle SQL
Trim Whitespaces (New Line and Tab Space) in a String in Oracle
Find Out Count of Employees Joined in January Month
Sql - Inserting a Row and Returning Primary Key
How to Remove Line Feed Characters When Selecting Data from SQL Server
How to Find Specific Values in a Table in Oracle
Wamp Server Error [Local Server - 2 of 3 Services Running]
How to Get Max Date Value of Date Column in Multiple Tables
Extracting Data Between Two Delimiters in SQL Server