Skip to Content
author's profile photo Former Member
Former Member

How to Convert Rows to Column in Query

Dear All,

I'm having problems in converting the data from rows into columns. Eg:

- item A sold on 01/01/07 = 5 kg

- item A sold on 10/01/07 = 5 kg

total item A sold in "JAN" = 10kg

- item A sold on 01/03/07 = 20 kg

total item A sold in "Mar" = 20kg

I did a query and it appear as below (in which I need the period as column)

Item Qty Period

-


-


-


A 10 2007-01

A 20 2007-03

The output I need is :

Item Jan Feb Mar

-


-


-


-


A 10 - 20

I've refer to the query posted in "Item History Query", even though I can get the column from Jan - Dec, but the total quantity for each month is not accurate. Please advise, and thanks for your time.

Cheers,

Serene

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Oct 18, 2007 at 06:38 AM

    There seems to be no error in my Query. In the meanwhile I am working to get you something close to what you want.

    Give me a day's time and I will come back

    Suda

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 17, 2007 at 10:58 AM

    Hello Serena,

    Could you describe your query ? I would like to check the table you are using and also want to know whether you use createdate or updatedate field.

    Rgds,

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 17, 2007 at 11:26 AM

    You can make a query with the structure:

    Select I.Itemcode,

    (Select sum(…) from …X Where X.Itemcode =I.ItemCode

    and X.DocDate between ‘2007.01.01’ and ‘2007.01.31’) Jan,

    (Select sum(…) from …X Where X.Itemcode =I.ItemCode

    and X.DocDate between ‘2007.02.01’ and ‘2007.02.28’) Febr,

    (Select sum(…) from …X Where X.Itemcode =I.ItemCode

    and X.DocDate between ‘2007.03.01’ and ‘2007.03.31’) Marc,

    . . .

    From OITM I

    Where . . .

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 17, 2007 at 10:45 PM

    Hello Serena,

    I have provided this reply earlier to Ben who had a similar requirement.

    You may remove the where clause if you running this query for whole set of items.

    You may want to add the year in the where clause for each and every month to avoid getting sum total from mutiple years as shown in the last SELECT statement for TOTAL. Also if you want to base the result on the Sale Orders instead of invoices then change the table name to RDR1 instead of INV1 in the FROM clause of this Query.

    <b>SELECT T0.ITEMCODE,

    (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 1 AND ITEMCODE = T0.ITEMCODE) AS 'JAN',

    (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 2 AND ITEMCODE = T0.ITEMCODE) AS 'FEB',

    (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 3 AND ITEMCODE = T0.ITEMCODE) AS 'MAR',

    (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 4 AND ITEMCODE = T0.ITEMCODE) AS 'APR',

    (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 5 AND ITEMCODE = T0.ITEMCODE) AS 'MAY',

    (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 6 AND ITEMCODE = T0.ITEMCODE) AS 'JUN',

    (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 7 AND ITEMCODE = T0.ITEMCODE) AS 'JUL',

    (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 8 AND ITEMCODE = T0.ITEMCODE) AS 'AUG',

    (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 9 AND ITEMCODE = T0.ITEMCODE) AS 'SEP',

    (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 10 AND ITEMCODE = T0.ITEMCODE) AS 'OCT',

    (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 11 AND ITEMCODE = T0.ITEMCODE) AS 'NOV',

    (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 12 AND ITEMCODE = T0.ITEMCODE) AS 'DEC',

    (SELECT SUM(QUANTITY) FROM INV1 WHERE YEAR(DOCDATE) = YEAR(GETDATE()) AND ITEMCODE = T0.ITEMCODE) AS 'TOTAL'

    FROM INV1 T0

    WHERE T0.ITEMCODE = 'xyz'

    GROUP BY T0.ITEMCODE</b>

    Best Wishes

    Suda

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Suda, I've referred to your queries, and it managed to show JAN - DEC as column but the quantities is not accurate, (I've tried to change the month to year, it stay the same) Actually I hope the result will be display based user selection of BP as well as financial period. Eg: Once user enter BP name and financial period from 2007-01 till 2007-05, results will be display for that BP with that particular period

      <b></b>SELECT T1.ItemCode, T1.Dscription,

      (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 1 AND ITEMCODE = T1.ItemCode) AS 'JAN',

      (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 2 AND ITEMCODE = T1.ItemCode) AS 'FEB',

      (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 3 AND ITEMCODE = T1.ItemCode) AS 'MAR',

      (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 4 AND ITEMCODE = T1.ItemCode) AS 'APR',

      (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 5 AND ITEMCODE = T1.ItemCode) AS 'MAY',

      (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 6 AND ITEMCODE = T1.ItemCode) AS 'JUN',

      (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 7 AND ITEMCODE = T1.ItemCode) AS 'JUL',

      (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 8 AND ITEMCODE = T1.ItemCode) AS 'AUG',

      (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 9 AND ITEMCODE = T1.ItemCode) AS 'SEP',

      (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 10 AND ITEMCODE = T1.ItemCode) AS 'OCT',

      (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 11 AND ITEMCODE = T1.ItemCode) AS 'NOV',

      (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 12 AND ITEMCODE = T1.ItemCode) AS 'DEC',

      (SELECT SUM(QUANTITY) FROM INV1 WHERE YEAR(DOCDATE) = YEAR(GETDATE()) AND ITEMCODE = T1.ItemCode) AS 'TOTAL'

      FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OFPR T2 ON T0.FinncPriod = T2.AbsEntry

      WHERE T0.CardName ='[%A]' AND T2.Code >='[%1]' AND T2.Code <='[%2]'

      GROUP BY T1.ItemCode, T1.Dscription<b></b>

      Please advise and thanks for your help

  • author's profile photo Former Member
    Former Member
    Posted on Oct 18, 2007 at 03:19 AM

    Hello Serene,

    The Quantities will not be correct because you are using a Period Code in the Selection Criteria but it is not checked in the Select Statement.

    But rewriting the code would cause duplicate entries for each item per months because of how the Group by clause works.

    CHECK THIS

    SELECT T1.ItemCode, T1.Dscription,

    (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 1 AND ITEMCODE = T1.ItemCode AND DOCDATE >= T2.F_RefDate AND DOCDATE <= T_RefDate) AS 'JAN',

    (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 2 AND ITEMCODE = T1.ItemCode AND DOCDATE >= T2.F_RefDate AND DOCDATE <= T_RefDate) AS 'FEB',

    (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 3 AND ITEMCODE = T1.ItemCode AND DOCDATE >= T2.F_RefDate AND DOCDATE <= T_RefDate) AS 'MAR',

    (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 4 AND ITEMCODE = T1.ItemCode AND DOCDATE >= T2.F_RefDate AND DOCDATE <= T_RefDate) AS 'APR',

    (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 5 AND ITEMCODE = T1.ItemCode AND DOCDATE >= T2.F_RefDate AND DOCDATE <= T_RefDate) AS 'MAY',

    (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 6 AND ITEMCODE = T1.ItemCode AND DOCDATE >= T2.F_RefDate AND DOCDATE <= T_RefDate) AS 'JUN',

    (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 7 AND ITEMCODE = T1.ItemCode AND DOCDATE >= T2.F_RefDate AND DOCDATE <= T_RefDate) AS 'JUL',

    (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 8 AND ITEMCODE = T1.ItemCode AND DOCDATE >= T2.F_RefDate AND DOCDATE <= T_RefDate) AS 'AUG',

    (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 9 AND ITEMCODE = T1.ItemCode AND DOCDATE >= T2.F_RefDate AND DOCDATE <= T_RefDate) AS 'SEP',

    (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 10 AND ITEMCODE = T1.ItemCode AND DOCDATE >= T2.F_RefDate AND DOCDATE <= T_RefDate) AS 'OCT',

    (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 11 AND ITEMCODE = T1.ItemCode AND DOCDATE >= T2.F_RefDate AND DOCDATE <= T_RefDate) AS 'NOV',

    (SELECT SUM(QUANTITY) FROM INV1 WHERE MONTH(DOCDATE) = 12 AND ITEMCODE = T1.ItemCode AND DOCDATE >= T2.F_RefDate AND DOCDATE <= T_RefDate) AS 'DEC',

    (SELECT SUM(QUANTITY) FROM INV1 WHERE ITEMCODE = T1.ItemCode AND DOCDATE >= T2.F_RefDate AND DOCDATE <= T_RefDate) AS 'TOTAL'

    FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OFPR T2 ON T0.FinncPriod = T2.AbsEntry

    WHERE T0.CardName ='[%A]' AND T2.Code >='[%1]' AND T2.Code <='[%2]'

    GROUP BY T1.ItemCode, T1.Dscription, T2.F_RefDate, T2.T_RefDate

    The Only way out of this is to declare Variable and Assign the F_RefDate and T_RefDate and then use the variables in the Select Statements.

    Suda

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Suda,

      Thanks again for your fast reply. I've tried it out, but getting an error msg "MONTHR is not a recognize function name". I've checked the query too, and can't find any "MONTHR". Any idea? Please advise, and thanks for your time.

      p/s: sorry, I'm still new in queries!

      cheers,

      serene

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.