Coxey
Starting Member
1 Post |
Posted - 2013-01-24 : 11:52:53
|
Hello, professionals and experts and all interested parties.I am new to this forum and hope that I can help with my problem.My requirements:MS SQL - Server 2005 - 2012QueryBuilder = FlySpeed ??OueryBuilderSQL knowledge = mediocre but willing to learn (I know how to create a simple pivot)[SQL-Statment:]SelectMDT.MandantenNummer As MdtNr,Replace(Replace(Replace(Replace(Replace(Kommunikation.Nummer, '/', ''), '-',''), '+', '00'), ' ', ''), 'http:', '') As Medium,Replace(Kommunikation.Bemerkung, ';', ',') As Bemerkung,ZuordnungBemerkung = Case When Row_Number() Over (Partition By MDT.MandantenNummer,Kommunikation.Medium Order By Kommunikation.Reihenfolge, Kommunikation.Medium Desc) = 1 And Kommunikation.Medium = 1 Then 'MD8111' When Row_Number() Over (Partition By MDT.MandantenNummer,Kommunikation.Medium Order By Kommunikation.Reihenfolge,Kommunikation.Medium Desc) = 2 And Kommunikation.Medium = 1 Then 'MD8121' End,ZuordnungMedium = Case When Row_Number() Over (Partition By MDT.MandantenNummer,Kommunikation.Medium Order By Kommunikation.Reihenfolge,Kommunikation.Medium Desc) = 1 And Kommunikation.Medium = 1 Then 'MD8112' When Row_Number() Over (Partition By MDT.MandantenNummer,Kommunikation.Medium Order By Kommunikation.Reihenfolge,Kommunikation.Medium Desc) = 2 And Kommunikation.Medium = 1 Then 'MD8122' EndFrom EODB.DB.u_sd_MDT As MDTLeft Outer Join (Select MDT.MandantId, ADT.AdressatId From EODB.DB.u_sd_MDT As MDTInner Join EODB.DB.u_sd_BZGMDT As BZGMDT On BZGMDT.MandantId = MDT.MandantIdInner Join EODB.DB.u_sd_BZG As BZG On ((BZG.BeziehungId = BZGMDT.BeziehungId And MDT.MandantenTyp = 1 And BZG.BeziehungArtId = 'S00001') Or (BZG.BeziehungId = BZGMDT.BeziehungId And MDT.MandantenTyp = 2 And BZG.BeziehungArtId = 'S00003') Or (BZG.BeziehungId = BZGMDT.BeziehungId And MDT.MandantenTyp = 3 And BZG.BeziehungArtId = 'S00003'))Inner Join EODB.DB.u_sd_ADT As ADT On ADT.AdressatId = BZG.IstAdressatId) As Beziehung On Beziehung.MandantId = MDT.MandantIdInner Join EODB.DB.u_sd_KOMM Kommunikation On Kommunikation.AdressatId = Beziehung.AdressatIdWhere Replace(Replace(Replace(Replace(Replace(Kommunikation.Nummer, '/', ''), '-',''), '+', '00'), ' ', ''), 'http:', '') Is Not NullOrder By MDT.MandantenNummer[Temporary Result:] MdtNr /Medium/Bemerkung /ZuordnungBemerkung/ZuordnungMedium 10000/01234-23455/TestNummer/MD8111/MD8112 10000/05437-87654/TestNummer2/MD8121/ MD8122 10002/0654-12345/Testnummer3/MD8111/MD8112 ...........The Column "Medium" and "Bemerkung" should be Pivot. The Columns "ZuordnungBemerkung" and "ZuordnungMedium" are Header. [wish Result:] MdtNr/MD8111/MD8112/MD8121/MD8122/MD8131/MD8132 ................ 10000/TestNummer/01234-23455/TestNummer2/05437-87654 ................ 10002/Testnummer3/0654-12345 ............. Thank you all! Best Regards Coxey |
|