Tuesday, April 28, 2009

Can You Take Creatine With Superpump

SQL

SQL is not my forte, but I want to share something that I used recently to solve a problem I had with a query, it turns out that I have a Products table, which are classified (Line-Represented), which it had in other tables, and needed bringing each product to its line and represented, with a normal query like this:

P. Code

SELECT P. Name, No Name
AS Level, A. Name AS Attribute
, P. Ean

Evento Mayo 2009

FROM Products P
INNER JOIN

ProductoAtributos PA

ON

PA.ProductoID 
    = P. ProductID INNER       Attributes   
 JOIN A ON     A. Attributes 
  =    PA.AtributoID       INNER JOIN     NivelProducto N N ON  . 
 =   Level A. Level         The result would be:     Code & # 160; Name , & # 160;  Nivel                 Atributo                Ean              ------------------------ -----------  & # 160; -------------- & # 160; ----------- & # 160; --------------- 1000000 &  
  # 160; Pampers BABYSEC Linea & # 160; LACTEOS & # 160;    1000000 5655425689322 Pampers BABYSEC Representada ; & GLORIA # 160; 1000001 5655425689322    Casinelli Line & GAS # 160; CONDIMENTS ; 6547574575474 1000001    Casinelli Represented GC 6547574575474 AJINOMOTO     If we analyze the result, not useful for much because it repeats the product line and represented to me what interests me is to have the line and represented in a single record with the product, so a little checking I found the solution in PIVOT, this is a feature new that comes from the 2005 version of SQL.
With this feature, we place the line and represents it in columns and obtain the desired result, the query would be something like:




SELECT Code, Name,


[Online



]







AS 'Line



'


,

[

Represented

] AS 
        '  Represented  '  , Ean      FROM (SELECT      P. Code, P. Name, No Name     AS Level , A. Name AS     attribute, P. Ean      FROM Products P        INNER JOIN 
  ProductoAtributos PA   ON  
 PA.ProductoID   = P. ProductID          INNER JOIN  
 Attributes A  ON    A. Attributes 
  =   PA.AtributoID        INNER JOIN     NivelProducto N N ON  . 
 =   Level A. Level  ) PVT  PIVOT (     MIN (attribute)  FOR      [Level  
  ]     IN   (  [Online    ]  , 
 
 [ Represented   ]  )) AS    Child              resulted as follows        Code Name & # 160; ;  Línea                Representada   Ean       -----------   -------------------           ------------          -------------         --------------- 1000000    Babysec PAMPERS ; MILK & GLORIA # 160; 5655425689322 1000001    ; GC Casinelli 6547574575474 SEASONING AJINOMOTO     This really helps us, we have a more orderly and better presented, with this solved the problem, This is known as Static PIVOT, since the columns are assigned to drive, but can also be done dynamically, perhaps later comment, and while I give you some links so they can further study.


Using PIVOT and UNPIVOT



Skip rows to columns with T-SQL: PIVOT () and things that will brighten up your day:)



Unpivot Pivot in SQL Server 2005







Implementation of Dynamic PIVOT





Posted by admin at 8:53 AM

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)