0

I have report where I am using that stored procedure to create a report using SSRS. The report currently show data like below (it has more additional columns) :

enter image description here

What I want is at the backend (stored procedure level or in SSRS) want the filter the data for product_type A only, but I want to show the amount associated with the product type F in a new column as below:

enter image description here

Can anyone help to achieve this please?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
deep
  • 41
  • 1
  • 12

2 Answers2

2

A simple LEFT JOIN with the same table will do the work

 SELECT t1.INVOICE_NO,t1.PRODUCT_TYPE,t1.AMOUNT,t2.AMOUNT AS FEE_AMOUNT
 FROM tbl t1
 LEFT JOIN tbl t2 ON t1.INVOICE_NO=t2.INVOICE_NO AND t2.PRODUCT_TYPE='F'
 WHERE t1.PRODUCT_TYPE='A'
Sanal Sunny
  • 617
  • 3
  • 9
0

you can also use matrix to achieve the same on ssrs side. In this case your trade off is none i.e. doing pivot on sql side and then rendering the data in ssrs table VS leaving data as is in sql and using matrix to pivot the data.

junketsu
  • 533
  • 5
  • 17