Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 161
  • Last Modified:

reformat query

how can i change my query so it display results like cross tab or pivot format?

SELECT [Location Code], SUM(Quantity)
FROM dbo.[CQC$Item Ledger Entry]
WHERE [Item No_] = '10438'
AND [Location Code] IN ('10', '20', '90', '95')
GROUP BY [Location Code]

Open in new window



results are:
results current
not all locations have quantities all times but ideally I would like my results to be like (using results as screen shot shows):

10        20        90        95
            2500    5050


Any help would be great.

thank you so much.
0
metropia
Asked:
metropia
  • 7
  • 2
1 Solution
 
AielloJCommented:
metropia,

What databse are you using?  Will your report always be using those 4 location codes?

AielloJ
0
 
metropiaAuthor Commented:
Using SQL server 2009. for now the location codes will be those 4. possibly adding more in the event future.

Thank you.
0
 
metropiaAuthor Commented:
SQL Server 2008 R2. I apologize for the typing error.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
sognoctCommented:
select
SUM(case when  [Location Code] = 10 then Quantity else 0 end) as l10,
SUM(case when  [Location Code] = 20 then Quantity else 0 end) as l20,
SUM(case when  [Location Code] = 90 then Quantity else 0 end) as l90,
SUM(case when  [Location Code] = 95 then Quantity else 0 end) as l95
FROM dbo.[CQC$Item Ledger Entry]
WHERE [Item No_] = '10438'
0
 
metropiaAuthor Commented:
thank you much. do you think this could be done using PIVOT? i would love to learn how to do it that way. if you could show me an example, if possible.
0
 
sognoctCommented:
cannot debug query (must restart my pc ... )
so, there can be some errors ... but should be something like :

SELECT 
[10], [20], [90], [95]
FROM
(SELECT [Location Code], SUM(Quantity)
  FROM dbo.[CQC$Item Ledger Entry]) AS SourceTable
  PIVOT (
  SUM(Quantity)
  FOR [Location Code] IN ('10', '20', '90', '95') ) AS PivotTable

Open in new window

0
 
metropiaAuthor Commented:
it gives an error: Incorrect syntax near '10'.
0
 
metropiaAuthor Commented:
got it past that error :

SELECT '10', '20', '90', '95'
FROM
(SELECT [Location Code], Quantity
  FROM dbo.[CQC$Item Ledger Entry]) AS SourceTable
  PIVOT (
  SUM(Quantity)
  FOR [Location Code] IN ([10], [20], [90], [95]) ) AS PivotTable
0
 
metropiaAuthor Commented:
how can i display the quantities below each location?
0
 
metropiaAuthor Commented:
it seems nobody is willing to explain little gurther. thank you though.

i am going to have to choose the expert comment that gave me a quick and dirty solution to my problem.

thank you.
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 7
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now