Click here to Skip to main content
15,942,710 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Query

SQL
select top(1) p.projectname,s.sitename,(select 'Contact Person :'+ s.ContactPerson+';'+'Contact Number: '+s.ContactNumber+';'+'Email_Id: '+s.ContactEmailId+';'+'Site Address'+s.SiteAddress+';')as ContactDetails ,s.RosteringDays,'Weekly Off:'= CASE WHEN w.weekday =  1 THEN 'Sunday' when w.weekday=2 then 'Monday'    when w.weekday=3 then 'Tuesday' when w.WeekDay=4 then 'Wensday' when w.weekday=5 then 'Thursday' when w.weekday=6 then 'Friday'  when w.weekday=7 then 'Saturday' else '' end from tbl_project_site s inner join tbl_Project_SiteWeeklyOff w on w.siteid=s.siteid inner join tbl_project p on p.projectid=s.projectid where s.siteid=w.siteid


Output this is a single row o/p
Indian Overseas| Andheri| Contact Person :abc;Contact Number: 8957626526;Email_Id: abc@gamil.com;Site Address mumbai;| 9 |Tuesday


i want contact details in a seperate row
means i want output like below ,is this possible? if anyone know hw to do it please answer it.........
|Indian Overseas| Andheri |Contact Person :abc; | 9 |Tuesday
| | |Contact Number: 8957626526;| |
| | | Email_Id: abc@gamil.com; | |
| | | Site Address mumbai; | |
Posted
Updated 16-Feb-15 22:29pm
v6
Comments
Thava Rajan 17-Feb-15 5:57am    
in which version of sql server you try
Herman<T>.Instance 17-Feb-15 7:26am    
Your query looked a bit strange so:
select top(1) p.projectname,s.sitename,
(select 'Contact Person :'+ s.ContactPerson+';'+'Contact Number: '+s.ContactNumber+';'+'Email_Id: '+s.ContactEmailId+';'+'Site Address'+s.SiteAddress+';') as ContactDetails,
s.RosteringDays,
'Weekly Off:'= CASE WHEN w.weekday = 1 THEN 'Sunday'
when w.weekday=2 then 'Monday'
when w.weekday=3 then 'Tuesday'
when w.WeekDay=4 then 'Wensday'
when w.weekday=5 then 'Thursday'
when w.weekday=6 then 'Friday'
when w.weekday=7 then 'Saturday'
else '' end as [WeeklyOff]
from tbl_project_site s
inner join tbl_Project_SiteWeeklyOff w on w.siteid=s.siteid
inner join tbl_project p on p.projectid=s.projectid

-> where s.siteid=w.siteid ????
where s.siteId=<value>

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900