CODEWITHSUNDEEP
sqlsql-server
Shohel
Shohel

Reputation: 3934

Make single record to multiple records in sql server

I have a records look like below

enter image description here

From two rows, I want to split ShiftPattern values and create multiple records and StartWeek will be created sequentially.

Final Query:

  1. Split ShiftPattern Column and Create multiple records
  2. Increase StartWeek like as 20, 21 to rotation.

Output result

enter image description here

Upvotes: 4

Views: 458

Answers (2)

Utsav
Utsav

Reputation: 8103

This is what you need. Tested in fiddle.

SQLFiddle Demo

select q.locationid,q.employeeid, 
case 
when (lag(employeeid,1,null) over (partition by employeeid order by weekshiftpatternid)) is null
 then startweek 
 else startweek + 1 
end as rotation ,
q.weekshiftpatternid,
q.shiftyear
 from 
(
select locationid,employeeid, left(d, charindex(',', d + ',')-1) as weekshiftpatternid ,
startweek,shiftyear
from (
    select *, substring(shiftpattern, number, 200) as d from MyTable locationid left join
        (select distinct number from master.dbo.spt_values where number between 1 and 200) col2
        on substring(',' + shiftpattern, number, 1) = ','
    ) t 
) q

Output

+------------+------------+----------+--------------------+-----------+
| locationid | employeeid | rotation | weekshiftpatternid | shiftyear |
+------------+------------+----------+--------------------+-----------+
|          1 |   10000064 |       20 |               1006 |      2016 |
|          1 |   10000064 |       21 |               1008 |      2016 |
|          1 |   10000065 |       20 |               1006 |      2016 |
|          1 |   10000065 |       21 |               1008 |      2016 |
+------------+------------+----------+--------------------+-----------+

Upvotes: 2

LoztInSpace
LoztInSpace

Reputation: 5697

Similar: In my test table my ID is your EmployeeID or however you want to work it.

SELECT  
*,
LEFT(shiftBits, CHARINDEX(',', shiftBits + ',')-1) newShiftPattern,
StartWeek+ROW_NUMBER() OVER(PARTITION BY ID ORDER BY shiftBits ) as newStartWeek
FROM
(
SELECT  
SUBSTRING(shiftPattern, number, LEN(shiftPattern)) AS shiftBits,
test2.*
FROM 
test2,master.dbo.spt_values 
WHERE 
TYPE='P' AND number<LEN(shiftPattern)
AND SUBSTRING(',' + shiftPattern, number, 1) = ','
) AS x

Upvotes: 1

Related Questions