Column data want to convert in columns

Question

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(52)) 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

Nitin Sontakke · 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.

Nitin Sontakke · Answer

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(52)) 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

Nitin Sontakke · Answer

The error is because of 'GrandValue' column. You have already done the conversion for other columns using cast. Do the same to this column.

Feroz Khan · Answer

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.

Feroz Khan · Answer

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(52))) grand sum(cast(Feesvalue as decimal(52))) fees from ( select Branch Account PDGNumber trandate grandvalue grand cast(feesvalue as decimal(52)) 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:

Mohammad Hussain · Answer

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(52)) 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…