Change rowns to columns

Question

Change rowns to columns

river1

SSC-Insane

Points: 24309
More actions
October 2, 2014 at 4:34 am

#293908

Hi
I have this query:
select descr_rep_fiscal, metodoctb, dt_emissao, count (metodoctb) as ocorrencias
from vw_cartoes group by descr_rep_fiscal,metodoctb, dt_emissao
this returns the results as:
descr_rep_fiscal | metodoctb | dt_emissao | ocorrencias
I would like that metodoctb generates a column for each diferente value that it returns
Example of the result returned:
1; tipo 1, 22/07/2014,7
1; tipo 2, 22/07/2014,2
1; tipo 5, 22/07/2014,7
1; tipo 7, 22/07/2014,4
1; tipo 1, 23/07/2014,7
1; tipo 2, 23/07/2014,2
1; tipo 5, 23/07/2014,7
1; tipo 7, 23/07/2014,4
2; tipo 1, 22/07/2014,3
2; tipo 2, 22/07/2014,2
2; tipo 5, 22/07/2014,4
2; tipo 7, 22/07/2014,1
I would like the results as:
1; 7, 2,7,4, 22/07/2014
1; 7, 2,7,4, 23/07/2014
2; 3, 2,4,1, 22/07/2014
Can someone help in the t-sql?
Thank you

Viewing 10 posts - 1 through 9 (of 9 total)

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Is for example 7, 2,7,4 one string where all the nubmers are concatened with a comma, or is it actually 4 different columns?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

river1 SSC-Insane Points: 24309 More actions · Answer 2

river1

SSC-Insane

Points: 24309

October 2, 2014 at 4:52 am

#1749644

Hi,

It's four different columns.

thanks

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

You always have 4 values in the metodoctb column? (tipo 1, 2, 5 and 7).

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

river1 SSC-Insane Points: 24309 More actions · Answer 4

I want to have always those 4 columns but some times some do not have values.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

Take a look at the PIVOT operator.

An example:

Pivot tables in SQL Server. A simple sample.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

river1 SSC-Insane Points: 24309 More actions · Answer 6

That's what I have looked in the first place. But I always have to put na operator like AGV.

I don't want any average.

river1 SSC-Insane Points: 24309 More actions · Answer 7

example:

select * from vw_teste

pivot (AVG(ocorrencias) for metodoctb IN ([tipo 1],[TIPO 2],[TIPO 5],[TIPO 7])) as teste

this has average.

I don't want average..

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 8

You can also write a pivot as a bunch of CASE statements.

PIVOT compared to CASE for a crosstab result

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 9

Please see the following article. I know you don't want an aggregate but it's one of the few ways that you can privot data in SQL Server. If you need a dynamic solution in the future, see the second article.

The first article also does a perforance comparison between PIVOT and aggregated CASE statement to do these type of CROSS TABs as well as teaching the performance trick known as "pre-aggregation".

http://www.sqlservercentral.com/articles/T-SQL/63681/

http://www.sqlservercentral.com/articles/Crosstab/65048/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)