Column data want to convert in columns

Feroz Khan

Dear all,

I want to retrive column data into columns like below sql query and result snap the issue is result not coming date wise each value showing seprate seprate date multiple times.

i want one date value in same row, not in seprate seprate row.

Please need your support.

select Branch, Account, PDGNumber, trandate, grandvalue grand,
cast(feesvalue as decimal(5,2)) fees,
max(case when Channel = 'Visa' then Credit_amount end) Visa,
max(case when Channel = 'Mada' then Credit_amount end) Mada,
max(case when Channel = 'Master' then Credit_amount end) Masters,
max(case when Channel = 'GCC' then Credit_amount end) GCC,
max(case when Channel = 'Amex' then Credit_amount end) Amex
from Bank_Statement where channel in ('Visa','Mada','Master','GCC','Amex')
group by branch, account, trandate, feesvalue, PDGNumber, GrandValue

Result

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke3y ago
Accepted answer
Remember, ANY column which is NOT having aggregate function (min, max, avg, count, sum, etc.) applied should be mentioned in GROUP BY clause.

As such, you should mention, Branch, Account, PDGNumber, trandate in group by. But you only have mentioned trandate.

Hope you get the point.
+1
Nitin Sontakke3y ago
The error is because of 'GrandValue' column. You have already done the conversion for other columns using cast. Do the same to this column.
+1
Feroz Khan3y ago
Dear Mr. Nitin,

Thnak you for your reply and yes you are right i did same but only issue is varchar want to convert numeric not found any proper solution.

below code and result snap for your refrence.

0
Feroz Khan3y ago
Dear All,

Thank you for your reply.

i am trying same below code issue my grandvalue and others columns datatype is nvarchar because there is some string value as well.

and i am trying to convert with double or decimal but showing error below for your refrence.

select Branch, Account, PDGNumber, trandate, sum(cast(GrandValue as decimal(5,2))) grand,
sum(cast(Feesvalue as decimal(5,2))) fees
from (
select Branch, Account, PDGNumber, trandate, grandvalue grand,
cast(feesvalue as decimal(5,2)) fees,
max(case when Channel = 'Visa' then Credit_amount end) Visa,
max(case when Channel = 'Mada' then Credit_amount end) Mada,
max(case when Channel = 'Master' then Credit_amount end) Masters,
max(case when Channel = 'GCC' then Credit_amount end) GCC,
max(case when Channel = 'Amex' then Credit_amount end) Amex
from Bank_Statement where channel in ('Visa','Mada','Master','GCC','Amex')
group by branch, account, trandate, Feesvalue, PDGNumber, GrandValue
) t
group by trandate
order by trandate

Error:

0

Mohammad Hussain3y ago

Please try this:

Select
trandate
,Sum(grand) as grand
,Sum(fees) as fees
,Sum(visa) as visa
,Sum(mada) as mada
,Sum(masters) as masters
,Sum(gcc) as gc
,Sum(amex) as amex
(
select Branch, Account, PDGNumber, trandate, grandvalue grand,
   cast(feesvalue as decimal(5,2)) fees, 
  max(case when Channel = 'Visa' then Credit_amount end) Visa,
  max(case when Channel = 'Mada' then Credit_amount end) Mada,
  max(case when Channel = 'Master' then Credit_amount end) Masters,
  max(case when Channel = 'GCC' then Credit_amount end) GCC,
    max(case when Channel = 'Amex' then Credit_amount end) Amex
  from Bank_Statement where channel in ('Visa','Mada','Master','GCC','Amex')
  group by branch, account, trandate,  feesvalue, PDGNumber, GrandValue) bs
Group By trandate
Order By trandate

This is the output. Is this what you want. Thanks…

Nitin Sontakke3y ago
The solution to this problem is really very simple, all you have to do is write one more query on top of this.

For example:

select trandate, sum(grand) grand, -- sum ALL your columns here and give them same name....

from

(

select Branch, Account, PDGNumber, trandate, grandvalue grand,
cast(feesvalue as decimal(5,2)) fees,
max(case when Channel = 'Visa' then Credit_amount end) Visa,
max(case when Channel = 'Mada' then Credit_amount end) Mada,
max(case when Channel = 'Master' then Credit_amount end) Masters,
max(case when Channel = 'GCC' then Credit_amount end) GCC,
max(case when Channel = 'Amex' then Credit_amount end) Amex
from Bank_Statement where channel in ('Visa','Mada','Master','GCC','Amex')
group by branch, account, trandate, feesvalue, PDGNumber, GrandValue) t

group by trandate

order by trandate
+2