Query Syntax
Query Syntax

reformat query

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:

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.

metropiaAsked: 2012-12-27

Who is Participating?

Solutions

LVL 9

sognoctConnect With a Mentor Commented: 2012-12-27

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'

LVL 13

AielloJCommented: 2012-12-27

metropia,

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

AielloJ

metropiaAuthor Commented: 2012-12-27

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

Thank you.

metropiaAuthor Commented: 2012-12-27

SQL Server 2008 R2. I apologize for the typing error.

metropiaAuthor Commented: 2012-12-27

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.

LVL 9

sognoctCommented: 2012-12-27

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

metropiaAuthor Commented: 2012-12-27

it gives an error: Incorrect syntax near '10'.

metropiaAuthor Commented: 2012-12-27

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

metropiaAuthor Commented: 2012-12-27

how can i display the quantities below each location?

metropiaAuthor Commented: 2012-12-27

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.