Link to home
Start Free TrialLog in
Avatar of csehz
csehzFlag for Hungary

asked on

SQL - Simple Pivot query

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

Open in new window


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

Open in new window


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,
Avatar of Vitor Montalvão
Vitor Montalvão
Flag of Switzerland image

You need to use the alias SourceTable:
SELECT 
	SourceTable.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

Open in new window

Avatar of csehz

ASKER

Thank you for the answer, I have tried that now, but unfortuantely the message still comes in this format:

The multi-part identifier "SourceTable.LIFNR" could not be bound.
What returns your original query? The one with SELECT *
In the final SELECT list, you have only access to the pivoted set, thus you need the alias name of the PIVOT clause.
Avatar of csehz

ASKER

Vitor,

The query with * brings vendors with all the columns of AP.LFB1, plus two columns [DE1] and [DE2].

In an even simplier format this works:

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

Open in new window


and this does not work:

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

Open in new window


because of error message:

The multi-part identifier "SourceTable.LIFNR" could not be bound.
ASKER CERTIFIED SOLUTION
Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of csehz

ASKER

Thank you those works both ones
I disagree with the marked answer, cause it does not clarify what alias name must be used, which is to problem in the OP. Especially as the OP already knew that using no alias name works..