October 30, 2007 at 3:09 am
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
October 30, 2007 at 3:19 am
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
October 30, 2007 at 3:25 am
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]
October 30, 2007 at 3:33 am
Thank you very much.
October 30, 2007 at 3:34 am
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
October 30, 2007 at 3:43 am
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