How can I pivot this rows

Question

Post reply

How can I pivot this rows

info 58414

Hall of Fame

Points: 3386
More actions
November 12, 2015 at 5:40 am

#399316

Hi,
this Output
rnCustomerIDCityAmountnCount
1102996Berlin675.003
2102996Magdeburg288.002
3102996Bielefeld268.002
CustomerIDTop1CityAmountnCountTop1CityAmountnCountTop1CityAmountnCount
102996Berlin675.003London288.003Paris268.003
Thanks
Regards
Nicole

Viewing 6 posts - 1 through 5 (of 5 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

You'll get a really quick response if you change this:

rn CustomerID City Amount nCount

1 102996 Berlin 675.00 3

2 102996 Magdeburg 288.00 2

3 102996 Bielefeld 268.00 2

into a CREATE TABLE... statement followed by a few inserts to populate it.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

Use CROSS TABS[/url] with the MAX() function.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 3

The best way would be to use the CROSS TAB method, mentioned in this article : http://www.sqlservercentral.com/articles/T-SQL/63681/

Will the number of columns be limited to 3 or will it depend on the data, ie if there are 4 cities does it have to be 4 wide?

Also your Input and output data do not correspond to each other which means theres a possible typo or we are missing data, in which case we would need the full dataset.

Edit : Beat me to it Lui 😀

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Luis Cazares SSC Guru Points: 183706 More actions · Answer 4

Jason-299789 (11/12/2015)
The best way would be to use the CROSS TAB method, mentioned in this article : http://www.sqlservercentral.com/articles/T-SQL/63681/
Will the number of columns be limited to 3 or will it depend on the data, ie if there are 4 cities does it have to be 4 wide?
Also your Input and output data do not correspond to each other which means theres a possible typo or we are missing data, in which case we would need the full dataset.
Edit : Beat me to it Lui 😀

You wrote a longer post. I would have spent more time on this, but if the OP won't even care to post adequately, why would I? 😀

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 5

Luis Cazares (11/12/2015)
Jason-299789 (11/12/2015)
The best way would be to use the CROSS TAB method, mentioned in this article : http://www.sqlservercentral.com/articles/T-SQL/63681/
Will the number of columns be limited to 3 or will it depend on the data, ie if there are 4 cities does it have to be 4 wide?
Also your Input and output data do not correspond to each other which means theres a possible typo or we are missing data, in which case we would need the full dataset.
Edit : Beat me to it Lui 😀
You wrote a longer post. I would have spent more time on this, but if the OP won't even care to post adequately, why would I? 😀

😀

_________________________________________________________________________
SSC Guide to Posting and Best Practices