Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Pivoting without aggregation Expand / Collapse
Author
Message
Posted Friday, February 22, 2008 10:35 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 9:39 AM
Points: 329, Visits: 195
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
Post #459208
Posted Friday, February 22, 2008 11:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 7,158, Visits: 15,269
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?
Post #459219
Posted Friday, February 22, 2008 12:41 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 9:39 AM
Points: 329, Visits: 195
i am sorry to bother but is there an way of cubing, MDX to resove it.



Regards
Vinay
Post #459256
Posted Friday, February 22, 2008 1:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 7,158, Visits: 15,269
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?
Post #459279
Posted Friday, February 22, 2008 2:57 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 9:39 AM
Points: 329, Visits: 195
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
Post #459330
Posted Friday, February 22, 2008 3:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 10:47 AM
Points: 7,158, Visits: 15,269
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?
Post #459340
Posted Friday, February 22, 2008 3:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, November 10, 2009 11:05 AM
Points: 2, Visits: 7
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
Post #459344
Posted Wednesday, January 27, 2010 8:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 12:16 PM
Points: 40, Visits: 456
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.
Post #854869
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse