Concatenating 1

[Cobby1812]

Morning,

I have a table that holds IT Call Information. The table assigned an ID and also has a CallID generated by another system.

Table shown below

ID CallID CallNotes Date AssignedTo
1 1 IT text Test 1 27/04/2015 Mark Cobb
2 1 IT text Test 2 27/04/2015 Mark Cobb
3 2 Test Text 1 27/04/2015 Mark Cobb
4 2 Test text 2 27/04/2015 Mark Cobb

What I want to do is return a row that shows the CallNotes in one row for instance

CallID - 1 = IT text Test 1 - IT text Test 2
CallID - 2 = Test Text 1 - Test test 2

and so on....so for each item added I can return the history of the call...

I cant figure it out at all..

Please help me

Regards

Mark 'Slowly going mad' somewhere in Kent

 

[bborissov]

DECLARE @Test TABLE (id int, CallId int, CallNotes varchar(50), Date1 date, AssignedTo varchar(20))
INSERT INTO @Test VALUES(1, 1,'IT text Test 1','20150427','Mark Cobb')
INSERT INTO @Test VALUES(2, 1,'IT text Test 2','20150427','Mark Cobb')
INSERT INTO @Test VALUES(3, 2,'Test Text 1','20150427','Mark Cobb')
INSERT INTO @Test VALUES(4, 2,'Test text 2','20150427','Mark Cobb')

;WITH cte AS (
  SELECT Callid, CAST(MAX(CallNotes) as varchar(max)) AS CallNotes, 0 AS Rnk
         FROM @Test
  GROUP BY Callid
  UNION ALL
  SELECT Cte.CallId, 
         Cte.CallNotes+','+Tst.CallNotes,
         Cte.Rnk+1 AS Cte
  FROM Cte
  INNER JOIN @Test Tst ON Cte.CallId = tst.CallId 
                      AND cte.CallNotes NOT LIKE '%'+Tst.CallNotes+'%'  
  )
  
SELECT CallId, MAX(CallNotes) AS CallNotes
FROM Cte
GROUP BY CallId

Borislav Borissov
VFP9 SP2, SQL Server

 

[Cobby1812]

Thank you so much bborissov

I have one small question...the result sets are coming out in the wrong order!!!

]For instance Line 1 below should be call id 1 id 1, call id 1 id 2 - any idea's

1 call id 1 id 2,call id 1 id 1
2 call id 2 id 4,call id 2 id 3
3 call id 3 id 9,call id 3 id 8,call id 3 id 7,call id 3 id 6,call id 3 id 5
4 WORK 1,Try Once More,Go again


Many thanks

 

[bborissov]

DECLARE @Test TABLE (id int, CallId int, CallNotes varchar(50), Date1 date, AssignedTo varchar(20))
INSERT INTO @Test VALUES(1, 1,'IT text Test 1','20150427','Mark Cobb')
INSERT INTO @Test VALUES(2, 1,'IT text Test 2','20150427','Mark Cobb')
INSERT INTO @Test VALUES(3, 2,'Test Text 1'   ,'20150427','Mark Cobb')
INSERT INTO @Test VALUES(4, 2,'Test text 2'   ,'20150427','Mark Cobb')

;WITH cte AS (
  SELECT Tst.Callid, CAST(Tst.CallNotes as varchar(max)) AS CallNotes, 0 AS Rnk
         FROM @Test Tst
  INNER JOIN (SELECT CallId, MIN(Id) AS Id FROM @Test GROUP BY CallId) Tst1 ON Tst.CallId = Tst1.CallId AND
                                                               Tst.Id = Tst1.Id
  UNION ALL
  SELECT Cte.CallId, 
         Cte.CallNotes+','+Tst.CallNotes,
         Cte.Rnk+1 AS Cte
  FROM Cte
  INNER JOIN @Test Tst ON Cte.CallId = tst.CallId 
                      AND cte.CallNotes NOT LIKE '%'+Tst.CallNotes+'%'  
  )
  
SELECT CallId, MAX(CallNotes) AS CallNotes
FROM Cte
GROUP BY CallId

Borislav Borissov
VFP9 SP2, SQL Server

 

[Cobby1812]

Doh of course.....thank you so much