Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and exploring the core foundations for app libraries.
reformat query
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
Premium Content
You need an Expert Office subscription to comment.Start Free Trial