CODEWITHSUNDEEP
sql-server-2008reporting-services
Mr. Jin
Mr. Jin

Reputation: 79

Need to align as one row SQL

I have some data which I am trying to align as one row instead of two. Example what I am getting 

Name     Feb         Mar 
Tom    $229.32   NULL 
Tom     NULL     $182.63

How I need is below

    Name     Feb          Mar 
    Tom    $229.32    $182.63

Below is the query which I am using

select  Name, 
        case
        when convert(varchar(7), bill_dt, 126) = '2018-02'
        then sum(cast(amount as float))
        end Feb,
        case
        when convert(varchar(7), bill_dt, 126) = '2018-03'
        then sum(cast(amount as float))
        end Mar
from psi.eop_stagging
where bill_dt >= '2018/02/01' and bill_dt < '2018/04/01'
group by Name, bill_dt

Upvotes: 0

Views: 659

Answers (2)

Wolfgang Kais
Wolfgang Kais

Reputation: 4100

For your dataset, use a query that "reduces" the billing date to the first of the month (to give the report something to group by), for example:

select  
  Name,
  DATEADD(month, DATEDIFF(month, 0, bill_dt), 0) AS bill_mnth,
  amount
from psi.eop_stagging
where bill_dt >= '2018/02/01' and bill_dt < '2018/04/01';

You do not have to group yet, you can let the report do this for you. And don't use anything that sounds sophisticated and starts with "conditional", unless you want to use your report only for Febuary and March 2018.

Add a matrix to the report and use the field selector to select

  • Name for Rows
  • bill_mnth for Columns and
  • amount for Data

This will create a Row group grouped by Name and a Column group grouped by bill_mnth. In order to display the 3-letter abbreviation of the month instead of a date, right-click the TextBox and change the expression of that column header to

=Format(Fields!bill_mnth.Value, "MMM")

... or even "MMM yyyy" if you plan to enlarge the interval. And of course, format the data cell as desired.

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

Use conditional aggregation:

select  Name, 
        SUM( case when convert(varchar(7), bill_dt, 126) = '2018-02'
                  then cast(amount as float)
                  else 0
             end) as Feb,
        SUM( case when convert(varchar(7), bill_dt, 126) = '2018-03'
                  then cast(amount as float)
                  else 0
             end) as Mar
from psi.eop_stagging
where bill_dt >= '2018/02/01' and bill_dt < '2018/04/01'
group by Name

Upvotes: 1

Related Questions