In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.
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
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.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs).
Being someone who is always looking for alternatives to "work your data", I came across this …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB.
How Percona can help
Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses
Course of the Month7 days, 4 hours left to enroll
704 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.