Create PIVOT - SQL Server Forums

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Create PIVOT

Author

Topic

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 - 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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-25 : 00:58:22

see

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources