select a.customerno, category, Sum(linesellvaluenet) as SumOflinesellvaluenet 
into ##temp1
from tCustomer a
	inner join tCustMatRollup b 
		on a.customerno=b.customerno
	inner join tSAPMaterials c
		on b.material =c.material
	inner join tCategory d
		on c.pg4=d.prodgrp
where salesperiod between 200708 and 200807
and a.deleted is null
and a.blacklisted is null
and accounttype = '0001'
and a.salesoffice <> '1000'
group by a.customerno, d.category
order by a.customerno

select a.customerno, category, Sum(linesellvaluenet) as SumOflinesellvaluenet 
into ##temp1
from tCustomer a
	inner join tCustMatRollup b 
		on a.customerno=b.customerno
	inner join tSAPMaterials c
		on b.material =c.material
	inner join tCategory d
		on c.pg4=d.prodgrp
where salesperiod between 200708 and 200807
and a.deleted is null
and a.blacklisted is null
and accounttype = '0001'
and a.salesoffice <> '1000'
group by a.customerno, d.category
order by a.customerno
GO
select *
from ##temp
pivot (sum(SumOflinesellvaluenet) for category in ([Clothing],[Footwear],[Gloves],[Personal Protection],[Sector Specific],[Workplace]))

quote:

---

Originally posted by CarlyBond

Thank you very much for your reply.

I have tried the simple version (as opposed to the dynamic version) as my skills are a bit lacking in that department, but I get the following error message:

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '('.

Is there a simple solution to this error?
Code is now as follows:

select a.customerno, category, Sum(linesellvaluenet) as SumOflinesellvaluenet 
into ##temp1
from tCustomer a
	inner join tCustMatRollup b 
		on a.customerno=b.customerno
	inner join tSAPMaterials c
		on b.material =c.material
	inner join tCategory d
		on c.pg4=d.prodgrp
where salesperiod between 200708 and 200807
and a.deleted is null
and a.blacklisted is null
and accounttype = '0001'
and a.salesoffice <> '1000'
group by a.customerno, d.category
order by a.customerno
GO
select *
from ##temp
pivot (sum(SumOflinesellvaluenet) for category in ([Clothing],[Footwear],[Gloves],[Personal Protection],[Sector Specific],[Workplace]))p

Kind Regards

Carly

P.S. I have tried this code in SQL 2000 & SQL 2005

---

select *
from ##temp1
pivot (sum(SumOflinesellvaluenet) for category in ([Clothing],[Footwear],[Gloves],[Personal Protection],[Sector Specific],[Workplace]))p

quote:

---

Originally posted by CarlyBond

I still get the same message:

select *
from ##temp1 t
pivot (sum(SumOflinesellvaluenet) for category in ([Clothing],[Footwear],[Gloves],[Personal Protection],[Sector Specific],[Workplace]))p

I am using ## tables as I am the only person who will be running this code.

---

quote:

---

Originally posted by CarlyBond

I now get:

Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'pivot'.

---

SELECT @@VERSION
EXEC sp_dbcmptlevel 'your db name'

quote:

---

Originally posted by CarlyBond

I am actually using SQL 2000 but as the post you initially sent was a link about SQL 2005, I ran it in 2005 as well & still got the same message.

Results of query is:

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

---

quote:

---

Originally posted by CarlyBond

Oh, I see...well thats helpful :) The IT guys must have set it up this way, but forgot to mention it!

No worries, thanks for helping anyway...

I will just have to resort to a CASE...WHEN query instead.

Kind Regards

Carly

---

Table Customer:
UID int                   -> identity
Name nvarchar(25)         -> primary key


Table Status:
UID int                   -> identity
Descr nvarchar(15)
Type nvarchar(15)


Table Projects:
UID int                   -> identity
Project nvarchar(16)      -> primary key
CustomerID int            -> foreigner key  (from Customer table)
FinalCustomerID int       -> foreigner key  (from Customer table)
ProjectTypeID int         -> foreigner key  (from Status table)
ProjectStatusID int       -> foreigner key  (from status table)
SalesTypeID int           -> foreigner key  (from status table)


The contents of Status Table maybe:
UID        Descr         Type
1          HW            projecttype
2          SW            projecttype
3          Opened        projectstatus
4          Closed        projectstatus
5          Nac           salestype
6          Imp           salestype

Project         Customer         FinalCustomer        ProjectType         ProjectStatus        SalesType
525             HotDogs          BigMac               SW                  Opened               Nac
526             Hamburgers       BigMac               SW                  Opened               Nac
527             Bearings         SKF                  HW                  Closed               Imp

quote:

---

Originally posted by visakh16

its possible. b/w whats the relevance of type field in Status? is that field that distinguishes ProjectType,ProjectStatus, SalesType values?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---

quote:

---

Originally posted by visakh16

Anyways you've to join with Customer and Status on each of ID values to get the relevant details as columns

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---