how to concatenate fields 1

[jchewsmith]

I have a SQL table like:

Job # Line # Note
12345 1 My name is
12345 2 John
12345 3 What is your
12345 4 Name
67890 1 How are you
67890 2 Today
99999 1 I am good

How can a query to get:
Job # Note
12345 My name is John What is your Name
67890 How are you Today
99999 I am good

 

[northw]

try this:

SELECT DISTINCT oq.id
, STUFF((SELECT ' ' + inq.note AS [text()]
FROM #Test inq
WHERE inq.id = oq.id
FOR XML PATH('')), 1, 1, '')AS qt
FROM #Test oq;

 

[jchewsmith]

I am not sure how to interpret this info? I don't know what oq.id or inq.note mean?

 

[Olaf Doschke]

Look closer, you don't have to care for oq or inq, these names come from the code.
Just take the query as given and replace "#Test" with your table name, besides just make sure your note column is called NOTE and your job# column is called ID.

Or if the fields really are job#, line# and note, as in this sample, the query just slightly changes

Declare @test as Table (job# int, line# int, note varchar(MAX))

insert into @test values
	 (12345, 1, 'My name is')
	,(12345, 2, 'John')
	,(12345, 3, 'What is your')
	,(12345, 4, 'Name')
	,(67890, 1, 'How are you')
	,(67890, 2, 'Today')
	,(99999, 1, 'I am good')


[highlight #FCE94F]SELECT DISTINCT oq.job#
, STUFF((SELECT ' ' + inq.note AS [text()]
FROM @Test inq
WHERE inq.job# = oq.job#
FOR XML PATH('')), 1, 1, '') AS qt
FROM @Test oq;[/highlight]

The line# is not used and not necessary, though.
And note the highlighted part really is almost unchanged northw's code. I just used a table variable @test instead of temp table #test

Bye, Olaf.

 

[Olaf Doschke]

And another take on sorting by line#, if the data isn't sorted that way. Quite simple by ORDER BY:

Declare @test as Table (job# int, line# int, note varchar(MAX))

insert into @test values
	 (12345, 1, 'My name is')
	,(12345, 4, 'name?')
	,(12345, 3, 'What''s your')
	,(12345, 2, 'John.')
	,(67890, 2, 'today?')
	,(67890, 1, 'How are you')
	,(99999, 1, 'I''m good.');


SELECT DISTINCT oq.job#
, (SELECT inq.note+' ' AS [text()]
FROM @Test inq
WHERE inq.job# = oq.job#
ORDER BY job#, line#
FOR XML PATH('')) AS concatenated
FROM @Test oq;

Bye, Olaf.