{"id":2268,"date":"2020-11-03T15:10:38","date_gmt":"2020-11-03T15:10:38","guid":{"rendered":"http:\/\/manualsqlserver.com\/?p=2268"},"modified":"2020-11-03T15:10:40","modified_gmt":"2020-11-03T15:10:40","slug":"pivot-y-procedimientos-almacenados","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=2268","title":{"rendered":"Pivot y procedimientos almacenados"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"231\" height=\"260\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP__D.png\" alt=\"\" class=\"wp-image-2269\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Usando Pivot y Procedimientos almacenados<\/h2>\n\n\n\n<p>Las operaciones con Pivot nos permitir\u00e1 convertir los resultados de una consulta que se presentan en filas y mostrarlos en columnas. Pivot utiliza las funciones de agregado para presentar los datos en columnas. En este<br>art\u00edculo se presentan varios ejercicios usando el operador Pivot usando procedimientos almacenados para hacer las consultas din\u00e1micas.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p>Los operadores relacionales PIVOT y UNPIVOT cambian una expresi\u00f3n con valores de tabla en otra tabla. PIVOT rota una expresi\u00f3n con valores de tabla convirtiendo los valores \u00fanicos de una columna en la expresi\u00f3n en varias columnas en la salida.<br>UNPIVOT realiza la operaci\u00f3n opuesta a PIVOT al rotar columnas de una expresi\u00f3n con valores de tabla en valores de columna.<\/p>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p style=\"font-size:18px\"><strong>Para m\u00e1s informaci\u00f3n ver:<\/strong><br><a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=207\" target=\"_blank\">Funciones de agregado.<\/a><br><a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=507\" target=\"_blank\">Pivot en SQL Server<\/a><br><a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=2029\" target=\"_blank\">Unpivot SQL Server<\/a><br><a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=514\" target=\"_blank\">CTE en SQL Server<\/a><br><a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=225\" target=\"_blank\">Agrupamiento en SQL Server<\/a><br><a href=\"https:\/\/manualsqlserver.com\/?p=215\" target=\"_blank\" rel=\"noreferrer noopener\">Joins en SQL Server<\/a><br><a href=\"https:\/\/manualsqlserver.com\/?p=314\" target=\"_blank\" rel=\"noreferrer noopener\">Procedimientos almacenados<\/a><\/p>\n\n\n\n<p><strong>Usando la base de datos Northwind<\/strong><br>USE Northwind<br>go<\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 1<\/strong><\/p>\n\n\n\n<p><strong>Mostrar las ventas de los productos por cada mes en un a\u00f1o determinado. El procedimiento almacenado recibe el a\u00f1o como par\u00e1metro.<\/strong><\/p>\n\n\n\n<p>Create or alter procedure spTotalVentasPorMesyAnio<br>(@Anio int)<br>As<br>with TotalVentas As<br>(<br>SELECT<br>P.ProductName As &#8216;Producto&#8217;,<br>MONTH(O.OrderDate) As &#8216;Mes&#8217;,<br>SUM(D.Quantity) As &#8216;Unidades&#8217;<br>from Products As P<br>join [Order Details] As D on P.ProductID = D.ProductID<br>join Orders As O on D.OrderID = O.OrderID<br>Where Year(O.OrderDate)= @Anio<br>GROUP BY P.ProductName, MONTH(O.OrderDate)<br>)<br>select<br>Producto, ISNULL([1],0) Ene, ISNULL([2],0) Feb,<br>ISNULL([3],0) Mar, ISNULL([4],0) Abr, ISNULL([5],0) May,<br>ISNULL([6],0) Jun, ISNULL([7],0) Jul, ISNULL([8],0) Ago,<br>ISNULL([9],0) Sep, ISNULL([10],0) Oct, ISNULL([11],0) Nov,<br>ISNULL([12],0) Dic<br>from TotalVentas<br>Pivot (SUM(Unidades)<br>for Mes in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]))<br>As Tabla<br>go<\/p>\n\n\n\n<p><strong>Ejecutar el procedimiento para ver las ventas del a\u00f1o 1997<\/strong><br>Execute spTotalVentasPorMesyAnio 1997<br>go<br>El resultado se muestra en la siguiente imagen<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_01.png\" alt=\"\" class=\"wp-image-2270\" width=\"678\" height=\"593\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_01.png 863w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_01-300x262.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_01-768x672.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_01-795x696.png 795w\" sizes=\"auto, (max-width: 678px) 100vw, 678px\" \/><\/figure>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 2<\/strong><\/p>\n\n\n\n<p><strong>Listado de los empleados y las \u00f3rdenes generadas por a\u00f1o.<\/strong><\/p>\n\n\n\n<p>Create or alter procedure spOrdenesPorAnioPorEmpleado<br>As<br>with TotalOrdenes As<br>(<br>Select Empleado = E.LastName + Space(1) + E.FirstName, YEAR(O.OrderDate) As &#8216;A\u00f1o&#8217;,<br>COUNT(O.OrderID) As &#8216;\u00d3rdenes&#8217;<br>from Employees As E<br>join Orders As O on E.EmployeeID = O.EmployeeID<br>Group by E.LastName + Space(1) + E.FirstName, YEAR(O.OrderDate) )<br>&#8212; El pivot<br>select * from TotalOrdenes<br>pivot (Sum(\u00d3rdenes) for A\u00f1o in ([1996],[1997],[1998])) As Calculos<br>go<\/p>\n\n\n\n<p><strong>Ejecutar el procedimiento<\/strong><br>Execute spOrdenesPorAnioPorEmpleado<br>go<br>El resultado se muestra en la siguiente imagen<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_02.png\" alt=\"\" class=\"wp-image-2271\" width=\"538\" height=\"457\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_02.png 372w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_02-300x255.png 300w\" sizes=\"auto, (max-width: 538px) 100vw, 538px\" \/><\/figure>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 3<\/strong><\/p>\n\n\n\n<p><strong>Listado de las ventas en unidades monetarias por categor\u00eda y por a\u00f1o.<\/strong><\/p>\n\n\n\n<p>Create or alter procedure spVentasTotalesCategoriaAnio<br>As<br>With VentasTotalesCategoria As<br>(<br>select<br>C.CategoryID As &#8216;C\u00f3d. Categor\u00eda&#8217;,<br>C.CategoryName As &#8216;Categor\u00eda&#8217;,<br>Year(O.OrderDate) As &#8216;A\u00f1o&#8217;,<br>sum(D.Quantity * D.UnitPrice) As &#8216;Total&#8217;<br>from Categories As C<br>join Products As P on C.CategoryID = P.CategoryID<br>join [Order Details] As D on P.ProductID = D.ProductID<br>join Orders AS O on D.OrderID = O.OrderID<br>Group by C.CategoryID, C.CategoryName, Year(O.OrderDate)<br>)<br>select *<br>from VentasTotalesCategoria<br>pivot (sum(Total) for A\u00f1o in ([1996],[1997],[1998])) As Totales<br>go<\/p>\n\n\n\n<p><strong>Ejecutar el procedimiento almacenado<\/strong><br>Execute spVentasTotalesCategoriaAnio<br>go<br>El resultado se muestra en la siguiente imagen<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_03.png\" alt=\"\" class=\"wp-image-2272\" width=\"683\" height=\"355\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_03.png 586w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_03-300x156.png 300w\" sizes=\"auto, (max-width: 683px) 100vw, 683px\" \/><\/figure>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 4<\/strong><\/p>\n\n\n\n<p><strong>Total de ventas de un producto por a\u00f1o.<\/strong><\/p>\n\n\n\n<p>Create or alter procedure spVentasPorProductoPorCategoriaAnio<br>(@CodigoProducto Int)<br>As<br>With VentasTotalesProductoCategoria As<br>(<br>select<br>C.CategoryID As &#8216;C\u00f3d. Categor\u00eda&#8217;,<br>C.CategoryName As &#8216;Categor\u00eda&#8217;,<br>P.ProductName As &#8216;Producto&#8217;,<br>Year(O.OrderDate) As &#8216;A\u00f1o&#8217;,<br>sum(D.Quantity * D.UnitPrice) As &#8216;Total&#8217;<br>from Categories As C<br>join Products As P on C.CategoryID = P.CategoryID<br>join [Order Details] As D on P.ProductID = D.ProductID<br>join Orders AS O on D.OrderID = O.OrderID<br>where P.ProductID = @CodigoProducto<br>Group by C.CategoryID, C.CategoryName,<br>Year(O.OrderDate), P.ProductName<br>)<br>select *<br>from VentasTotalesProductoCategoria<br>pivot (sum(Total) for A\u00f1o in ([1996],[1997],[1998])) As Totales<br>go<\/p>\n\n\n\n<p><strong>Ejecutar para el producto con c\u00f3digo 5<\/strong><br>Execute spVentasPorProductoPorCategoriaAnio 5<br>go<br>El resultado se muestra en la siguiente imagen<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_04.png\" alt=\"\" class=\"wp-image-2273\" width=\"648\" height=\"122\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_04.png 717w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_04-300x56.png 300w\" sizes=\"auto, (max-width: 648px) 100vw, 648px\" \/><\/figure>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 5<\/strong><\/p>\n\n\n\n<p><strong>Total vendido por empleado por mes en un determinado a\u00f1o.<\/strong><\/p>\n\n\n\n<p>Create or alter procedure spTotalVentasPorEmpleadoMesyAnio<br>(@Anio int)<br>As<br>with TotalVentasEmpleado As<br>(<br>SELECT<br>Empleado = E.LastName + Space(1) + E.FirstName,<br>MONTH(O.OrderDate) As &#8216;Mes&#8217;,<br>SUM(D.Quantity * D.UnitPrice) As &#8216;Total&#8217;<br>from Employees As E<br>join Orders As O on E.EmployeeID= O.EmployeeID<br>join [Order Details] As D on O.OrderID= D.OrderID<br>Where Year(O.OrderDate)= @Anio<br>GROUP BY E.LastName + Space(1) + E.FirstName, MONTH(O.OrderDate)<br>)<br>select<br>Empleado, ISNULL([1],0) Ene, ISNULL([2],0) Feb,<br>ISNULL([3],0) Mar, ISNULL([4],0) Abr, ISNULL([5],0) May,<br>ISNULL([6],0) Jun, ISNULL([7],0) Jul, ISNULL([8],0) Ago,<br>ISNULL([9],0) Sep, ISNULL([10],0) Oct, ISNULL([11],0) Nov,<br>ISNULL([12],0) Dic<br>from TotalVentasEmpleado<br>Pivot (SUM(Total)<br>for Mes in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12]))<br>As Ventas<br>go<\/p>\n\n\n\n<p><strong>Ejecutar el procedimiento almacenado para el a\u00f1o 1997<br><\/strong>Execute spTotalVentasPorEmpleadoMesyAnio 1997<br>go<br>El resultado se muestra en la siguiente imagen<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_05-1024x292.png\" alt=\"\" class=\"wp-image-2274\" width=\"742\" height=\"211\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_05-1024x292.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_05-300x86.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_05-768x219.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_05-795x227.png 795w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_05.png 1125w\" sizes=\"auto, (max-width: 742px) 100vw, 742px\" \/><\/figure>\n\n\n\n<p style=\"font-size:22px\"><strong>Obtener los valores de un campo encerrados entre corchetes<\/strong><\/p>\n\n\n\n<p>Para obtener los valores de un campo encerrados entre corchetes se va a crear una variable para almacenar en esta los valores.<\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 6<\/strong><\/p>\n\n\n\n<p><strong>Obtener las descripciones de las categor\u00edas en una variable tipo cadena.<\/strong> Esta cadena de caracteres se usar\u00e1 junto con el operador Pivot para realizar la comparaci\u00f3n usando el operador In.<br>DECLARE @Categorias nvarchar(MAX) = \u00bb<br>SELECT @Categorias += QUOTENAME(CategoryName) + &#8216;,&#8217;<br>FROM Categories Order by CategoryName<br>Set @Categorias = Left(@Categorias, LEN(@Categorias) &#8211; 1);<br>select @Categorias<br>go<br>El resultado se muestra en la siguiente imagen<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_06.png\" alt=\"\" class=\"wp-image-2275\" width=\"707\" height=\"112\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_06.png 849w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_06-300x48.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_06-768x122.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_06-795x126.png 795w\" sizes=\"auto, (max-width: 707px) 100vw, 707px\" \/><\/figure>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 7<\/strong><\/p>\n\n\n\n<p><strong>Obtener las descripciones de los productos de la categoria 6<\/strong><br>DECLARE @ProductosCategoria6 nvarchar(MAX) = \u00bb<br>SELECT @ProductosCategoria6 += QUOTENAME(ProductName) + &#8216;,&#8217;<br>FROM Products where CategoryID = 6<br>Order by ProductName<br>Set @ProductosCategoria6 = Left(@ProductosCategoria6, LEN(@ProductosCategoria6) &#8211; 1)<br>Print @ProductosCategoria6<br>go<br>El resultado se muestra en la siguiente imagen<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_07.png\" alt=\"\" class=\"wp-image-2276\" width=\"690\" height=\"80\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_07.png 1015w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_07-300x35.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_07-768x89.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_07-795x92.png 795w\" sizes=\"auto, (max-width: 690px) 100vw, 690px\" \/><\/figure>\n\n\n\n<script async src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\"\n     style=\"display:block; text-align:center;\"\n     data-ad-layout=\"in-article\"\n     data-ad-format=\"fluid\"\n     data-ad-client=\"ca-pub-2636008503986218\"\n     data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 8<\/strong><\/p>\n\n\n\n<p><strong>Las ventas de los productos de una categor\u00eda en cada a\u00f1o. Para el ejemplo se va a usar los datos de la categor\u00eda 6<\/strong><br>with VentasProductoAnio As<br>(<br>select<br>P.ProductName As &#8216;Producto&#8217;,<br>Year(O.OrderDate) As &#8216;A\u00f1o&#8217;,<br>sum(D.Quantity) As &#8216;Unidades&#8217;<br>from Products As P<br>join [Order Details] As D on P.ProductID = D.ProductID<br>join Orders As O on D.OrderID = O.OrderID<br>where P.CategoryID = 6<br>Group by P.ProductName, Year(O.OrderDate)<br>)<br>select * from VentasProductoAnio<br>pivot (sum(Unidades) for A\u00f1o in ([1996],[1997],[1998]) )<br>As Totales<br>go<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_08.png\" alt=\"\" class=\"wp-image-2277\" width=\"540\" height=\"386\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_08.png 355w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_08-300x215.png 300w\" sizes=\"auto, (max-width: 540px) 100vw, 540px\" \/><figcaption>Productos de la categor\u00eda 6<\/figcaption><\/figure>\n\n\n\n<p><strong>El procedimiento almacenado para cualquier categor\u00eda<\/strong>. Este recibe el a\u00f1o como par\u00e1metro.<br>Create or alter procedure spVentasProductosCategoriaAnio<br>(<br>@CodigoCategoria int<br>)<br>As<br>with VentasProductoAnio As<br>(<br>select<br>P.ProductName As &#8216;Producto&#8217;,<br>Year(O.OrderDate) As &#8216;A\u00f1o&#8217;,<br>sum(D.Quantity) As &#8216;Unidades&#8217;<br>from Products As P<br>join [Order Details] As D on P.ProductID = D.ProductID<br>join Orders As O on D.OrderID = O.OrderID<br>where P.CategoryID = @CodigoCategoria<br>Group by P.ProductName, Year(O.OrderDate)<br>)<br>select * from VentasProductoAnio<br>pivot (sum(Unidades) for A\u00f1o in ([1996],[1997],[1998]) )<br>As Totales<br>go<\/p>\n\n\n\n<p><strong>Ejecutar el SP para los productos de la categor\u00eda 6<\/strong><br>Execute spVentasProductosCategoriaAnio 6<br>go<\/p>\n\n\n\n<p>El resultado es similar al de la imagen anterior.<\/p>\n\n\n\n<p><strong>Ahora se va a mostrar los productos como encabezados de columna<\/strong><\/p>\n\n\n\n<p><strong>Para generar la cadena con los productos s\u00f3lo de la categor\u00eda 6 (como ejemplo) se usa el siguiente c\u00f3digo.<br><\/strong>&#8212; Generar los productos<br>DECLARE @ProductosCategoria nvarchar(MAX) = \u00bb<br>SELECT @ProductosCategoria += QUOTENAME(ProductName) + &#8216;,&#8217;<br>FROM Products<br>where CategoryID = 6<br>Order by ProductName<br>Set @ProductosCategoria = Left(@ProductosCategoria, LEN(@ProductosCategoria) &#8211; 1)<br>Declare @Productos nvarchar(MAX)= (select (@ProductosCategoria))<br>Select @Productos<br>go<\/p>\n\n\n\n<p><strong>El c\u00f3digo anterior genera esta cadena.<br><\/strong>[Alice Mutton],[Mishi Kobe Niku],[P\u00e2t\u00e9 chinois],[Perth Pasties],[Th\u00fcringer Rostbratwurst],[Tourti\u00e8re]<\/p>\n\n\n\n<p><strong>Si se reemplaza la cadena en el operador Pivot<br><\/strong>Select * from<br>(select<br>P.ProductName As Producto,<br>Year(O.OrderDate) As A\u00f1o,<br>sum(D.Quantity) As Unidades<br>from Products As P<br>join [Order Details] As D on P.ProductID = D.ProductID<br>join Orders As O on D.OrderID = O.OrderID<br>where P.CategoryID = 6<br>Group by P.ProductName, Year(O.OrderDate)<br>) As Tabla<br>pivot (sum(Unidades) for Producto in<br>([Alice Mutton],[Mishi Kobe Niku],[P\u00e2t\u00e9 chinois],[Perth Pasties],[Th\u00fcringer Rostbratwurst],[Tourti\u00e8re]) )<br>As Totales<br>go<br>Se muestran los productos de la categor\u00eda 6.<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_09.png\" alt=\"\" class=\"wp-image-2278\" width=\"760\" height=\"176\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_09.png 828w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_09-300x70.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_09-768x178.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_09-795x184.png 795w\" sizes=\"auto, (max-width: 760px) 100vw, 760px\" \/><\/figure>\n\n\n\n<p><strong>Ahora usando la cadena generada en una instrucci\u00f3n SQL<\/strong><\/p>\n\n\n\n<p>Declare @Instruccion nvarchar(max)<br>DECLARE @ProductosCategoria nvarchar(MAX) = \u00bb<br>SELECT @ProductosCategoria += QUOTENAME(ProductName) + &#8216;,&#8217;<br>FROM Products<br>where CategoryID = 6<br>Order by ProductName<br>Set @ProductosCategoria = Left(@ProductosCategoria, LEN(@ProductosCategoria) &#8211; 1)<br>Declare @Productos nvarchar(MAX)= (select (@ProductosCategoria))<br>Set @Instruccion =<br>&#8216;Select * from<br>(select<br>P.ProductName As Producto,<br>Year(O.OrderDate) As A\u00f1o,<br>sum(D.Quantity) As Unidades<br>from Products As P<br>join [Order Details] As D on P.ProductID = D.ProductID<br>join Orders As O on D.OrderID = O.OrderID<br>where P.CategoryID = 6<br>Group by P.ProductName, Year(O.OrderDate)<br>) As Tabla<br>pivot (sum(Unidades) for Producto in (&#8216; + @Productos + &#8216;) )<br>As Totales&#8217;;<br>Execute (@Instruccion)<br>go<\/p>\n\n\n\n<p><strong>Creando un SP para hacerlo din\u00e1mico, para los productos de cualquier categor\u00eda.<\/strong><br>Create or alter procedure spVentasProductosCategoriaAnio<br>(<br>@CodigoCategoria int<br>)<br>As<br>Declare @Instruccion nvarchar(max)<br>DECLARE @ProductosCategoria nvarchar(MAX) = \u00bb<br>SELECT @ProductosCategoria += QUOTENAME(ProductName) + &#8216;,&#8217;<br>FROM Products<br>where CategoryID = @CodigoCategoria<br>Order by ProductName<br>Set @ProductosCategoria = Left(@ProductosCategoria, LEN(@ProductosCategoria) &#8211; 1)<br>Declare @Productos nvarchar(MAX)= (select (@ProductosCategoria))<br>Set @Instruccion =<br>&#8216;Select * from<br>(select<br>P.ProductName As Producto,<br>Year(O.OrderDate) As A\u00f1o,<br>sum(D.Quantity) As Unidades<br>from Products As P<br>join [Order Details] As D on P.ProductID = D.ProductID<br>join Orders As O on D.OrderID = O.OrderID<br>where P.CategoryID = &#8216; + Str(@CodigoCategoria) + &#8216;<br>Group by P.ProductName, Year(O.OrderDate)<br>) As Tabla<br>pivot (sum(Unidades) for Producto in (&#8216; + @Productos + &#8216;) )<br>As Totales&#8217;;<br>Execute (@Instruccion)<br>go<\/p>\n\n\n\n<p><strong>Las ventas de los productos de la categor\u00eda 5<br><\/strong>Execute spVentasProductosCategoriaAnio 5<br>go<\/p>\n\n\n\n<figure class=\"wp-block-image size-large is-resized\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_10-1024x156.png\" alt=\"\" class=\"wp-image-2279\" width=\"672\" height=\"102\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_10-1024x156.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_10-300x46.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_10-768x117.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_10-795x121.png 795w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/11\/ManualSQL_PivotySP_10.png 1204w\" sizes=\"auto, (max-width: 672px) 100vw, 672px\" \/><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Usando Pivot y Procedimientos almacenados Las operaciones con Pivot nos permitir\u00e1 convertir los resultados de una consulta que se presentan en filas y mostrarlos en columnas. Pivot utiliza las funciones de agregado para presentar los datos en columnas. En esteart\u00edculo se presentan varios ejercicios usando el operador Pivot usando procedimientos almacenados para hacer las consultas &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=2268\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,233,8],"tags":[24,263,48,66,242],"class_list":["post-2268","post","type-post","status-publish","format-standard","hentry","category-consultasdedatos","category-sp","category-programacion","tag-cte","tag-execute","tag-pivot","tag-procedimientos-almacenados","tag-store-procedure","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2268","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2268"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2268\/revisions"}],"predecessor-version":[{"id":2280,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2268\/revisions\/2280"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2268"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2268"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2268"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}