Help on Update

Question

Post reply

Help on Update

jdelorino

Valued Member

Points: 73
More actions
October 4, 2012 at 11:33 pm

#263417

http://i45.tinypic.com/14xmgza.jpg
-please see image for reference,please help me to attain this kind of result thank you,
and more power.

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

jdelorino Valued Member Points: 73 More actions · Answer 1

Source Table:

TERMINALIDTERMINALDESCERRORCODE 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 to attain the result,thank you.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

-- CROSSTAB query

SELECT ERRORCODE, ERRORDESC,

Terminal_1 = MAX(CASE WHEN TERMINALID = 'Terminal_1' THEN TOTALCOUNT ELSE 0 END),

Terminal_2 = MAX(CASE WHEN TERMINALID = 'Terminal_2' THEN TOTALCOUNT ELSE 0 END)

FROM #SourceTable

GROUP BY ERRORCODE, ERRORDESC

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jdelorino Valued Member Points: 73 More actions · Answer 3

thank you sir for your reply,but the column for totalcount displays only 0 not the actual value of the totalcount column.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

jdelorino (10/5/2012)
thank you sir for your reply,but the column for totalcount displays only 0 not the actual value of the totalcount column.

It works for me:

CREATE TABLE #SourceTable (

TERMINALID VARCHAR(15),

TERMINALDESC VARCHAR(15),

ERRORCODE CHAR(4), ERRORDESC VARCHAR(50), TOTALCOUNT INT

)

INSERT INTO #SourceTable (TERMINALID, TERMINALDESC, ERRORCODE, ERRORDESC, TOTALCOUNT)

SELECT 'Terminal_1', 'Terminal_Main', '0000', 'Approved', 72 UNION ALL

SELECT 'Terminal_1', 'Terminal_Main', '0051', 'Insufficient Funds', 1 UNION ALL

SELECT 'Terminal_1', 'Terminal_Main', '0061', 'ExceededDailyAmtLimit', 1 UNION ALL

SELECT 'Terminal_2', 'Terminal_Site', '0000', 'Approved', 17 UNION ALL

SELECT 'Terminal_2', 'Terminal_Site', '0005', 'UnabletoProcess', 3 UNION ALL

SELECT 'Terminal_2', 'Terminal_Site', '0025', 'InvalidCurrentAcct', 1

-- CROSSTAB query

SELECT ERRORCODE, ERRORDESC,

Terminal_1 = MAX(CASE WHEN TERMINALID = 'Terminal_1' THEN TOTALCOUNT ELSE 0 END),

Terminal_2 = MAX(CASE WHEN TERMINALID = 'Terminal_2' THEN TOTALCOUNT ELSE 0 END)

FROM #SourceTable

GROUP BY ERRORCODE, ERRORDESC

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lokesh Vij SSChampion Points: 10836 More actions · Answer 5

Here is the modified version with totalcount values:

SELECT ERRORCODE, ERRORDESC,

Terminal_1 = MAX(CASE WHEN TERMINALID = 'Terminal_1' THEN TOTALCOUNT ELSE 0 END),

Terminal_2 = MAX(CASE WHEN TERMINALID = 'Terminal_2' THEN TOTALCOUNT ELSE 0 END),

TOTALCOUNT = SUM(TOTALCOUNT)

FROM #SourceTable

GROUP BY ERRORCODE, ERRORDESC

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 6

Lokesh Vij (10/5/2012)
Here is the modified version with totalcount values:
SELECT ERRORCODE, ERRORDESC,
Terminal_1 = MAX(CASE WHEN TERMINALID = 'Terminal_1' THEN TOTALCOUNT ELSE 0 END),
Terminal_2 = MAX(CASE WHEN TERMINALID = 'Terminal_2' THEN TOTALCOUNT ELSE 0 END),
TOTALCOUNT = SUM(TOTALCOUNT)
FROM #SourceTable
GROUP BY ERRORCODE, ERRORDESC

Where did this Totalcount column come from, the original requirement doesnt specify this and ChrisM's solution generates the correct values as per the desired output.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Lokesh Vij SSChampion Points: 10836 More actions · Answer 7

Jason-299789 (10/5/2012)
Lokesh Vij (10/5/2012)
Here is the modified version with totalcount values:
SELECT ERRORCODE, ERRORDESC,
Terminal_1 = MAX(CASE WHEN TERMINALID = 'Terminal_1' THEN TOTALCOUNT ELSE 0 END),
Terminal_2 = MAX(CASE WHEN TERMINALID = 'Terminal_2' THEN TOTALCOUNT ELSE 0 END),
TOTALCOUNT = SUM(TOTALCOUNT)
FROM #SourceTable
GROUP BY ERRORCODE, ERRORDESC
Where did this Totalcount column come from, the original requirement doesnt specify this and ChrisM's solution generates the correct values as per the desired output.

As per the query raised by the user, it seems totalcount is also required.

but the column for totalcount displays only 0

I agree ChrisM's solution is perfect!

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

Lokesh Vij SSChampion Points: 10836 More actions · Answer 8

I might be wrong as well. Let jdelorino confirm!

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

Lokesh Vij (10/5/2012)
I might be wrong as well. Let jdelorino confirm!

Exactly.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jdelorino Valued Member Points: 73 More actions · Answer 10

Thank you for your reply,your help is greatly appreciated, more power.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

jdelorino (10/5/2012)
Thank you for your reply,your help is greatly appreciated, more power.

Can you please post the solution you are using? It provides feedback to those who have taken time to help you, and may help others who have the same problem in the future. Thanks.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Lokesh Vij SSChampion Points: 10836 More actions · Answer 12

jdelorino (10/5/2012)
Thank you for your reply,your help is greatly appreciated, more power.

Please post the correct solution and which query worked for you. This might help us.

~ Lokesh Vij

Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com[/url]

Follow me @Twitter