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!
Course Of The Month
5 days, 22 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
reformat query
Posted on 2012-12-27
how can i change my query so it display results like cross tab or pivot format?
results are:
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.
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]
results are:
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.
[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
metropia,
What databse are you using? Will your report always be using those 4 location codes?
AielloJ
What databse are you using? Will your report always be using those 4 location codes?
AielloJ
Using SQL server 2009. for now the location codes will be those 4. possibly adding more in the event future.
Thank you.
Thank you.
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'
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'
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.
cannot debug query (must restart my pc ... )
so, there can be some errors ... but should be something like :
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
Featured Post
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
Suggested Solutions
| Title | # Comments | Views | Activity |
|---|---|---|---|
| vb.net, web page, SQL holding the selected data in an accessible variable | 7 | 48 | |
| When to use a Unique Index? A Clustered Index? | 5 | 76 | |
| How do i delete the last node in an xml in T-SQL | 7 | 26 | |
| SQL - Using an 'array' kind of variable | 5 | 13 |
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 …
Suggested Courses
Course of the Month5 days, 22 hours left to enroll
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.