asked on reformat query
how can i change my query so it display results like cross tab or pivot format?
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.
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.
SQL
Last Comment
metropia
ASKER
Using SQL server 2009. for now the location codes will be those 4. possibly adding more in the event future.
Thank you.
Thank you.
ASKER
SQL Server 2008 R2. I apologize for the typing error.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER CERTIFIED SOLUTION
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a questionASKER
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
ASKER
it gives an error: Incorrect syntax near '10'.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
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
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
ASKER
how can i display the quantities below each location?
ASKER
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.
i am going to have to choose the expert comment that gave me a quick and dirty solution to my problem.
thank you.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
What databse are you using? Will your report always be using those 4 location codes?
AielloJ