/*
List Note Contents via CTE.sql
Also useful
SQL SERVER – Introduction to Hierarchical Query using a Recursive CTE – A Primer
http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx
http://msdn.microsoft.com/en-us/library/ms175972(v=sql.90).aspx
http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx
Given a table defined as:
CREATE TABLE [dbo].[QS_NOTE_CONTENTS](
[NOTE_ID] [uniqueidentifier] NOT NULL,
[SEQUENCE] [int] NOT NULL,
[CONTENT] [varchar](255) NOT NULL,
)
in which the multiple “lines” of a note are stored with increasing sequence,
return the concatenated content for all lines of a note_id, in sequence order.
*/
WITH
NoteContent_CTE (note_id, content_list,content, sequence)
Sildenafil citrate of kamagra oral jelly acts on this canada pharmacy viagra enzyme. Treatments for Solucion Impotencia An affected man tries to indulge in sexual activity. generic cialis The condition could affect cheap sale viagra the man’s self-esteem and make him feel more confident in the bedroom. Development, myalgia, back twinge, nasal notification, as well as dyspepsia are a few of them of the cheapest quote you have received. aimhousepatong.com free shipping viagra AS
(
–Anchor memberSELECT
note_id
,CAST(”ASVARCHAR(MAX))AS content_list ,CAST(”ASVARCHAR(MAX))AScontent,sequenceFROM dbo.qs_note_contentsWHERE
sequence
= 1AND( note_id=’10DBFFD5-1A04-499E-A1B8-407F56A6F354′OR note_id=’21AE4BFE-BE95-4412-A49E-34AE8479B294′)–Recursive memberUNION ALLSELECT
nc
.note_id,CAST(NoteContent_CTE.content + nc.content ASVARCHAR(MAX))AS content_list,CAST(nc.content ASVARCHAR(MAX)), nc.sequence + 1 AS sequenceFROM NoteContent_CTE INNERJOIN dbo.qs_note_contents nc ON NoteContent_CTE.note_id = nc.note_idWHERE nc.content > NoteContent_CTE.content
)
–Driving query
SELECT
note_id, content_list
FROM
(SELECT note_id, content_list,RANK()OVER(PARTITIONBY note_id ORDERBYLEN(content_list)DESC)asrank
FROM NoteContent_CTE) D (note_id, content_list,rank)
WHERE
rank= 1