Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

pivot a simple table Expand / Collapse
Author
Message
Posted Thursday, August 14, 2014 9:26 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 8:08 AM
Points: 744, Visits: 1,056
Hi,

I have a table which has three columns:

reparticao; metodo; contribuintes

if I do a select reparticao, metodo, contribuintes from pois the result set is:


teste; tipo 1; 200
teste; tipo 2; 400
teste; tipo 5; 600
teste; tipo 7; 900
teste; tipo 0; 2
teste2; tipo 1; 200
teste2; tipo 2; 400
teste2; tipo 5; 600
teste2; tipo 7; 600
teste2; tipo 0; 4


I want pivot like this result as :

columns: reparticao; tipo 1; tipo 2; tipo 5; tipo 7
data: teste 200 ; 400 ; 600 ; 900
teste2 200 ; 400 ; 600 ;600


How can Î do the query?

Thank you

p.s i WANT ALSO TO CREATE A COLUMN AT RUNTIME THAT IS THE SUM OF TIPO 1, TIPO 2, TIPO 5 AND TIPO 7 like:


columns: reparticao; tipo 1; tipo 2; tipo 5; tipo 7; ALL
data: teste 200 ; 400 ; 600 ; 900; 2100
teste2 200 ; 400 ; 600 ;600; 1800



Post #1603348
Posted Thursday, August 14, 2014 11:10 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, September 19, 2014 2:34 PM
Points: 12,923, Visits: 12,342
This is fairly straight forward. The easiest way to do this is with a cross tab. You can find a couple articles in my signature about the static and dynamic versions. If you run into trouble post some ddl and sample data and I can give you a hand.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1603393
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse