1

How can I filtered values in SQL?

When I execute this query, I'm getting the result below.

SELECT Stock_TBL.StockID, Stock_TBL.ItemCode, Stock_TBL.ItemDescription,
       InvoiceItems_TBL.StoreName, InvoiceItems_TBL.Quantity
FROM Stock_TBL
INNER JOIN InvoiceItems_TBL ON Stock_TBL.StockID = InvoiceItems_TBL.StockID
INNER JOIN Invoice_TBL ON InvoiceItems_TBL.InvoiceNo = Invoice_TBL.InvoiceNo
GROUP BY Stock_TBL.StockID, Stock_TBL.ItemCode, Stock_TBL.ItemDescription,
         InvoiceItems_TBL.StoreName, InvoiceItems_TBL.Quantity

enter image description here

I want that filtered like the below screen shot. How can i do it (for this three tables are involving and only 5 stores).

enter image description here

2

You want to PIVOT the StoreName. Try something like this:

SELECT * FROM (
  SELECT
    Stock_TBL.StockID,
    Stock_TBL.ItemCode,
    Stock_TBL.ItemDescription,
    InvoiceItems_TBL.StoreName,
    InvoiceItems_TBL.Quantity
  FROM Stock_TBL
  INNER JOIN InvoiceItems_TBL ON Stock_TBL.StockID = InvoiceItems_TBL.StockID
  INNER JOIN Invoice_TBL ON InvoiceItems_TBL.InvoiceNo = Invoice_TBL.InvoiceNo
  GROUP BY Stock_TBL.StockID, Stock_TBL.ItemCode, Stock_TBL.ItemDescription, InvoiceItems_TBL.StoreName, InvoiceItems_TBL.Quantity
) t
PIVOT ( SUM(Quantity)
  FOR StoreName IN (
    [E M B],
    [LB],
    [SHOP],
    [WAT],
    [NIH],
    [AJITH]
  )
) p

Note that you will have to specify the list of stores manually. If you don't want to do this, then research dynamic pivot.

|improve this answer|||||
  • Thanks for the answer answer. i'm getting error "Incorrect syntax near 'E M B'." how to fix What is PIVOT i'm new to SQL.. – M.I.M.F Jun 17 '15 at 21:49
  • try changing it to [E M B] – JoeNahmias Jun 17 '15 at 21:51
  • thanks again working fine.. small problem if i update the Store name error will occur so can i use sql query inside the [ ] such as PIVOT ( SUM(Quantity) FOR StoreName IN ( [SELECT StoreName FROM Store_TBL WHERE StoreID = 1], [SELECT StoreName FROM Store_TBL WHERE StoreID = 2], [SELECT StoreName FROM Store_TBL WHERE StoreID = 3], [SELECT StoreName FROM Store_TBL WHERE StoreID = 4], [SELECT StoreName FROM Store_TBL WHERE StoreID = 5], [SELECT StoreName FROM Store_TBL WHERE StoreID = 6] ) ) p – M.I.M.F Jun 17 '15 at 22:02
  • if i add query inside the box bracket i'm getting error.. how to solve that please help me – M.I.M.F Jun 17 '15 at 22:14
  • Can i specify the list of stores using select query because of when i change the column name i will get problem.. please help me... – M.I.M.F Jun 17 '15 at 22:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.