Cross Tab with nvarchar()

Question

Post reply

Cross Tab with nvarchar()

r_slot

Ten Centuries

Points: 1379
More actions
December 10, 2010 at 2:36 am

#244081

Hello,
I'm wondering if it is possible to make a cross tab query with the data mentioned below. It must be a case when etc. because my sql server 2005 doesn't eat a pivot. The data in the table are as follows:
Coureur Jaar Koersnaam
Adorni, Vittorio (ITA)1965Giro d'Italia
Aimar, Lucien (FRA)1966Tour de France
Altig, Rudy (DUI) 1962Vuelta a Espana
Anquetil, Jacques (FRA)1957Tour de France
Anquetil, Jacques (FRA)1960Giro d'Italia
Anquetil, Jacques (FRA)1961Tour de France
Anquetil, Jacques (FRA)1962Tour de France
Anquetil, Jacques (FRA)1963Tour de France
Anquetil, Jacques (FRA)1963Vuelta a Espana
Anquetil, Jacques (FRA)1964Giro d'Italia
Anquetil, Jacques (FRA)1964Tour de France
Armstrong, Lance (USA)1999Tour de France
Armstrong, Lance (USA)2000Tour de France
Armstrong, Lance (USA)2001Tour de France
Armstrong, Lance (USA)2002Tour de France
Armstrong, Lance (USA)2003Tour de France
Armstrong, Lance (USA)2004Tour de France
Armstrong, Lance (USA)2005Tour de France
Bahamontes, Federico (SPA)1959Tour de France
Baldini, Ercole (ITA)1958Giro d'Italia
Balmamion, Franco (ITA)1962Giro d'Italia
The output should be like this:
Year Giro d'Italia Tour de France Vuelta a Espana
1995 Rominger, Tony (ZWI)Indurain, Miguel (SPA) Jalabert, Laurent (FRA)
1996 Tonkov, Pavel (RUS)Riis, Bjarne (DEN)Zulle, Alex (ZWI)
1997 Gotti, Ivan (ITA)Ullrich, Jan (DUI)Zulle, Alex (ZWI)
1998 Pantani, Marco (ITA)Pantani, Marco (ITA) Olano, Abraham (SPA)
1999 Gotti, Ivan (ITA)Armstrong, Lance (USA)Ullrich, Jan (DUI)
2000 Garzelli, Stefano (ITA)Armstrong, Lance (USA)Heras Hernandez, Roberto (SPA)
2001 Simoni, Gilberto (ITA)Armstrong, Lance (USA) Casero Moreno, Angel-Luis (SPA)
At this moment I have tried everything, f.i. like this:
Select Jaar,
Case When Koersnaam='Tour de France' Then Coureur End As [Tour de France]
Case When Koersnaam='Giro d'+'''Italia' Then Coureur End As [Giro d'Italia]
etc.
FROM SomeView
GROUP BY Jaar
ORDER BY Jaar
This gives complaints about Coureur and Koersnaam not in an aggregate function etc.
The problem must be in the nvarchar Coureur etc. Is there a solutiob to this problem?
Thanks in advance for any comments.
Robert

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 1

Try using Max

Select Jaar,

MAX(Case When Koersnaam='Tour de France' Then Coureur End) As [Tour de France]

MAX(Case When Koersnaam='Giro d''Italia' Then Coureur End) As [Giro d'Italia]

etc.

FROM SomeView

GROUP BY Jaar

ORDER BY Jaar;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

LutzM SSC Guru Points: 107049 More actions · Answer 2

I don't understand why you can't use PIVOT on your system...

Anyway, here are both solutions, PIVOT and CrossTab.

SELECT

Jaar ,

MAX(CASE WHEN Koersnaam ='Giro d''Italia' THEN Coureur ELSE NULL END) AS [Giro d'Italia],

MAX(CASE WHEN Koersnaam ='Tour de France' THEN Coureur ELSE NULL END) AS [Tour de France],

MAX(CASE WHEN Koersnaam ='Vuelta a Espana' THEN Coureur ELSE NULL END) AS [Vuelta a Espana]

FROM @tbl

GROUP BY Jaar

ORDER BY Jaar

SELECT

Jaar,

[Giro d'Italia] AS [Giro d'Italia],

[Tour de France] AS [Tour de France],

[Vuelta a Espana] AS [Vuelta a Espana]

FROM

(SELECT * FROM @tbl) p

PIVOT

( MAX (Coureur)

FOR Koersnaam IN

( [Giro d'Italia], [Tour de France], [Vuelta a Espana])

) AS pvt

ORDER BY Jaar

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

r_slot Ten Centuries Points: 1379 More actions · Answer 3

Hello Mark,

You have earned your points: MAX did the trick! I tried SUM, COUNT, Etc but not MAX. Thanks for your answer.

Greetings,

Robert