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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Help on Update
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

julius.delorino
Starting Member

Philippines
29 Posts

Posted - 10/05/2012 :  00:07:12  Show Profile  Reply with Quote


Source Table:
TERMINALID TERMINALDESC ERRORCODE ERRORDESC TOTALCOUNT
Terminal_1 Terminal_Main 0000 Approved 72
Terminal_1 Terminal_Main 0051 Insufficient Funds 1
Terminal_1 Terminal_Main 0061 ExceededDailyAmtLimit 1
Terminal_2 Terminal_Site 0000 Approved 17
Terminal_2 Terminal_Site 0005 UnabletoProcess 3
Terminal_2 Terminal_Site 0025 InvalidCurrentAcct 1



Desired Output:
ERRORCODE ERRORDESC Terminal_1 Terminal_2
0000 Approved 72 17
0051 Insufficient Funds 1 0
0061 ExceededDailyAmtLimit 1 0
0025 InvalidCurrentAcct 0 1
0005 UnabletoProcess 0 3


-Please help me attain this kind of result, your help is greatly appreciated,thank you. more power.

tkizer
Almighty SQL Goddess

USA
36845 Posts

Posted - 10/05/2012 :  00:51:28  Show Profile  Visit tkizer's Homepage  Reply with Quote
We can't view your image.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

julius.delorino
Starting Member

Philippines
29 Posts

Posted - 10/05/2012 :  01:01:13  Show Profile  Reply with Quote


-please see image for reference,thank you.
Go to Top of Page

julius.delorino
Starting Member

Philippines
29 Posts

Posted - 10/05/2012 :  02:35:19  Show Profile  Reply with Quote
Please help me with this problem,thank you.
Go to Top of Page

malpashaa
Constraint Violating Yak Guru

Saudi Arabia
257 Posts

Posted - 10/05/2012 :  03:28:19  Show Profile  Reply with Quote
Try something like this:


SELECT ERRORCODE, ERRORDESC,
       SUM(CASE WHEN TERMINALID = 'Terminal_1' THEN TOTALCOUNT ELSE 0 END) AS Terminal_1,
       SUM(CASE WHEN TERMINALID = 'Terminal_2' THEN TOTALCOUNT ELSE 0 END) AS Terminal_2
  FROM [Source Table]
 GROUP BY ERRORCODE, ERRORDESC




For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha
Go to Top of Page

julius.delorino
Starting Member

Philippines
29 Posts

Posted - 10/05/2012 :  03:41:06  Show Profile  Reply with Quote
thank you for your help sir,but your solution did not generate the desired output
,the total count of each terminal should from source table with corresponding terminal and errorcode
please see the image for reference thank you.

Edited by - julius.delorino on 10/05/2012 03:46:52
Go to Top of Page

malpashaa
Constraint Violating Yak Guru

Saudi Arabia
257 Posts

Posted - 10/05/2012 :  12:30:17  Show Profile  Reply with Quote
quote:
Originally posted by julius.delorino

thank you for your help sir,but your solution did not generate the desired output
,the total count of each terminal should from source table with corresponding terminal and errorcode
please see the image for reference thank you.



The query generates the desired output based on your sample data:


USE tempdb;

IF OBJECT_ID(N'tempdb..SourceTable', N'U') IS NULL
   SELECT T.TERMINALID, T.TERMINALDESC, T.ERRORCODE, T.ERRORDESC, T.TOTALCOUNT
     INTO SourceTable
     FROM (VALUES('Terminal_1', 'Terminal_Main', '0000', 'Approved', 72),
                 ('Terminal_1', 'Terminal_Main', '0051', 'Insufficient Funds', 1),
                 ('Terminal_1', 'Terminal_Main', '0061', 'ExceededDailyAmtLimit', 1),
                 ('Terminal_2', 'Terminal_Site', '0000', 'Approved', 17),
                 ('Terminal_2', 'Terminal_Site', '0005', 'UnabletoProcess', 3),
                 ('Terminal_2', 'Terminal_Site', '0025', 'InvalidCurrentAcct', 1)
          ) AS T(TERMINALID, TERMINALDESC, ERRORCODE, ERRORDESC, TOTALCOUNT);

SELECT ERRORCODE, ERRORDESC,
       SUM(CASE WHEN TERMINALID = 'Terminal_1' THEN TOTALCOUNT ELSE 0 END) AS Terminal_1,
       SUM(CASE WHEN TERMINALID = 'Terminal_2' THEN TOTALCOUNT ELSE 0 END) AS Terminal_2
  FROM SourceTable
 GROUP BY ERRORCODE, ERRORDESC;




For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha
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.16 seconds. Powered By: Snitz Forums 2000