Click here to Skip to main content
14,698,950 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I'm trying to get all the different types of items to become its own column for each invoice (instead of getting one line per type of item per invoice). Since there are so many invoices, I've created two temp. tables in SQL Server Management. Then I'm creating a query to pivot the tables based on the total price of the item (price*quantity) by item type. I get the error "Invalid column name 'totalprice'. I think it has to do with the data types (unit price and decimals and quantity are both decimals i.e. unit price = 160.405 and units = 1.0000). Could anyone assist? Thank you!

Create table #temp1 (
invoicenumber int,
store varchar(255),
purchasedate datetime, 
itemtype varchar(255),
totalprice money
)


Create table #temp2 (
invoicenumber int,
amount money
)

Insert INTO #temp1

 SELECT A.invnum,
 L.locationname,
 R.pdate,
 A.type,
 A.unitprice * A.quantity
 
From acct A
 inner join locations L
 on A.store = L.name
 inner join reports R
 on A.invoice = R.invnum

Insert INTO #temp2
Select invoicenumber, sum(totalprice)
From #temp1
Group by invoicenumber
Posted
Comments
_Asif_ 27-Jan-16 7:56am
   
It does not seem to have any problem, at which line you get this error
Member 12270118 27-Jan-16 9:09am
   
It's the last parenthesis for create table #temp2.
_Asif_ 27-Jan-16 23:37pm
   
I have successfully executed a tweaked version of urs and its working fine.

Create table #temp1 (
invoicenumber int,
store varchar(255),
purchasedate datetime,
itemtype varchar(255),
totalprice money
)


Create table #temp2 (
invoicenumber int,
amount money
)

Insert INTO #temp1

SELECT 1,'a', getdate(), 'b', 100
UNION ALL
SELECT 2,'A', getdate(), 'b', 101


Insert INTO #temp2
Select invoicenumber, sum(totalprice)
From #temp1
Group by invoicenumber

select * from #temp2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900