Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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  
 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.03 seconds. Powered By: Snitz Forums 2000