2

EDIT: Adding Code reduction to make query clearer:

Select
AMS.arecno As [RecNo],
Convert(DATE,AMS.adate) As [Date],
ac.acommentno as [commentno],
ac.acomment as [comments]

From
amain As AMS

left Join
asfat As ASF On AMS.arecno = ASF.afatrecno

left join 
acomments as ac on ac.areportno = asf.afatrecno 

Order By
AMS.arecno Desc

My first table has this type of info:

recno |  date  

1234  |  2017
6548  |  2018

I am then left joining on a table called comments. Per record number (recno) there are multiple comments.

like this:

recno  | commentno  |  comments

1234   | 1          |  blah blah...
1234   | 2          |  doot doot...
6548   | 1          |  jib jab...
6548   | 2          |  flib flob...

I'd like to show this:

recno |  date  | comments |

1234  |  2017  | Comment 1: blah blah...  Comment 2:  doot doot... 
6548  |  2018  | Comment 1:jib jab...     Comment 2: flib flob...

I've looked up and tried a few solutions but am really struggling. Any help would be much appreciated.

Note: I can't create any tables or stored procedures due to limitations on our ODBC setup and have a very limited sql knowledge compared to most.

fred
  • 21
  • 3
  • 1
    Have a look at this SO answer: https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 – Tim Biegeleisen Apr 06 '18 at 08:38
  • 1
    Note that broad questions not showing any effort usually don't get a lot of help here. It would be better for you to show us a query you have tried. – Tim Biegeleisen Apr 06 '18 at 08:38
  • I'd like to but the query is massive and contains sensitive info. I will try to make a cut down version desensitized. Thanks for your help – fred Apr 06 '18 at 08:44
  • Possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) – Michał Turczyn Apr 06 '18 at 08:45
  • The links given here in the comments _are_ a cut down version. You should be able to adapt it to your own query. – Tim Biegeleisen Apr 06 '18 at 08:45
  • Thanks Michal, I checked that one out but I don't think I can do it because I can't use views. I wasn't familiar with the information_schema part and googled it to find it was a view. I'm using an ODBC and writing in TSQL or MS SQL (same thing I think, am really quite new). EDIT: I still had a good go, but squeezing my code (joins and table references) into that setup is quite confusing. I'm fairly certain I can't use a view so perhaps I'm out of options. – fred Apr 06 '18 at 09:21
  • Thanks Tim, I had a look at your link and tried that which I mentioned above to Michal. I've added my code as you suggested, maybe it will help show what I'm using. Apologies for quality. I'm only learning and my colleague is away ill so I'm reaching out for help for the first time. Appreciate your time. – fred Apr 06 '18 at 09:25

1 Answers1

5

you can try the following query statement

CREATE TABLE #RecordInfo(RecNo INT,RecDate VARCHAR(10))
INSERT INTO #RecordInfo
SELECT 1234,'2017' UNION ALL SELECT 6548,'2018'

CREATE TABLE #CommentsInfo(RecNo INT,CommentNo INT,Comments VARCHAR(MAX))
INSERT INTO #CommentsInfo
SELECT 1234, 1, 'blah blah' UNION ALL SElECT 1234, 2, 'doot doot'
UNION ALL SELECT 6548, 1, 'jib jab' UNION ALL SELECT 6548 ,2,'flib flob'

;With CTE AS
(
    SELECT  R.RecNo,R.RecDate,CONCAT('Comment ',C.CommentNo,': ', C.Comments) AS Comments 
    FROM #RecordInfo R
    LEFT JOIN #CommentsInfo C ON R.RecNo = C.RecNo
)


SELECT 
  t1.RecNo, t1.RecDate,Comments=
  STUFF((
    SELECT ',' + t2.Comments
    FROM CTE t2
    WHERE t1.RecNo = t2.RecNo
    FOR XML PATH(''), TYPE).value('.','varchar(1000)'),1,1,'')
FROM CTE as t1
GROUP BY t1.RecNo,t1.RecDate;

DROP TABLE #RecordInfo
DROP TABLE #CommentsInfo

I think your problem will be solved by this. Thanks.

Emdad
  • 822
  • 7
  • 14
  • 1
    CTEs don't start with a semi-colon. SQL statements end with semi-colons. But I'm upvoting your answer anyway. :) – Eric Brandt Apr 06 '18 at 13:24