CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
C Sharper
C Sharper

Reputation: 8626

Pivoting a column and showing data according to count

I have following table:

enter image description here

I wanted to pivot month column and show count of data according to groups of >10,<=06,etc.

Means wanted to show how many counts for >10 in Jun, jan

As folows:

Gruppi Min (GG Flusso/Decorrenza-->Out)   Jan Feb Mar Apr May Jun

>10                                        5   2  1   0    8  2

<=06                                       2  8   1   7    9  33

I Tried as follows:

SELECT * 
FROM   (SELECT CONVERT(CHAR(3), [data out (no val#vuoto)], 0) month, 
               ( [gruppi min (gg flusso/decorrenza-->out)] ) 
        FROM   dbpratiche 
        WHERE  compagnia = 'GENERALI INA ASSITALIA' 
               AND stato = 'OUT ATTIVATA' 
               AND [data out (no val#vuoto)] > '01-01-2012') TLB 
       PIVOT ( Count([gruppi min (gg flusso/decorrenza-->out)] ) 
             FOR tlb.month IN([jan], 
                              [feb], 
                              [mar], 
                              [apr], 
                              [may], 
                              [jun]) )PVT

but given me following result:

enter image description here

Its not grouping according to >10,<=06,etc. and showing overall count.

where i am making mistake?

Please help me.

Upvotes: 1

Views: 95

Answers (3)

Jaques
Jaques

Reputation: 2287

This worked

SELECT *
FROM 
(SELECT 1 CntCol, [Gruppi Min (GG Flusso/Decorrenza-->Out)], [month]
FROM #X) TLB
PIVOT
(
COUNT (CntCol)
FOR TLB.[month] IN
( [jan],[feb],[mar],[apr],[may],[jun] )
) AS pvt

You need to add a column that should be counted.

Look at the complex example here: http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Change your code to this:

SELECT * 
FROM   (SELECT 1 CntCol, CONVERT(CHAR(3), [data out (no val#vuoto)], 0) month, 
               ( [gruppi min (gg flusso/decorrenza-->out)] ) 
        FROM   dbpratiche 
        WHERE  compagnia = 'GENERALI INA ASSITALIA' 
               AND stato = 'OUT ATTIVATA' 
               AND [data out (no val#vuoto)] > '01-01-2012') TLB 
       PIVOT ( Count(CntCol) 
             FOR tlb.month IN([jan], 
                              [feb], 
                              [mar], 
                              [apr], 
                              [may], 
                              [jun]) )PVT

Upvotes: 3

Katy
Katy

Reputation: 296

you can add an autoincrement field to your dbPratiche table(for example id), and then try this code:

    select * from 
    (
       select [id], [month],  [gruppi]   
       from dbPratiche 
    )as s
    pivot
    (
       COUNT ([id])
       for month in(jun, sep, jul, may, aug)
    )as PVT

Upvotes: 2

VIPUL PARMAR
VIPUL PARMAR

Reputation: 292

check this may help you

SELECT *
FROM (
    SELECT 
        year(invoiceDate) as [year],left(datename(month,invoicedate),3)as [month], 
        InvoiceAmount as Amount 
    FROM Invoice
) as s
PIVOT
(
    SUM(Amount)
    FOR [month] IN (jan, feb, mar, apr, 
    may, jun, jul, aug, sep, oct, nov, dec)
)AS pivot

Upvotes: 1

Related Questions