Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Create a view for Merged records Expand / Collapse
Author
Message
Posted Thursday, June 20, 2013 8:28 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, February 14, 2014 1:15 PM
Points: 86, Visits: 196
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

Post #1465733
Posted Thursday, June 20, 2013 9:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:29 PM
Points: 11,969, Visits: 10,994
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1465760
Posted Thursday, June 20, 2013 9:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, February 14, 2014 1:15 PM
Points: 86, Visits: 196
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.
Post #1465761
Posted Thursday, June 20, 2013 9:19 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:29 PM
Points: 11,969, Visits: 10,994
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1465767
Posted Thursday, June 20, 2013 9:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:29 PM
Points: 11,969, Visits: 10,994
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1465769
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse