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
 separate column

Author  Topic 

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-03-21 : 04:43:54
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

52326 Posts

Posted - 2013-03-21 : 04:54:42
[code]
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
[/code]

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

420 Posts

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-21 : 05:18:26
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 - 2013-03-22 : 19:42:58
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 - 2013-03-23 : 03:33:02
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
   

- Advertisement -