How can i pivot this Output ....?

Transact-SQL
1

Hi,
this Output

rn CustomerID City Amount nCount
1 102996 Berlin 675.00 3
2 102996 Magdeburg 288.00 2
3 102996 Bielefeld 268.00 2

CustomerID Top1City Amount nCount Top1City Amount nCount Top1City Amount nCount
102996 Berlin 675.00 3 London 288.00 3 Paris 268.00 3

Thanks
Regards
Nicole

2

You can do something like this, if you can rely on rn column:

select CustomerID
, MAX(CASE WHEN RN =1 
THEN CITY 
END) AS CITY1
, SUM(CASE WHEN RN =1 
THEN Amount 
END) AS Amount1
, MAX(CASE WHEN RN = 2
THEN CITY 
END) AS CITY2
, SUM(CASE WHEN RN =2
THEN Amount 
END) AS Amount2
from @t
GROUP BY CustomerID

see: Pivot Multiple Columns in SQL Server

3

Great !!!
Thank you Mangal :grinning:

4

Wow, is it really that easy to turn Magdeburg into London? :grin:

1 Like