How to get this output using SQL query

Question

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 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

Have you tried the pivot cmd? msdn.microsoft.com/en-us/library/ms177410.aspx — bryanmac
– bryanmac, Commented Sep 5, 2011 at 13:00
use pivot tables msdn.microsoft.com/en-us/library/ms177410.aspx and kodyaz.com/articles/… — Amir Ismail
– Amir Ismail, Commented Sep 5, 2011 at 13:03
I have added the results of pivoted. Added in above question. But that is not what i want. Any help? — user696383
– user696383, Commented Sep 5, 2011 at 13:16

Cade Roux · Accepted Answer · 2011-09-05 13:34:09Z

1

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.

answered Sep 5, 2011 at 13:34

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

shawty · Accepted Answer · 2011-09-05 13:44:06Z

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.

Bogdan Sahlean · Accepted Answer · 2011-09-05 13:50:56Z

0

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.

answered Sep 5, 2011 at 13:50

Bogdan Sahlean

1

Collectives™ on Stack Overflow

How to get this output using SQL query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related