2

I am searching for a GROUP_CONCAT equivalent function in SQL Server and I found this two options which are CROSS APPLY and STUFF. Because I want to concatenate the day of the sday field in the schedule table using join. Here is my schema for the database, please refer to the photo below. So what is the better approach to this one?

doctor schemaschedule schema

I tried STUFF function like so and I get the record duplicated:

enter image description here

Community
  • 1
  • 1
user3093453
  • 699
  • 2
  • 7
  • 24
  • 2
    It's not STUFF that does the job, it's FOR XML PATH('') – Szymon Mar 13 '14 at 05:04
  • I edited my question. I want the record not to be duplicated – user3093453 Mar 13 '14 at 05:04
  • How do you want to get start_time and end_time without grouping? There will be many records for each doctor. – Szymon Mar 13 '14 at 05:05
  • Can you share the logic in here? Damn I'm so saturated – user3093453 Mar 13 '14 at 05:07
  • You're getting duplicated records because you do inner join between doctor and schedule. There's 4 records in schedule for one of the doctors. – Szymon Mar 13 '14 at 05:20
  • 4
    It seems you've done your share of attempt with regard to this, and your question does seem useful in general, but you have asked it an a rather lazy manner. You probably thought it *easier for you* to just make snapshots of relevant parts of the problem instead of typing them in to the post, but the result is *difficult for us* to work with. (Or maybe it's just me who doesn't use OCR software every waking moment to feel comfortable applying it to your illustrations in order to retrieve the pieces of text I'd need for trying and working out an answer for you, in which case I beg your pardon.) – Andriy M Mar 20 '14 at 07:17
  • possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) – billinkc Mar 20 '14 at 21:42

2 Answers2

3

There's no problem with concatenating the values in your query. You can see yourself that it brings back the results correctly. Also, note that it's not STUFF() that does the concatenating, it's FOR XML PATH('').

You're getting duplicated records because you do inner join between doctor and schedule. There's 4 records in schedule for one of the doctors. Specifically, columns start_time, end_time and sstatus are the problem. You probably cannot group by them as the values can differ for each day of the schedule.

Tyou can either:

  • not return those columns in your query,
  • or concatenate those columns as well.
Szymon
  • 42,577
  • 16
  • 96
  • 114
0

You may wish to see below thread on MSDN for Group Concat functions http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f09d4166-2030-41fe-b86e-392fbc94db53/tsql-equivalent-for-groupconcat-function?forum=transactsql

The four entries are due to the fact that schedule table must be having four entries monday, tuesday, wednesday and thursday. Check the inner join you've applied.

Ankit Kumar
  • 28
  • 9