How to get this output using SQL query

Need help with this.

I have this table of data for illustration. (There are many other rows of data with different customer. Do consider this in the answer)

RowID   Customer     Category     Date       Figure1
  1     Cust1        Week 1       Jun-11     10
  2     Cust1        Week 2       Jun-11     20
  3     Cust1        Week 3       Jun-11     30
  4     Cust1        Week 4       Jun-11     40
  5     Cust1        Actual       Jun-11     200
  6     Cust1        Forecast     Jun-11     100
  7     Cust2        Forecast     Jun-11     100

I would like to have it display the Category Actual only (row 5) including the RowID on the pivoted Category as shown below

This should be the output.

RowID   Customer Date     Week1  Week2  Week3   Week4   Actual  Forecast
   5    Cust1    Jun-11   10     20     30      40      200     100

Any help would be appreciated.

Thanks in advance.

Tried Pivot开发者_如何学JAVA but it gives me this which is not i want.

RowID   Customer Date     Week1  Week2  Week3   Week4   Actual  Forecast
   1    Cust1    Jun-11   10     null   null    null    null    null
   2    Cust1    Jun-11   null   20     null    null    null    null
   3    Cust1    Jun-11   null   null   30      null    null    null
   4    Cust1    Jun-11   null   null   null    40      null    null
   5    Cust1    Jun-11   null   null   null    null    200     null
   6    Cust1    Jun-11   null   null   null    null    null    100

---

PIVOT is fine, but you need to GROUP BY and SUM afterwards.

Alternatively, you can self-JOIN on all the different code criteria, but it can be slightly less-maintainable than having the list of values in one place like you can with PIVOT.

---

You could do it this way:

    SELECT
        RowID,
        Customer,
        Date,
        (SELECT Figure1 FROM sotest WHERE Customer = 'Cust1' AND Date = 'Jun-11' AND Category = 'Week 1') AS Week1,
        (SELECT Figure1 FROM sotest WHERE Customer = 'Cust1' AND Date = 'Jun-11' AND Category = 'Week 2') AS Week2,
        (SELECT Figure1 FROM sotest WHERE Customer = 'Cust1' AND Date = 'Jun-11' AND Category = 'Week 3') AS Week3,
        (SELECT Figure1 FROM sotest WHERE Customer = 'Cust1' AND Date = 'Jun-11' AND Category = 'Week 4') AS Week4,
        (SELECT Figure1 FROM sotest WHERE Customer = 'Cust1' AND Date = 'Jun-11' AND Category = 'Actual') AS Actual,
        (SELECT Figure1 FROM sotest WHERE Customer = 'Cust1' AND Date = 'Jun-11' AND Category = 'Forecast') AS Forecast

    FROM
        sotest

    WHERE
        Customer = 'Cust1'
        AND Date = 'Jun-11'
        AND Category = 'Actual'

Should be fairly easy to wrap up in a stored procedure, where you can pass the CustomerID and Date params in.

---

Try this solution based on two parameters (@MyCustomer & @MyDate):

DECLARE @MyCustomer VARCHAR(10) = 'Cust1'
    ,@MyDate VARCHAR(10) = 'Jun-11';

SELECT pvt.*
FROM
(
SELECT  t.Customer, t.Date, t.Category, t.Figure1
FROM    MyTable t
WHERE   t.Customer = @MyCustomer AND t.[Date] = @MyDaye
) src
PIVOT ( SUM(src.Figure1) FOR src.Category IN ([Week 1], [Week 2], [Week 3], [Week 4], [Actual], [Forecast]) ) pvt

The basic idea is to filter in src derived table only those rows and columns you need for pivot, nothing more or less.