0

I am working with an outsource developed database,

They have given two columns -- As an example

Charge Value 
Test   0.23 
Jop    0.10 
Bob    0.15

Well I want to query the table so that I get the following output from these two columns.

Charge Value Bob Value Total
Test   0.23  0.15      0.38

These are obviously linked together by a primary key. So I want to use the two columns to represent data in four columns. I was just wondering if this is possible, or do I have to perform multiple queries.

Hope that is enough information!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
James Reeves
  • 97
  • 1
  • 13
  • Is your result example correct? Where is the score for Jop? – gotqn Sep 06 '12 at 11:52
  • @Joro It was just an example, So i could do an where cause on the charge so it only contain 'test', but also showed the bob value, and the total of the two values. – James Reeves Sep 06 '12 at 12:01
  • What I mean is your example is misleading (not logical). In order to get some help you need to correct it. There is no any sense in your output result - Charge-Value-Bob-Value-Total ... Do you mean the result columns need to be Test-Jop-Bob and Total? – gotqn Sep 06 '12 at 12:08
  • I think you have misunderstood the question. The first table is an example. There are charges called test jop bob (For example) and each of them are linked to a value. I need to do a query to to return values depending on the choice of charges, so I return a value from the row charge, called test with the value of 0.23, but in the same query I want to return the value of bob (From the charge row), and I want to return the value of both of them added up. I want to ignore Jop as it is not needed. Does that explain it better. – James Reeves Sep 06 '12 at 12:19
  • That's ok. Explain to me why you have two columns "Value" - it is not possible to have columns with duplicate names in T-SQL and explain to me why in the result table there is no results for Jop but its value is included in the Total column? – gotqn Sep 06 '12 at 12:23
  • Well one value is the test value, and another value column is the bob value. I want to get both values depending on the selection from the charge row. Because we are not searching for Jop, it was there to indicate that the result depends on the charge. So you have a where clause like this where charge != 'Jop' – James Reeves Sep 06 '12 at 12:32

1 Answers1

1

You can do this with conditional aggregation:

select 'Test' as Charge,
       max(case when t.charge = 'Test' then value end) as Value,
       max(case when t.charge = 'Bob' then value end) as "Bob Value",
       sum(case when t.charge in ('Test', 'Bob') then value end) as Total
from t
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786