Post reply

SQL Query

  • May 13, 2015 at 7:19 pm

    #303861

    Hi Guys,

    I have

    Issues | Category | Date

    I1 | A | 1/1/2015

    I2 | A | 2/2/2015

    I3 | B | 2/1/2015

    I4 | C | 3/3/2015

    I5 | B | 4/3/2015

    I6 | A | 5/4/2015

    I want to convert to

    A | B | C

    JAN 1 | 1 | 0

    FEB 1 | 0 | 0

    MAR 0 | 1 | 1

    APR 1 | 0 | 0

    Where numbers under neath A, B, C are the count that falls in the particular month.

    Can anyone please help,

    Thanks in advance,

    Saumil.

  • May 13, 2015 at 8:00 pm

    #1797732

    DECLARE @issues TABLE(Issues varchar(5), Category char(1), IssueDate date);

    INSERT @issues VALUES

    ('I1','A','1/1/2015'),

    ('I2','A','2/2/2015'),

    ('I3','B','2/1/2015'),

    ('I4','C','3/3/2015'),

    ('I5','B','4/3/2015'),

    ('I6','A','5/4/2015');

    SELECT * FROM @issues;

    SELECT

    mo.m,

    A = SUM(CASE WHEN Category = 'A' THEN 1 ELSE 0 END),

    B = SUM(CASE WHEN Category = 'B' THEN 1 ELSE 0 END),

    C = SUM(CASE WHEN Category = 'C' THEN 1 ELSE 0 END)

    FROM @issues i

    CROSS APPLY (VALUES (1,'JAN'),(2,'FEB'),(3,'MAR'),(4,'APR')) mo(mi,m)

    WHERE MONTH(IssueDate) = mo.mi

    GROUP BY m;

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

  • May 13, 2015 at 8:43 pm

    #1797739

    Perfect. That's what I was looking for.

    HIGHLY appreciate your help, ALAN.

    Thanks once again, Saumil.:-)

  • May 14, 2015 at 4:07 am

    #1797778

    Or look at the PIVOT() function

Viewing 4 posts - 1 through 3 (of 3 total)

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