Click here to Skip to main content
12,511,494 members (55,836 online)
Rate this:
 
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 20:01pm
Updated 25-Jan-13 21:36pm
v3
Comments
Richard MacCutchan 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160929.1 | Last Updated 26 Jan 2013
Copyright © CodeProject, 1999-2016
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