troubleshooting Question

SQL - Simple Pivot query

Avatar of csehz
csehzFlag for Hungary asked on
Microsoft SQL ServerSQL
8 Comments1 Solution126 ViewsLast Modified:
Dear Experts,

I have a simple pivot query in SQL, which works in this format:

SELECT 
	*
FROM
	(SELECT AP.LFB1.LIFNR, AP.LFB1.BUKRS FROM AP.LFB1) AS SourceTable
PIVOT (COUNT(SourceTable.LIFNR) FOR SourceTable.BUKRS IN ([DE1],[DE2])) AS CountCCs

but does not work in this one:

SELECT 
	AP.LFB1.LIFNR AS VendorNumber
FROM
	(SELECT AP.LFB1.LIFNR, AP.LFB1.BUKRS FROM AP.LFB1) AS SourceTable
PIVOT (COUNT(SourceTable.LIFNR) FOR SourceTable.BUKRS IN ([DE1],[DE2])) AS CountCCs

because of getting error message:
The multi-part identifier "AP.LFB1.LIFNR" could not be bound.

Could you please advise how that syntax should be, basically instead of all the columns with using '*', I would like to select one specified column plus the two DE1 and DE2 from the pivot.

Thanks in advance,
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros