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

Question

Query as follows select FacultyConvert(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('')) 11'')+')' as Schedule from Tb_Sch_Time_Table sch1 group by schdateFaculty order by 1 When i run the above the query output as follows Faculty Schedule CPG Aug 26 (B Tech 1-S3B Tech 1-S4) CPG Aug 28 (ETO-S3ETO-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-S3B Tech 1-S4) Aug28(ETO -S3ETO -S4)

narasiman rao · Answer

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)') 12'') 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-S3B Tech 1-S4) Aug28(ETO -S3ETO -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

Kalai · Answer

Pls give Table Structure and insert few records for sample.

narasiman rao · Answer

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)') 12'') 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-S3B Tech 1-S4) Aug28(ETO -S3ETO -S4)

Kalai · Answer

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)') 12'') 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