CODEWITHSUNDEEP
sqldatabasesql-server-2008-r2
C Sharper
C Sharper

Reputation: 8626

Pivoting the count of data

I have following data:

Cod# prodotto     Mon

  A               Jan

  B               Feb

  C               Jan

  D               Feb

  A               Jan

  D               Jan

  D               Jan

I wanted to get the data as count of Cod# prodotto for particular month grouping as follows:

Cod# prodotto   Jan  Feb 

   A             2    -

   B             -    1

   C             1    -

   D             2    1

I made query as:

select * from
(
select CONVERT(CHAR(4), [Data OUT (No Val#Vuoto)], 100) as Month , [Cod# prodotto] as col1,count([Cod# prodotto]) as codprodotto
from dbPratiche
where  [Cod# prodotto] is not null
and [Data OUT (No Val#Vuoto)] < CONVERT(datetime,'2012/01/11')
and Stato='OUT ATTIVATA' or Stato='SOSPESA' 
group by ([Cod# prodotto]),CONVERT(CHAR(4), [Data OUT (No Val#Vuoto)], 100)

) T

pivot
(
  count(codprodotto)
  for Month 
  in([nov],[dec],[jan],[feb],[mar],[apr],[may],[jun],[jul],[aug],[sep],[oct])
) P

Also applied following method:

select * from
(
select 1 CntCol,CONVERT(CHAR(4), [Data OUT (No Val#Vuoto)], 100) as Month , [Cod# prodotto] as col1
from dbPratiche
where  [Cod# prodotto] is not null
and [Data OUT (No Val#Vuoto)] < CONVERT(datetime,'2012/01/11')
and Stato='OUT ATTIVATA' or Stato='SOSPESA' 
group by ([Cod# prodotto]),CONVERT(CHAR(4), [Data OUT (No Val#Vuoto)], 100)

) T

pivot
(
  sum(CntCol)
  for Month 
  in([nov],[dec],[jan],[feb],[mar],[apr],[may],[jun],[jul],[aug],[sep],[oct])
) P

But it gave me wrong result.

Please help me.

Upvotes: 0

Views: 35

Answers (2)

C Sharper
C Sharper

Reputation: 8626

Also done through:

select * from
(
select 1 CntCol,CONVERT(CHAR(4), [Data OUT (No Val#Vuoto)], 100) as Month , [Cod# prodotto] as col1
from dbPratiche
where  [Cod# prodotto] is not null
and [Data OUT (No Val#Vuoto)] < CONVERT(datetime,'2012/01/11')
and Stato='OUT ATTIVATA' or Stato='SOSPESA' 
) T

pivot
(
  sum(CntCol)
  for Month 
  in([nov],[dec],[jan],[feb],[mar],[apr],[may],[jun],[jul],[aug],[sep],[oct])
) P

But @simon's answer seems more correct since he is not adding a extra column as i added CntCol in this query.

Upvotes: 0

simon at rcl
simon at rcl

Reputation: 7344

Change your first query to:

select * from
(
select CONVERT(CHAR(4), [Data OUT (No Val#Vuoto)], 100) as Month , [Cod# prodotto] as col1,count([Cod# prodotto]) as codprodotto
from dbPratiche
where  [Cod# prodotto] is not null
and [Data OUT (No Val#Vuoto)] < CONVERT(datetime,'2012/01/11')
and Stato='OUT ATTIVATA' or Stato='SOSPESA' 
group by ([Cod# prodotto]),CONVERT(CHAR(4), [Data OUT (No Val#Vuoto)], 100)

) T

pivot
(
  SUM(codprodotto)
  for Month 
  in([nov],[dec],[jan],[feb],[mar],[apr],[may],[jun],[jul],[aug],[sep],[oct])
) P

I've changed the Pivot function from count to sum. That looks like it should work.

Upvotes: 1

Related Questions