Home / Questions / 202215
Accepted
Pedro Ávila
Asked: 2020-10-06 05:11:52 +0800 CST 2020-10-06 05:11:52 +0800 CST

Applying PIVOT

  • 772

I have a detail Catalogothat is called ProductoCatalogoin which I enter the products that are going to be sold, but there are products that have their specifications, as in the case of clothing they have: Size and Color, that is registered in a Specifications master that has the following structure: Header = table Especificacionesand Detail = Table Opcionesin Especificaciones va Color and in Opcionesva Red, Blue, Yellow, etc. The same happens for sizes.

This is the structure of the tables involved to get the information I want.

Product Catalog

The information I want to obtain is the following:

  • ProductCatalogId
  • Product name
  • Sale price
  • Size
  • Color

This is what I expect as a result:

1 CAMISETA FERRARI 12.00 TALLA LETRA S COLOR ROJO
1 CAMISETA FERRARI 12.00 TALLA LETRA S COLOR AZUL

For that I have elaborated the following query in which I apply a filter by the name of the product. As I told you, I have a master specification that is why in OptionName I get the sizes and colors.

SELECT PC.ProductoCatalogoId, PC.ProductoId, P.Nombre, PC.PrecioVenta, 

EP.EspecificacionId, E.Nombre, EC.OpcionId, 
O.NombreOpcion
FROM ProductoCatalogos AS PC
JOIN Productos AS P ON P.ProductoId = PC.ProductoId
JOIN EspecificacionProductos AS EP ON EP.ProductoId = P.ProductoId
JOIN Especificaciones AS E ON E.EspecificacionId = EP.EspecificacionId
JOIN EspecificacionCatalogos AS EC ON EC.EspecificacionProductoId = EP.EspecificacionProductoId
JOIN Opciones AS O ON O.OpcionId = EC.OpcionId
WHERE P.Nombre LIKE '%FERR%'

Which gives me the following data.

query

I need to separate the sizes and the colors for that I have improvised with PIVOT, with which it has been possible to put the colors in the Color column which was separated with PIVOT, but it has not worked with Size.

WITH Pruebas(ProductoCatalogoId, ProductoId, NombreProducto, PrecioVenta, 

EspecificacionId, Nombre, OpcionId, NombreOpcion) AS
    (SELECT PC.ProductoCatalogoId, PC.ProductoId, P.Nombre, PC.PrecioVenta, EP.EspecificacionId, E.Nombre, EC.OpcionId, 
O.NombreOpcion
FROM ProductoCatalogos AS PC
JOIN Productos AS P ON P.ProductoId = PC.ProductoId
JOIN EspecificacionProductos AS EP ON EP.ProductoId = P.ProductoId
JOIN Especificaciones AS E ON E.EspecificacionId = EP.EspecificacionId
JOIN EspecificacionCatalogos AS EC ON EC.EspecificacionProductoId = EP.EspecificacionProductoId
JOIN Opciones AS O ON O.OpcionId = EC.OpcionId
WHERE P.Nombre LIKE '%FERR%')
SELECT * FROM PRUEBAS
PIVOT(MAX(NombreOpcion) FOR Nombre IN ([TALLA LETRA], [COLOR])) PVT

It gives me the following information.

query 2

trying on pants

pants

I don't know if it's the right way or if there is another way to get what I need. Credentials to connect to the database.

Server: den1.mssql4.gear.host User: business Pass: Ga33-47L1YC!

Thanks in advance. Cheers!

sql-server

1 Answers

  1. Best Answer

    If you only want to show those 2 products, already arranged by their colors and sizes, it would look something like this:

       SELECT distinct(PC.ProductoCatalogoId),
    P.Nombre,
    PC.PrecioVenta,
    PL.Product + ' ' + E.Nombre + ' ' + O.NombreOpcion
FROM ProductoCatalogos AS PC
INNER JOIN Productos AS P ON PC.ProductoId = P.ProductoId
INNER JOIN EspecificacionCatalogos AS EC ON PC.ProductoCatalogoId = EC.ProductoCatalogoId 
INNER JOIN EspecificacionProductos AS EP ON EC.EspecificacionProductoId = EP.EspecificacionProductoId
INNER JOIN Especificaciones AS E ON EP.EspecificacionId = E.EspecificacionId
AND E.EspecificacionId = 2
INNER JOIN Opciones AS O ON EC.OpcionId = O.OpcionId
CROSS APPLY (
    SELECT E.Nombre + ' ' + O.NombreOpcion AS 'Product'
    FROM ProductoCatalogos AS PC
    INNER JOIN Productos AS P ON PC.ProductoId = P.ProductoId
    INNER JOIN EspecificacionCatalogos AS EC ON PC.ProductoCatalogoId = EC.ProductoCatalogoId
    INNER JOIN EspecificacionProductos AS EP ON EC.EspecificacionProductoId = EP.EspecificacionProductoId
    INNER JOIN Especificaciones AS E ON EP.EspecificacionId = E.EspecificacionId
        AND E.EspecificacionId IN (5)
    INNER JOIN Opciones AS O ON EC.OpcionId = O.OpcionId

    ) PL
    WHERE P.Nombre LIKE '%FERR%'

    The pants have different sizes and prices, if you take those sizes and prices by color that each 1 has, you will get many results, but each 1 with its price, color, and size ordered, while the shirt has only 1 size and 2 colors .

    In this case you must specify the accommodation of your new field, for this you must define which specificationId = 2 (COLOR) and specificationId = 5 (SIZE LETTER) for your shirt.

    I hope and serve you.

    • 1