SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Create PIVOT
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Coxey
Starting Member

1 Posts

Posted - 01/24/2013 :  11:52:53  Show Profile  Reply with Quote
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 - 2012
QueryBuilder = FlySpeed ??OueryBuilder
SQL knowledge = mediocre but willing to learn (I know how to create a simple pivot)
[SQL-Statment:]

Select
MDT.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'
End
From EODB.DB.u_sd_MDT As MDT
Left Outer Join
(Select
MDT.MandantId,
ADT.AdressatId
From EODB.DB.u_sd_MDT As MDT
Inner Join
EODB.DB.u_sd_BZGMDT As BZGMDT On BZGMDT.MandantId = MDT.MandantId
Inner 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.MandantId
Inner Join
EODB.DB.u_sd_KOMM Kommunikation On Kommunikation.AdressatId =
Beziehung.AdressatId
Where
Replace(Replace(Replace(Replace(Replace(Kommunikation.Nummer, '/', ''), '-',''), '+', '00'), ' ', ''), 'http:', '') Is Not Null
Order 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

Edited by - Coxey on 01/24/2013 12:17:43

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/25/2013 :  00:58:22  Show Profile  Reply with Quote
see

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000