August 14, 2014 at 9:26 am
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
August 14, 2014 at 11:10 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply