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.
metropiaAsked:
Who is Participating?
 
sognoctConnect With a Mentor Commented:
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
 
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
metropiaAuthor Commented:
SQL Server 2008 R2. I apologize for the typing error.
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.