I tried my query but showing error in sql query

Question

1.00/5 (2 votes)

See more:

In database records as follows

Schdate Course Session Faculty
28 Aug MFA 1 AD
28 AUg EFA 2 AD
28 Aug MC 3 AD

i want the output as follows

28Aug(MFA -S1,EFA -S2,MC -S3)

for getting a above output i tried my query as follows

SQL

SELECT Facuty, 
  STUFF((SELECT '( ' + Convert(char(7),[Schdate],100 )+ ' ' + [Course] + '-S' + [Session] +  ')'
              from Tb_Sch_Time_Table_Details sch
            where  sch.Facuty = sch1.Facuty
             FOR XML PATH('') [course])
        FROM Tb_Sch_Time_Table_Details sch1
GROUP BY Facuty
ORDER BY 1

When i execute the above query shows error as follows

Incorrect syntax near 'Course'

please help me how can i solve the problem to get the correct ouptut.

Posted 24-Aug-14 5:51am

narasiman1

Updated 24-Aug-14 6:56am

Dilan Shaminda

v2

Add a Solution

Comments

BacchusBeale 24-Aug-14 19:58pm

I never used it but looking at MSDN syntax is wrong: STUFF ( character_expression , start , length , replaceWith_expression ) http://msdn.microsoft.com/en-au/library/ms188043.aspx

David Lee 145 24-Aug-14 21:32pm

Is your XML PATH right? Can you show me the full source data?

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)

ChauhanAjay · Answer 1 · 2014-08-24T18:18:00

Just try the below query and let me know the result.

SQL

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

_Asif_ · Answer 2 · 2014-08-24T23:02:00

Try this

SELECT Faculty, 
  (SELECT '( ' + Convert(char(7),[Schdate],100 )+ ' ' + [Course] + '-S' + cast([Session] as varchar(5) )+  ')'
              from @TBL sch
            where  sch.Faculty = sch1.Faculty
             FOR XML PATH('') )
        FROM @TBL sch1
GROUP BY Faculty
ORDER BY 1

I tried my query but showing error in sql query

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0