CODEWITHSUNDEEP
sql-server
Alexandar R
Alexandar R

Reputation: 39

How to make three similar rows from one row in SQL Server table?

I have a table like below, that table contain one record (may be several record) but I need each and every will get three similar records with one columns values different.

For example, the table contains record like below:

Table Name: Timestamp

Id  SalesMan_Id  Module_Cdoe  Time_In  Time_Out
1   13524        Analysis     1.30pm   1.40pm
2   12543        Analysis     2.10pm   2.30pm

From the above table, I need output(result set) like below:

Id  SalesMan_Id  Module_Cdoe  Time_In  Time_Out
1   13524        Analysis     1.30pm   1.40pm
2   13524        Photo        1.30pm   1.40pm
3   13524        Survey       1.30pm   1.40pm
4   13524        Merchand     1.30pm   1.40pm
5   12543        Analysis     2.10pm   2.30pm
6   12543        Photo        2.10pm   2.30pm
7   12543        Survey       2.10pm   2.30pm
8   12543        Merchand     2.10pm   2.30pm

What is the SQL query to achieve my expectation?

Suppose the table contain like below:

Id  SalesMan_Id  Module_Cdoe  Time_In  Time_Out
1   13524        Analysis     1.30pm   1.40pm
2   13524        Photo        1.30pm   1.40pm

Then also I need output(result set) like below:

 Id  SalesMan_Id  Module_Cdoe  Time_In  Time_Out
 1   13524        Analysis     1.30pm   1.40pm
 2   13524        Photo        1.30pm   1.40pm
 3   13524        Survey       1.30pm   1.40pm
 4   13524        Merchand     1.30pm   1.40pm

Upvotes: 1

Views: 61

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28900

Demo Here updated as per Arul comments

;with cte(mod_code,id)
as
(
select 'Analysis',1 union all
select 'photo',2 union all
select 'survey',3 union all
select 'merchand',4 
 )
select distinct  salesman_id,c.*,
time_in,time_out
from #temp
cross join
cte c

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 81970

Declare @TimeStamp table (Id int, SalesMan_ID int, Module_Cdoe varchar(50),Time_In varchar(10),Time_Out varchar(10))
Insert into @TimeStamp values
(1,13524,'Analysis','1:30pm','1:40pm'),
(2,12543,'Analysis','2:10pm','2:30pm')


;with cteBase (Seq,Module_Cdoe) as(
    Select 1,'Analysis' union all
    Select 2,'Photo' union all
    Select 3,'Survey' union all
    Select 4,'Merchand'
)
Select id=Row_Number() over (Order By Time_In,SalesMan_id,Seq)
      ,SalesMan_id
      ,b.Module_Cdoe
      ,Time_In
      ,Time_Out
 from @TimeStamp A
 Join cteBase B on (A.Module_Cdoe='Analysis')
 Order By 1

Returns

id  SalesMan_id Module_Cdoe Time_In Time_Out
1   13524       Analysis    1:30pm  1:40pm
2   13524       Photo       1:30pm  1:40pm
3   13524       Survey      1:30pm  1:40pm
4   13524       Merchand    1:30pm  1:40pm
5   12543       Analysis    2:10pm  2:30pm
6   12543       Photo       2:10pm  2:30pm
7   12543       Survey      2:10pm  2:30pm
8   12543       Merchand    2:10pm  2:30pm

Upvotes: 3

Related Questions