SQL: comment: modifier les valeurs d'un champ en fonction de l'ordre des files

Affiche de la première fois ici … allez-y doucement =)

Fondamentalement, ce que j'essaie de faire est de changer le [attachlvl] de l'logging étiqueté «pièce jointe» dans la colonne [xMedia] à «1» pour tous les loggings étiquetés «pièce jointe» après tout logging étiqueté «eMail».

Ensuite, la partie délicate consiste à replace le [ATTACHPID] par un numéro unique pour l'e-mail au-dessus de la pièce jointe et tout ce qui est étiqueté «pièce jointe» ci-dessous, je voudrais également remplir le champ [pièce jointe] pour le courrier électronique. ) des pièces jointes qui sont ci-dessous séparé par un point-virgule.

Voici à quoi ressemble ma table:

[docorder] [docid] [filename] [attachpid] [attachlvl] [attach] [xmedia] 1 | SAM003266 | SAM003266^eMailContent.htm | 0 | 0 | NULL | eMail 2 | SAM003268 | SAM003268^eMailContent.htm | 0 | 0 | NULL | eMail 3 | SAM003269 | SAM003269^THEROCKQ12013.pdf | 0 | 0 | NULL | Attachment 4 | SAM003569 | SAM003269^THEROCKQ12014.pdf | 0 | 0 | NULL | Attachment 5 | SAM003270 | SAM003270^eMailContent.htm | 0 | 0 | NULL | eMail 6 | SAM003273 | SAM003273^eMailContent.htm | 0 | 0 | NULL | eMail 7 | SAM003275 | SAM003275^eMailContent.htm | 0 | 0 | NULL | eMail 8 | SAM003276 | SAM003276^[email protected]_20130109_093821.pdf | 0 | 0 | NULL | Attachment 9 | SAM004269 | SAM003269^THEROCKQ12013.pdf | 0 | 0 | NULL | Attachment 

Voici ce que je voudrais que le résultat final ressemble:

 [docorder] [docid] [filename] [attachpid] [attachlvl] [attach] [xmedia] 1 | SAM003266 | SAM003266^eMailContent.htm | 0 | 0 | NULL | eMail 2 | SAM003268 | SAM003268^eMailContent.htm | 1234567 | 0 | SAM003269^THEROCKQ12013.pdf ; SAM003269^THEROCKQ12014.pdf | eMail 3 | SAM003269 | SAM003269^THEROCKQ12013.pdf | 1234567 | 1 | NULL | Attachment 4 | SAM003569 | SAM003269^THEROCKQ12014.pdf | 1234567 | 1 | NULL | Attachment 5 | SAM003270 | SAM003270^eMailContent.htm | 0 | 0 | NULL | eMail 6 | SAM003273 | SAM003273^eMailContent.htm | 0 | 0 | NULL | eMail 7 | SAM003275 | SAM003275^eMailContent.htm | 1234568 | 0 | SAM003276^[email protected]_20130109_093821.pdf ; SAM003269^THEROCKQ12013.pdf | eMail 8 | SAM003276 | SAM003276^[email protected]_20130109_093821.pdf | 1234568 | 1 | NULL | Attachment 9 | SAM004269 | SAM003269^THEROCKQ12013.pdf | 1234568 | 1 | NULL | Attachment 

Il serait plus facile si vous avez normalisé ce qui est ce qu'un bon chunck la requête ci-dessous.

 DECLARE @t TABLE ( [docorder] INT, [docid] VARCHAR(20), [filename] VARCHAR(100), [attachpid] INT, [attachlvl] INT, [attach] VARCHAR(MAX), [xmedia] VARCHAR(20) ) INSERT INTO @t ([docorder],[docid],[filename],[attachpid],[attachlvl],[attach],[xmedia]) VALUES (1 , 'SAM003266' , 'SAM003266^eMailContent.htm' , 0 , 0 , NULL , 'eMail'), (2 , 'SAM003268' , 'SAM003268^eMailContent.htm' , 0 , 0 , NULL , 'eMail'), (3 , 'SAM003269' , 'SAM003269^THEROCKQ12013.pdf' , 0 , 0 , NULL , 'Attachment'), (4 , 'SAM003569' , 'SAM003269^THEROCKQ12014.pdf' , 0 , 0 , NULL , 'Attachment'), (5 , 'SAM003270' , 'SAM003270^eMailContent.htm' , 0 , 0 , NULL , 'eMail'), (6 , 'SAM003273' , 'SAM003273^eMailContent.htm' , 0 , 0 , NULL , 'eMail'), (7 , 'SAM003275' , 'SAM003275^eMailContent.htm' , 0 , 0 , NULL , 'eMail'), (8 , 'SAM003276' , 'SAM003276^[email protected]_20130109_093821.pdf' , 0 , 0 , NULL , 'Attachment'), (9 , 'SAM004269' , 'SAM003269^THEROCKQ12013.pdf' , 0 , 0 , NULL , 'Attachment') ;WITH emails AS ( SELECT emails.docorder, emails.docid, emails.[filename], CHECKSUM(emails.[docid]) emailpid, attachpid, emails.attachlvl, emails.[attach], emails.xmedia FROM @t emails WHERE emails.xmedia='email' ) , attachments AS ( SELECT * FROM ( SELECT attachments.docorder, attachments.docid, attachments.[filename], CHECKSUM(emails.[docid]) emailpid, 1 attachlvl, attachments.[attach], attachments.xmedia, DENSE_RANK() OVER (PARTITION BY attachments.docid ORDER BY emails.docorder DESC) dr FROM emails JOIN ( SELECT * FROM @t WHERE xmedia='attachment' ) attachments ON attachments.docorder > emails.docorder ) t WHERE dr=1 ) , grouped_attachments AS ( SELECT emailpid, LEFT(filenames , LEN(filenames )-1) filenames FROM attachments AS extern CROSS APPLY ( SELECT [filename] + ';' FROM attachments AS intern WHERE extern.emailpid = intern.emailpid FOR XML PATH('') ) pre_sortingmmed (filenames) GROUP BY emailpid, filenames ) SELECT emails.docorder, emails.docid, emails.[filename], COALESCE(grouped_attachments.emailpid, emails.attachpid) attachpid, emails.attachlvl, grouped_attachments.filenames [attach], emails.xmedia FROM emails LEFT JOIN grouped_attachments ON grouped_attachments.emailpid = emails.emailpid UNION ALL SELECT attachments.docorder, attachments.docid, attachments.[filename], grouped_attachments.emailpid attachpid, attachments.attachlvl, attachments.[attach], attachments.xmedia FROM attachments LEFT JOIN grouped_attachments ON grouped_attachments.emailpid = attachments.emailpid ORDER BY docorder 

sqlfiddle