February 22, 2008 at 10:35 am
I have a table Product
Product_ID = 'XDell020458'
Product_ID = 'XAcer05557'
now i have a mapping talbe
Product_ID , CountryCode , AlternateProductID
XDell020458 USA XDell20499-001
XDell020458 UK XDell20489-002
XDell020458 SGP XDell20489-003
All i want is the following
ProductID USA UK SGP
------------------------------------------------------------------------
XDell020458 XDell20499-001 XDell20499-001 XDell20499-001
Is this possible with a Pivot Operator.
Regards
Vinay
February 22, 2008 at 11:17 am
Technically - no. Pivot entails an aggregation operation.
Of course - an aggregation on a group of 1 would usually be the same as no aggregation at all. Try using MAX or MIN, based on something that won't aggregate more than one row at a time.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 22, 2008 at 12:41 pm
i am sorry to bother but is there an way of cubing, MDX to resove it.
Regards
Vinay
February 22, 2008 at 1:21 pm
I didn't answer that very well. Let me rephrase.
Your initial question is whether you can PIVOT without aggregation. The answer is no: PIVOT requires aggregation. That being said - you can use pivot to get what you want, assuming you "pivot" smartly. The trick is to make sure that the pivoting occurs on a group that would only return one row.
Meaning - use aggregation, even if it ultimately does "nothing".
In your example, this would yield the right result:
select product_id, USA,UK,SGP
from
(
select product_id,
countrycode,
alternateproductid
from mytable
) topvt
PIVOT
(
max(alternateproductid)
FOR countrycode in ([usa],[uk],[sgp])
) pvt
This might become problematic if you should ever get TWO alt. product ID's for a given country. But that's the "being smart" is about. Make sure that it only yields one per group.
MDX is also about aggregation. That's the purpose of a cube.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 22, 2008 at 2:57 pm
Hai,
That looks great but it gives me all nulls if the column doesnot have alternative identifirer for the product.
how do i handle that.
Very good trick. but does that realy work with nulls aswell.
Regards
Vinay
February 22, 2008 at 3:21 pm
Which column is NULL?
If a country code is mentioned once in any row - it should show in the column list, and put NULL next to the ones that don't have a value. If the country is not mentioned any any physical row - no column will show up. Of course - if you HAVE a predetermined list of countries, then using UNION to "insert" them in would make the column appear.
If you don't want to see NULLs then use the ISNULL or COALESCE functions to take that out.
select product_id,
isnull(pvt.USA) USA,
Isnull(pvt.UK) UK,
Isnull(pvt.SGP) SGP
from (
select product_id,
countrycode,
alternateproductid
from mytable
) topvt
PIVOT
(
max(alternateproductid)
FOR countrycode in ([usa],[uk],[sgp])
) pvt
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 22, 2008 at 3:28 pm
What about ...
select Product_ID
, MAX( case when CountryCode = 'USA' then AlternateProductID else null end ) AS USA
, MAX( case when CountryCode = 'UK' then AlternateProductID else null end ) AS UK
, MAX( case when CountryCode = 'SGP' then AlternateProductID else null end ) AS SGP
from mapping
group by
Product_ID
January 27, 2010 at 8:00 pm
Hai,
That looks great but it gives me all nulls if the column doesnot have alternative identifirer for the product.
how do i handle that.
Very good trick. but does that realy work with nulls aswell.
For posterity's sake, please allow me to chime in here...
I think what bhushanvinay is saying is that since there is no real aggregation going on here, he is not getting one row with all the AlternateProductID for each product. Instead, referring to his example data, he would get three rows with nulls for each of the CountryCodes that do not have a corresponding AlternateProductID. The Pivot function would work, but the values would be staggered across each of the columns and look something like this:
ProductID USA UK SGP
------------------------------------------------------------------------
XDell020458 XDell20499-001 NULL NULL
XDell020458 NULL XDell20489-002 NULL
XDell020458 NULL NULL XDell20489-003
So, the code Matt provided is a tad incomplete. What you also need is to get the "max" value of each of the CountryCodes returned by the Pivot function, which, as Matt eluded to earlier, will always return one value, so max() serves as a dummy function in this sense:
select product_id, max(USA),max(UK),max(SGP)
from
(
select product_id,
countrycode,
alternateproductid
from mytable
) topvt
PIVOT
(
max(alternateproductid)
FOR countrycode in ([usa],[uk],[sgp])
) pvt
group by Product_ID
So, when you are pivoting without aggregation, you still must feign an aggregation in the outermost select statement to get one row.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply