SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 separate column
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

peace
Constraint Violating Yak Guru

415 Posts

Posted - 03/21/2013 :  04:43:54  Show Profile  Reply with Quote
column1 column2 column3 column4
car blue driver1 pass
car blue driver2 pass
car red driver3 failed

how can i separate out the column4 into as below. is it possible?

column1 column2 column3 column4 PassTotal FailedTotal
car blue driver1 pass 1 0
car blue driver2 pass 1 0
car red driver3 failed 0 1

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/21/2013 :  04:54:42  Show Profile  Reply with Quote

SELECT *,CASE WHEN column4 = 'pass' THEN 1 ELSE 0 END AS PassTotal,CASE WHEN column4='failed' THEN 1 ELSE 0 END AS FailedTotal
FROM TAble


Didnt understand need of this requirement though. Here you're actually creating redundant columns out of same column value (column4)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

peace
Constraint Violating Yak Guru

415 Posts

Posted - 03/21/2013 :  05:02:28  Show Profile  Reply with Quote
is actually to count the total.
Not to set 1 or 0.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/21/2013 :  05:18:26  Show Profile  Reply with Quote
then why not do it like this?

SELECT COlumn1,Colummn2,
SUM(CASE WHEN column4 = 'pass' THEN 1 ELSE 0 END) AS PassTotal,
SUM(CASE WHEN column4='failed' THEN 1 ELSE 0 END) AS FailedTotal
FROM TAble
GROUP BY COlumn1,Column2


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

ashabc
Starting Member

2 Posts

Posted - 03/22/2013 :  19:42:58  Show Profile  Reply with Quote
My question is similar. So, I did not open a new topic. I have the following two SQL statements. Individually they return one column and returns the correct value.

How can I join them together with two columns as the output with respective column headers?


SELECT SUM(LASTVAL) AS icon_hq_ext FROM table1 where (MACHINE = '192.168.50.111' OR MACHINE = '192.168.50.112') AND (INSTANCE = 'LtmVsConn@/Common/icon.rfs.nsw.gov.au_Ext_S_443' OR INSTANCE = 'LtmVsConn@/Common/icon.rfs.nsw.gov.au_Ext_T_443')

SELECT SUM(LASTVAL) AS icon_dr_ext FROM table1 where (MACHINE = '192.168.64.111' OR MACHINE = '192.168.64.112') AND (INSTANCE = 'LtmVsConn@/Common/icon.rfs.nsw.gov.au_Ext_443')



Go to Top of Page

ashabc
Starting Member

2 Posts

Posted - 03/23/2013 :  03:33:02  Show Profile  Reply with Quote
OK, I got some work around. I have added a dummy column in the beginning and then put these two queries separated by a comma and it works. However, if I put only these two statements separated by a comma, it does not work (throws some syntax error)

SELECT 'LA' as Location, (SELECT SUM(LASTVAL) FROM [ArgentATLive].[dbo].[ARGSOFT_SN_PRD_MOTHER] where (MACHINE = '192.168.50.111' OR MACHINE = '192.168.50.112') AND (INSTANCE = 'LtmVsConn@/Common/icon.rfs.nsw.gov.au_Ext_S_443' OR INSTANCE = 'LtmVsConn@/Common/icon.rfs.nsw.gov.au_Ext_T_443')) AS icon_hq_ext, (SELECT SUM(LASTVAL) FROM [ArgentATLive].[dbo].[ARGSOFT_SN_PRD_MOTHER] where (MACHINE = '192.168.64.111' OR MACHINE = '192.168.64.112') AND (INSTANCE = 'LtmVsConn@/Common/icon.rfs.nsw.gov.au_Ext_443')) AS icon_dr_ext


Would be nice to have only two columns instead of three (first one being dummy)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000