How to Split a Row in Multiple Rows SQL Server

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:



































projectidvalue
11
12
13
14
22
23

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



Leave a reply



Submit