Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Columns ins SQL

Author  Topic 

jblobner
Starting Member

3 Posts

Posted - 2009-06-24 : 14:17:08
I have a query that returns two data sets.
select count (*) from pfvcfnh where vcfabr = 'AUDIT' and vcsta1 = 'A' and vcshdt > 20090101 and vcshdt < 20090617 union
select count (*) from pfvcfnh where vcfabr = 'AUDIT' and vcsta1 = '01' and vcshdt > 20090101 and vcshdt < 20090617;

The result looks like this.

00001
9479
19254

00001 is the column heading
9479 & 19245 are data.

How can I rewrite the query so the output is in 2 columns?

Example
00001 00002
9478 19254

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-24 : 14:22:18
[code]

SELECT [00001] = SUM(CASE WHEN vcsta1 = 'A'
THEN 1 ELSE 0
END)
,[00002] = SUM(CASE WHEN and vcsta1 = '01'
THEN 1 ELSE 0 END)
FROM pfvcfnh

WHERE
vcfabr = 'AUDIT'
and vcshdt > 20090101 and vcshdt < 20090617

[/code]


Jim
Go to Top of Page

jblobner
Starting Member

3 Posts

Posted - 2009-06-24 : 14:38:11
Thanks for the speedy reply. I ran the query, but it returned and error

14:36:23 [SELECT - 0 row(s), 0.125 secs] [Error Code: -104, SQL State: 42601] [SQL0104] Token [ was not valid. Valid tokens: ( + * - ? : DAY RRN CASE CAST CHAR DATE DAYS HASH HOUR LEFT RANK.
... 1 statement(s) executed, 0 row(s) affected, execution time 0.125 sec

Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-06-24 : 14:55:13
Are you querying a MS SQL Server database?

Jim
Go to Top of Page

jblobner
Starting Member

3 Posts

Posted - 2009-06-24 : 17:29:49
No it's an AS400 database.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-25 : 00:04:34
try dbforums.com. This is a MS SQL Server forum.


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
   

- Advertisement -