I tried this query but not geeting the correct output

Question

0.00/5 (No votes)

See more:

My query as follows in ms access data base

SQL

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,Tb_SCh_TIme_Table.Faculty_Code
PIVOT Tb_SCh_TIme_Table.Session

Output as follows;

Sch_Date    Course    1      2     3
12/01/2013  AFF       GS
12/01/2013  AFF       NR
13/01/2013  TFC                    GS
13/01/2013  TFC       VB    VB

From the above output I want the final output as follows;

Sch_Date    Course    1         2     3
12/01/2013  AFF       GS,NR
13/01/2013  TFC                       GS
13/01/2013  TFC       VB       VB

Using my above query how to get the output.

How can I do? Please help me.

[Edit]Code block added[/Edit]

Posted 26-Jan-13 5:59am

Member 8054539

Updated 26-Jan-13 6:01am

Thomas Daniels

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Sheikh Muhammad Haris · Answer 1 · 2013-01-26T13:41:00

Solution 2

A very simple and handy solution ...

Try this

http://tejasnshah.wordpress.com/2009/02/28/sql-server-get-column-values-as-comma-seperated-list-using-xml-path-instead-of-udfs-using-sql-coalesce/[^]

Posted 26-Jan-13 13:41pm

Sheikh Muhammad Haris

Muthuraja Irullandi · Answer 2 · 2013-01-26T06:07:00

Hi,
Try with the below Query

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