Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Concatinating Columns

Author  Topic 

cobby1812
Starting Member

16 Posts

Posted - 2015-04-27 : 06:40:03
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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2015-04-27 : 13:55:52
Here's one way:

DECLARE @Table TABLE (ID INT,CallID INT,CallNotes VARCHAR(50), CallDate DATE, AssignedTo VARCHAR(20))
INSERT INTO @Table
VALUES
(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')



select t1.CallID,t1.CallNotes,t2.CallNotes
from @Table t1
cross apply (select top 1 callnotes
from @Table
where t1.CallID = Callid
and t1.ID < ID
order by id desc
)t2


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2015-05-02 : 03:59:58
quote:
Originally posted by cobby1812

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

I am guessing that there is NOT a maximum of two rows for each CallID? and if more they "all" need concatenating together to make a "text history" ?
Go to Top of Page
   

- Advertisement -