Click here to Skip to main content
11,483,586 members (70,033 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#
In MS Access database as follows:
 Date          Session   Course     Faculty_Code
 
 25/01/2013      1       AFF             GS
 25/01/2013      1       AFF             VB
I would like the output as follows:
 Date          Session   Course     Faculty_Code
 
 25/01/2013      1       AFF             GS,VB
When I tried your query output shows as follows:

I tried with this query:
TRANSFORM First(Tb_SCh_TIme_Table.Faculty_Code) AS a
SELECT Tb_SCh_TIme_Table.Sch_Date, Tb_SCh_TIme_Table.Course
FROM Tb_SCh_TIme_Table
GROUP BY Tb_SCh_TIme_Table.Sch_Date, Tb_SCh_TIme_Table.Course
PIVOT Tb_SCh_TIme_Table.Session;
When I tried your query output shows as follows;
Date          Session   Course     Faculty_Code
 
25/01/2013      1       AFF             GS
In the above output not showing the Two faculty name only shows the GS Faculty_Code. I would like the VB Faculty_Code also.

I need the final output as follows:
 Date          Session   Course     Faculty_Code
 
 25/01/2013      1       AFF             GS,VB.

For the same date, session and course, different faculty takes class I need to mention both Faculty_Code also.

How can I write the query to get the final output as I mentioned above.

Please help me. How can I do it?


Thanks.
Posted 25-Jan-13 21:01pm
Edited 25-Jan-13 22:36pm
v3
Comments
Richard MacCutchan at 26-Jan-13 4:50am
   
Who are you directing this message to?

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,
Can you try the below SQL

SELECT t1.Sch_Date,t1.Session,t1.Course,
       Faculty_Code =REPLACE( (SELECT Faculty_Code AS [data()]
           FROM [tb_SchTime_Table] t2
          WHERE t2.Sch_Date = t1.Sch_Date and t2.Course=t1.Course
            FOR XML PATH('')
            ), ' ', ',')
      FROM [tb_SchTime_Table] t1
      GROUP BY Sch_Date,Course,Session ;

Best Regards
Muthuraja
  Permalink  

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



Advertise | Privacy | Mobile
Web01 | 2.8.150520.1 | Last Updated 26 Jan 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100