I want the mutiple row output in single row using sql server

narasiman rao

12y1.3k

Query as follows

select Faculty,Convert(char(7),sch1.[Schdate],100 )+'('+STUFF(
(select ','+ ltrim(rtrim([Course])) +'-S'+ltrim(rtrim([Session])) from Tb_Sch_Time_Table sch
where sch.Schdate = sch1.Schdate and sch.Faculty = sch1.Faculty
FOR XML PATH(''))
,1,1,'')+')' as Schedule from Tb_Sch_Time_Table sch1
group by schdate,Faculty
order by 1

When i run the above the query output as follows

Faculty Schedule
CPG Aug 26 (B Tech 1-S3,B Tech 1-S4)
CPG Aug 28 (ETO-S3,ETO-S4)

i want the Aug28 date in first row not in second row.

But i want the output as follows
Faculty Schedule
CPG Aug26(B Tech 1-S3,B Tech 1-S4) Aug28(ETO -S3,ETO -S4)

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

narasiman rao12y ago
   I tried your query as follows

  SELECT Faculty,
STUFF((SELECT distinct ' ( ' + Convert(char(7),[Schdate],100 ) + ' ' +
[Course] + '-S' + cast([Session] as varchar(5) )+ ')'
from Tb_Sch_Time_Table t2
where t1.Faculty = t2.Faculty
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,2,'')
from Tb_Sch_Time_Table t1
group by Faculty

When i execute the above query output as follows

Aug 26 B Tech 1-S3) ( Aug 26 B Tech 1-S4) ( Aug 28 ETO-S3) ( Aug 28 ETO-S4)

But i want my excepted output as follows

CPG Aug26(B Tech 1-S3,B Tech 1-S4) Aug28(ETO -S3,ETO -S4)

Table structure and record as follows

Schdate Course Session Faculty
Aug26   B Tech 1 3 CPG
Aug26   B Tech 1 4 CPG
Aug28   ETO   3 CPG
Aug28 ETO 4 CPG

0
Kalai12y ago
Pls give Table Structure and insert few records for sample.
0
narasiman rao12y ago
I tried your query as follows

SELECT Faculty,
STUFF((SELECT distinct ' ( ' + Convert(char(7),[Schdate],100 ) + ' ' +
[Course] + '-S' + cast([Session] as varchar(5) )+ ')'
from Tb_Sch_Time_Table t2
where t1.Faculty = t2.Faculty
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,2,'')
from Tb_Sch_Time_Table t1
group by Faculty

When i execute the above query output as follows

Aug 26 B Tech 1-S3) ( Aug 26 B Tech 1-S4) ( Aug 28 ETO-S3) ( Aug 28 ETO-S4)

But i want my excepted output as follows

CPG Aug26(B Tech 1-S3,B Tech 1-S4) Aug28(ETO -S3,ETO -S4)

0
Kalai12y ago
Try this.

SELECT Faculty,
STUFF((SELECT distinct ' , ' + Schedule
from Tb_Sch_Time_Table t2
where t1.Faculty = t2.Faculty
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,2,'') Schedule
from Tb_Sch_Time_Table t1
group by Faculty

Output:

Faculty Schedule
CPG Aug 26 , Aug 27 , Aug 28
CS Aug 26 , Aug 27
EC Aug 27 , Aug 29
0