Applying PIVOT

Question

Applying PIVOT

Navigation

#1 by (1 votes)

3

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

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

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 for the name of the product. As I said, I have a master specifications, that's why in NombreOpcion 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%'

That throws me the following data.

I need to separate the sizes and colors for that I have improvised with PIVOT , with which it was possible to put the colors in the Color column which 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

He throws me the following information.

Testing with pants

I do not know if it's the right way or 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. Greetings!

sql-server sql-server-2014

asked by Pedro Ávila 05.10.2018 в 15:11

source

1 answer

how can I save all the data within a table with php Stop media player android, when receiving calls or another application uses the audio

score 1 · Accepted Answer

If you only want to show those 2 products, already accommodated by their colors and their 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 per color you have every 1, you will get many results, but every 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 the specificationId = 5 (SIZE LETTER) for your shirt.

I hope and serve you.