April 12, 2010 at 7:56 pm
I have a table
CREATE TABLE [dbo].[HUYEN](
[char](10) NOT NULL,
[int] NULL,
[VALUE] [int] NULL,
CONSTRAINT [PK_HUYEN] PRIMARY KEY CLUSTERED
(
ASC
)
) ON [PRIMARY]
With data
INSERT INTO HUYEN(,CODE,VALUE)
SELECT 'a' AS KEY,1 AS CODE,10 AS VALUE UNION ALL
SELECT 'b' AS KEY,1 AS CODE,11 AS VALUE UNION ALL
SELECT 'c' AS KEY,2 AS CODE,12 AS VALUE UNION ALL
SELECT 'd' AS KEY,2 AS CODE,13 AS VALUE UNION ALL
SELECT 'e' AS KEY,3 AS CODE,14 AS VALUE UNION ALL
SELECT 'f' AS KEY,3 AS CODE,15 AS VALUE UNION ALL
and i have a SQL
select
SUM(CASE WHEN CODE = 1 AND ='a' THEN VALUE*1 ELSE 0 END),
SUM(CASE WHEN CODE = 1 AND ='b' THEN VALUE*2 ELSE 0 END),
SUM(CASE WHEN CODE = 2 AND ='c' THEN VALUE*3 ELSE 0 END),
SUM(CASE WHEN CODE = 2 AND ='d' THEN VALUE*4 ELSE 0 END),
SUM(CASE WHEN CODE = 3 AND ='e' THEN VALUE*5 ELSE 0 END),
SUM(CASE WHEN CODE = 3 AND ='f' THEN VALUE*6 ELSE 0 END)
from HUYEN
You see, CODE = 1 repeat twice, CODE = 2 repeat twice...
How to rewrite my SQL with CODE = 1 apear one time:-D
April 12, 2010 at 8:35 pm
Are you sure you want to do that?
As you have it setup now, each on would display as a different column. Rewriting the query would change it so that "1" would be a single column but with mixed results.
Rewriting this statement is straightforward
case when ... then... when... then... else... end
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 12, 2010 at 8:39 pm
:-D, in this case, i want to get 2 column with CODE=1 but case when CODE=1 one time, huhu
April 12, 2010 at 8:44 pm
nguyennd (4/12/2010)
:-D, in this case, i want to get 2 column with CODE=1 but case when CODE=1 one time, huhu
I'm not sure I understand. Your statement is self-contradicting.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply