PIVOT query

Transact-SQL
1

i am wanting to combine multiple rows into a single column as a string value

my data is as such...

description | title | body
aaa one 1
aaa two 2
aaa three 3
bbb four 4
bbb five 5
ccc six 6

i want to have a sinlge string output in the format

aaa + one + 1 + two + 2 + three + 3 + bbb + four + 4 + five + 5 + ccc + six + 6

in other words concat description title and body together for all rows but group by description...

i understand PIVOT is the best approach but havent been able to get the output i need

can anyone help ?

2

If you want to concatenate as a singe string, pivot may not be the right approach. You can copy the example below and run it to see how it works.

CREATE TABLE #tmp ([description] VARCHAR(32), title VARCHAR(32), body VARCHAR(32));
INSERT INTO #tmp VALUES
('aaa','one','1'),('aaa','two','2'),('aaa','three','3'),
('bbb','four','4'),('bbb','five','5'),('bbb','six','6');

SELECT REVERSE(STUFF(REVERSE(
(
	SELECT
		a.[description] + '+' +
		( 
			SELECT title + '+' + body + '+'
			FROM #tmp b
			WHERE b.[description] = a.[description]
			ORDER BY body
			FOR XML PATH('')
		)
	FROM
		(SELECT DISTINCT [description] FROM #tmp) a
	FOR XML PATH('')
)),1,1,''));

When you post a question, if you can post the DDL for a sample table (the CREATE statement in my example above) and sample date (the INSERT statement in my example above) that would make it easier for someone to understand and respond

3

thanks you this does work but 2 things

i want the order of the description as is, not sorted alphabetically

also my final string is wrapped in html tags and these are coming out as < > rather than < > html tags

i presume the FOR XML causes this? is there a way around this?

4

Data in a SQL Server table has no order to it. So unless you specify an order, there is no way to determine what comes first and what is next. SQL Server will return some output to you, but the order will not be deterministic. In the example I posted, I used ORDER BY body. If body is not the column you want to sort by, you can sort by something else.

The sample code that I posted does not have any tags around it. This is what it gives me

aaa+one+1+two+2+three+3+bbb+four+4+five+5+six+6

Is that not what you are getting? If you are getting the same result for the test code and if you are getting a tags from your actual data, I don't know what is causing that unless I can see the data and your query.

5

ok apologies

i gave a simplistic example, in my actual datrabase i am wrapping the description and title in html and the body field is already containing html tags.