CODEWITHSUNDEEP
sqlsql-serverstored-procedures
Ahmet
Ahmet

Reputation: 59

How to make two records from one?

I have records with the following:

Id    Date          MondayMorning  MondayEvening  TuesdayMorning, and so on
1   2012-01-01          2               3   
2   2012-01-01          2               2

I want split record 1 in two records because the states of mondaymorning and mondayevening are not the same. If the state is the same then do nothing.

The result that I expect:

Id    Date          MondayMorning  MondayEvening  TuesdayMorning
1   2012-01-01          2               NULL          2
1   2012-01-01          NULL            3             2
2   2012-01-01          2               2

Please remember that also the other days can vary.

How can I do this?

Upvotes: 1

Views: 2076

Answers (4)

Ravi
Ravi

Reputation: 1

Select * from empmas where compcd=1 and empcd =0001 connect by level <=2;

Retrieves Employee 0001 Details and created two records of that retrieves record.

Upvotes: -2

Lourens
Lourens

Reputation: 278

I do not completely understand the output you require but the following script will give you all the records in the table and then all the records with the MondayMorning and MondayEvening that do not match.

SELECT * FROM table
UNION ALL
SELECT * FROM table WHERE table.MondayMorning <> table.MondayEvening

@Ahmet Try this to get the NULL in the columns

SELECT id
    ,date
    ,MondayMorning
    ,(CASE WHEN table.MondayEvening <> table.MondayMorning THEN NULL ELSE MondayEvening END) AS MondayEvening
    ,TuesdayMorning
    ,(CASE WHEN table.TuesdayEvening <> table.TuesdayMorning THEN NULL ELSE TuesdayEvening END) AS TuesdayEvening
FROM table 

UNION ALL 

SELECT id
    ,date
    ,NULL AS MondayMorning
    ,MondayEvening
    ,NULL AS TuesdayMorning
    ,TuesdayEvening
FROM table
WHERE table.MondayMorning <> table.MondayEvening
OR table.TuesdayMorning <> table.TuesdayEvening

Upvotes: 4

Niladri Biswas
Niladri Biswas

Reputation: 4171

Since you didn't specify what is your needed output, I have come up with the below. Let us know your actual output

Declare @t Table(Id int identity,[Date] Datetime,MondayMorning Int,MondayEvening Int)
Insert Into @t Select '2012-01-01',2,3 Union All Select '2012-01-01', 2,2

;With Cte As
(Select
    Id ,
    [Date],
    MondayMorning,
    MondayEvening,
    [Status] = Case When MondayMorning = MondayEvening Then '0' Else '1' End
From @t)
Select Id,[Date],MondayMorning,MondayEvening From Cte Where [Status] = 0
Union All
Select Id,[Date],MondayMorning,Null From Cte Where [Status] = 1
Union All
Select Id,[Date],Null,MondayEvening From Cte Where [Status] = 1

enter image description here

Upvotes: 2

podiluska
podiluska

Reputation: 51504

select id, date, mondaymorning as monday from yourtable
union
select id, date, mondayevening as monday from yourtable

(A union will remove duplicates by default)

Or more generically

select distinct id, [date], value from yourtable
unpivot 
(value for day in (mondaymorning, mondayevening, ....)) u

Upvotes: 3

Related Questions