Quarter Year Wise Result Filteration In Sql

I have a table having structure as follows id cust_id target month year fiscal_ID 1 234 50 4 2013 1 2 234 50 5 2013 1 3 234 50 6 2013 1 4 2

Solution 1:

In order to concatenate the values from the multiple rows together, you will need to implement FOR XML PATH, similar to this:

;with cte as
(
  select t.cust_id,
    sum(target) target,
    d.qtr,
    t.year,
    t.fiscal_id
  from yourtable t
  innerjoin
  (
    select4 mth, 'Q1' qtr unionallselect5 mth, 'Q1' qtr unionallselect6 mth, 'Q1' qtr unionallselect7 mth, 'Q2' qtr unionallselect8 mth, 'Q2' qtr unionallselect9 mth, 'Q2' 
  ) d
    on t.month = d.mth
  groupby t.cust_id, d.qtr, t.year, t.fiscal_id
) 
selectdistinct cust_id,
  STUFF(
         (SELECT' / '+cast(c2.target asvarchar(10))
          FROM cte c2
          where c1.cust_id = c1.cust_id
            and c1.year = c2.year
            and c1.fiscal_id = c2.fiscal_id
          FOR XML PATH (''))
          , 1, 2, '')  AS target,
  STUFF(
         (SELECT' / '+ c2.qtr
          FROM cte c2
          where c1.cust_id = c1.cust_id
            and c1.year = c2.year
            and c1.fiscal_id = c2.fiscal_id
          FOR XML PATH (''))
          , 1, 2, '')  AS qtr,
  year,
  fiscal_id
from cte c1;

See SQL Fiddle with Demo