How to write Transact-SQL command to get this

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

SQL Server 2000 Forums

Transact-SQL (2000)

How to write Transact-SQL command to get this

Author

Topic

chungpn
Starting Member

8 Posts

Posted - 2008-12-03 : 20:05:58

Hello, everybody!
I have a sql server table contains this data:

EmpCode DeptCode FullName N01 N02 N03 N04 N05
---------------------------------------------------------------------------------------
QT351 KHHC ABC X X Y X C
QT555 KTMB GHJ Y Z X C X
QT351 KHHC ABC X X Y X C
QT555 KTMB GHJ Y Z X C X
--------------------------------------------------------------------------------------
Can you please help me to write a Transact-SQL command to count and get the result as follow:

EmpCode DeptCode FullName X Y Z C
---------------------------------------------------------------------------------------
QT351 KHHC ABC 6 2 0 2
QT555 KTMB GHJ 4 2 2 2
--------------------------------------------------------------------------------------
thank you in advance!

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-03 : 20:44:54

select EmpCode,DeptCode,FullName,
sum(case when N01 = 'X' then 1 else 0 end + case when N02 ='X' then 1 else 0 end + case when N03 ='X' then 1 else 0 end + case when N04 ='X' then 1 else 0 end + case when N05 ='X' then 1 else 0 end)as X,
sum(case when N01 = 'Y' then 1 else 0 end + case when N02 ='Y' then 1 else 0 end + case when N03 ='Y' then 1 else 0 end + case when N04 ='Y' then 1 else 0 end + case when N05 ='Y' then 1 else 0 end)as Y,
sum(case when N01 = 'Z' then 1 else 0 end + case when N02 ='Z' then 1 else 0 end + case when N03 ='Z' then 1 else 0 end + case when N04 ='Z' then 1 else 0 end + case when N05 ='Z' then 1 else 0 end)as Z,
sum(case when N01 = 'C' then 1 else 0 end + case when N02 ='C' then 1 else 0 end + case when N03 ='C' then 1 else 0 end + case when N04 ='C' then 1 else 0 end + case when N05 ='C' then 1 else 0 end)as C
from table
group by EmpCode,DeptCode,FullName

chungpn
Starting Member

8 Posts

Posted - 2008-12-03 : 23:03:39

Thks sodeep!
This solve the problem.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 00:16:07

[code]SELECT EmpCode, DeptCode, FullName,
SUM(CASE WHEN Col='X' THEN 1 ELSE 0 END) AS X,
SUM(CASE WHEN Col='Y' THEN 1 ELSE 0 END) AS Y,
SUM(CASE WHEN Col='Z' THEN 1 ELSE 0 END) AS Z,
SUM(CASE WHEN Col='C' THEN 1 ELSE 0 END) AS C
FROM
(
SELECT EmpCode, DeptCode, FullName,N01 AS Col
FROM YourTable
UNION ALL
SELECT EmpCode, DeptCode, FullName,N02
FROM YourTable
UNION ALL
SELECT EmpCode, DeptCode, FullName,N03
FROM YourTable
UNION ALL
SELECT EmpCode, DeptCode, FullName,N04
FROM YourTable
UNION ALL
SELECT EmpCode, DeptCode, FullName,N05
FROM YourTable
)t
GROUP BY EmpCode, DeptCode, FullName[/code]

chungpn
Starting Member

8 Posts

Posted - 2008-12-04 : 04:57:32

thks,visakh16!
Both of you are number 1.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 06:32:31

welcome

robvolk
Most Valuable Yak

15732 Posts

Posted - 2008-12-04 : 07:10:16

Another option, if you're interested:

select EmpCode,DeptCode,FullName,
sum(5-len(replace(N01+N02+N03+N04+N05, 'X', ''))) as X,
sum(5-len(replace(N01+N02+N03+N04+N05, 'Y', ''))) as Y,
sum(5-len(replace(N01+N02+N03+N04+N05, 'Z', ''))) as Z,
sum(5-len(replace(N01+N02+N03+N04+N05, 'C', ''))) as C
from table
group by EmpCode,DeptCode,FullName

Warning: not tested.

chungpn
Starting Member

8 Posts

Posted - 2008-12-04 : 09:25:58

Dear, robvolk!
I have tested your suggest but this is not correct.
Thank you anywway.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-04 : 09:33:30

quote:
Originally posted by chungpn

Dear, robvolk!
I have tested your suggest but this is not correct.
Thank you anywway.

doesnt seem to contain any problem, what was error you got?

chungpn
Starting Member

8 Posts

Posted - 2008-12-04 : 10:21:11

return incorrect value

kms313_2002
Starting Member

1 Post

Posted - 2008-12-17 : 05:19:56

--------booking----------------------------------------------------------------------------------------------------------
select journey_date,booking_date,trnno,cls,lap_src_code,lap_dstn_code,
Num_OF_CNF_psgn=sum(case when status_0 = 'CNF' then 1 else 0 end),
Num_OF_RAC_psgn=sum(case when status_0 = 'RAC' then 1 else 0 end),
Num_OF_WL_psgn=sum(case when status_0 = 'WL' then 1 else 0 end) into #temp1
from dw_bmb_fi_pnr where journey_date = '2008-01-01' and trnno = '2901' and fi_txn_type ='B'
and cls='1A'
group by journey_date,booking_date,lap_src_code,lap_dstn_code,trnno,cls
order by 1 asc,4 asc

--------------------------------------------------------------------------------------------------------------------------
-------cancelling---------------------------------------------------------------------------------------------------------

select A.journey_date,B.booking_date,trnno,cls,lap_src_code,lap_dstn_code,canc_psgn= count(*) into #temp2
from dw_bmb_fi_pnr as A,dw_pnr_session as B
where A.journey_date ='2008-01-01' and trnno = '2901' and fi_txn_type ='C' and cls='1A' and A.pnrno = B.pnrno
--and A.sess_form_summary like ('Can %')
and A.journey_date = B.journey_date and A.sess_form_summary=b.sess_form_summary
group by A.journey_date,B.booking_date,trnno,cls,lap_src_code,lap_dstn_code
---------------------------------------------------------------------------------------------------------------------------------
------Combined--------------------------------------------------------------------------------------------------------------------

select A.journey_date,B.booking_date,A.trnno,A.cls,A.lap_src_code,A.lap_dstn_code,Num_OF_CNF_psgn,Num_OF_RAC_psgn,Num_OF_WL_psgn,canc_psgn
from #temp1 as A, #temp2 as B where A.journey_date= B.journey_date and A.trnno=B.trnno and A.booking_date*=B.booking_date
and A.cls=b.cls and A.lap_src_code*=B.lap_src_code and A.lap_dstn_code*=B.lap_dstn_code

In combined querry we are not getting data from both the tables #temp1 ,#temp2 ,we are just getting data from one table one time by outer join ... i want them to be in one table ...pl suggest how it can be possible

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 05:37:09

Dont hijack threads. please post your question as new thread, by doing so your question will be viewed by more ppl and you'll get quick solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources