Need query help?

  • Hi all,

    I have a small question. I have a table as foolws:

    column1 column2

    -------- --------

    A 10

    B 10

    C 10

    A 10

    C 10

    B 10

    I need a result like as follows:

    A B C

    -- -- --

    20 20 20

    Can anyone help me for my question?

    Thanks in advance,

    Kannan R

  • SELECT P1.*, (P1.CA + P1.CB + P1.CC) AS SUMTotal

    FROM (SELECT COLUMN1,

    SUM(CASE P.COLUMN1 WHEN 10 THEN P.COLUMN2 ELSE 0 END) AS CA,

    SUM(CASE P.COLUMN2 WHEN 10 THEN P.COLUMN2 ELSE 0 END) AS CB,

    SUM(CASE P.COLUMN3 WHEN 10 THEN P.COLUMN2 ELSE 0 END) AS CC,

    FROM Pivot AS P

    GROUP BY P.COLUM1) AS P1

    GO

  • CREATE TABLE t1 (

    c1 CHAR(1),

    c2 INT)

    GO

    INSERT INTO t1

    VALUES ('A',10)

    INSERT INTO t1

    VALUES ('A',22)

    INSERT INTO t1

    VALUES ('B',10)

    INSERT INTO t1

    VALUES ('B',12)

    INSERT INTO t1

    VALUES ('C',10)

    INSERT INTO t1

    VALUES ('C',30)

    INSERT INTO t1

    VALUES ('C',10)

    INSERT INTO t1

    VALUES ('A',10)

    SELECT SUM(CASE

    WHEN c1 = 'A' THEN c2 ELSE 0 END) AS a,

    SUM(CASE

    WHEN c1 = 'B' THEN c2 ELSE 0 END) AS b,

    SUM(CASE

    WHEN c1 = 'C' THEN c2 ELSE 0 END) AS c

    FROM t1

    [font="Verdana"]Markus Bohse[/font]

  • Thank you very much.

  • On 2000 you can use a case in a sum like:

    SELECT

    SUM(CASE t.column1 WHEN 'A' THEN t.column2 ELSE 0 END) AS [A],

    SUM(CASE t.column1 WHEN 'B' THEN t.column2 ELSE 0 END) AS ,

    SUM(CASE t.column1 WHEN 'C' THEN t.column2 ELSE 0 END) AS [C]

    FROM myTable AS t

    on 2005 you can use pivot like:

    SELECT [A]

    ,

    , [C]

    FROM myTable p PIVOT ( SUM(column2) FOR column1 IN ( [A], , [C] ) ) AS pivotTable

    For both examples the base tables:

    create table myTable (column1 char, column2 int)

    insert into myTable values ('A', 10)

    insert into myTable values ('B', 10)

    insert into myTable values ('C', 10)

    insert into myTable values ('A', 10)

    insert into myTable values ('B', 10)

    insert into myTable values ('C', 10)

    Regards,

    Andras


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • Thanks Andras Belokosztolszki.I got that.

    I have Some of questions.

    1. What is Dynamic SQL?

    2. Is there any diffrences between Dynamic SQL and Stored Procedure?

    3. Can i perform this operation using Dynamic SQL?

    Any ideas? Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

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