Create a view for Merged records

Question

Create a view for Merged records

skb 44459

SSCarpal Tunnel

Points: 4768
More actions
June 20, 2013 at 8:28 am

#279302

Table
Rowointer Notes
------- ---------
Row0001 TestNotes1
Row0002 TestNotes2
Row0003 TestNotes3
Would like to create a view based on above table which which will merge Notes field based on RowPointer.
it should put a return character between each notes.
View record will look like this
ViewRP ViewNotes
-------- ------------
Row0001 TestNotes1
TestNotes2
TestNotes3

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

A rather strange request and a bit limited I suspect but this should do it. Notice how I posted readily consumable ddl and data? This is something you should do in the future.

if OBJECT_ID('tempdb..#Something') is not null

drop table #Something

create table #Something

(

RowPointer char(7),

Notes char(10)

)

insert #Something

select 'Row0001', 'TestNotes1' union all

select 'Row0002', 'TestNotes2' union all

select 'Row0003', 'TestNotes3'

select top 1 RowPointer, STUFF((select Notes + char(10)

from #Something

order by RowPointer

for XML PATH('')), 1, 0, '')

from #Something

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

skb 44459 SSCarpal Tunnel Points: 4768 More actions · Answer 2

Thanks your code works . But my notes field is type ntext when i run the code i get

The data types ntext and char are incompatible in the add operator.

if OBJECT_ID('tempdb..#Something') is not null

drop table #Something

create table #Something

(

RowPointer char(7),

Notes NTEXT

)

insert #Something

select 'Row0001', 'TestNotes1' union all

select 'Row0002', 'TestNotes2' union all

select 'Row0003', 'TestNotes3'

select top 1 RowPointer, STUFF((select Notes + char(10)

from #Something

order by RowPointer

for XML PATH('')), 1, 0, '')

from #Something

The data types ntext and char are incompatible in the add operator.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Don't use the TEXT datatype. It is deprecated and the performance is horrible. It is also incredibly painful to work with. The replacement is (n)varchar(max). It would be FAR better to change your table. You should be able to change it in place with no adverse affects on anything. If you are unable you will have to cast your column in your query.

select top 1 RowPointer, STUFF((select cast(Notes as nvarchar(max)) + char(10)

from #Something

order by RowPointer

for XML PATH('')), 1, 0, '')

from #Something

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

Also, I should be ashamed of myself for not putting an ORDER BY clause on my query. It is imperative to use an order by when the order of the rows matters. For this small sample it will never be an issue, but in your actual table it does make a difference.

select top 1 RowPointer, STUFF((select cast(Notes as nvarchar(max)) + char(10)

from #Something

order by RowPointer

for XML PATH('')), 1, 0, '')

from #Something

order by RowPointer

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/