Solved

reformat query

Posted on 2012-12-27
10
149 Views
Last Modified: 2012-12-27
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
Comment
Question by:metropia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 2
10 Comments
 
LVL 13

Expert Comment

by:AielloJ
ID: 38724633
metropia,

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

AielloJ
0
 

Author Comment

by:metropia
ID: 38724643
Using SQL server 2009. for now the location codes will be those 4. possibly adding more in the event future.

Thank you.
0
 

Author Comment

by:metropia
ID: 38724655
SQL Server 2008 R2. I apologize for the typing error.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Accepted Solution

by:
sognoct earned 500 total points
ID: 38724728
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
 

Author Comment

by:metropia
ID: 38724943
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
 
LVL 9

Expert Comment

by:sognoct
ID: 38725042
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
 

Author Comment

by:metropia
ID: 38725139
it gives an error: Incorrect syntax near '10'.
0
 

Author Comment

by:metropia
ID: 38725184
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
 

Author Comment

by:metropia
ID: 38725186
how can i display the quantities below each location?
0
 

Author Comment

by:metropia
ID: 38725307
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question