{"id":2029,"date":"2020-06-08T21:27:28","date_gmt":"2020-06-08T21:27:28","guid":{"rendered":"http:\/\/manualsqlserver.com\/?p=2029"},"modified":"2020-06-08T21:27:28","modified_gmt":"2020-06-08T21:27:28","slug":"unpivot-sql-server","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=2029","title":{"rendered":"UnPivot SQL Server"},"content":{"rendered":"\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"220\" height=\"259\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/06\/ManualSQL_UnpivotSQLServer__D.png\" alt=\"\" class=\"wp-image-2030\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Unpivot en SQL Server<\/h2>\n\n\n\n<p>Los operadores PIVOT y UNPIVOT de la instrucci\u00f3n Select permiten cambiar una expresi\u00f3n con valores de tabla en otra tabla donde las columnas de la tabla origen se transponen en la tabla destino.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p>PIVOT gira una expresi\u00f3n con valores de tabla al convertir los valores \u00fanicos de una columna en la expresi\u00f3n en varias columnas en la salida. PIVOT ejecuta agregaciones donde se requieren en los valores de columna restantes que se desean en la salida final.<br>UNPIVOT realiza la operaci\u00f3n opuesta a PIVOT girando las 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>Sintaxis Select incluyendo Unpivot<br><strong>SELECT \u2026<br>FROM \u2026<br>UNPIVOT (<br>FOR IN ( ) )<\/strong><\/p>\n\n\n\n<p style=\"font-size:24px\"><strong>Ejemplos<\/strong><\/p>\n\n\n\n<p><strong>Usando el operador Pivot<\/strong><\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 1<\/strong><\/p>\n\n\n\n<p><strong>Usando Northwind vamos a presentar los empleados y la cantidad de  productos vendidos por categor\u00eda.<\/strong><br><a href=\"https:\/\/manualsqlserver.com\/?p=1863\" target=\"_blank\" rel=\"noreferrer noopener\">Ver Funciones nuevas en SQL Server 2017<\/a><br><a href=\"https:\/\/manualsqlserver.com\/?p=215\" target=\"_blank\" rel=\"noreferrer noopener\">Ver Consultas de varias tablas con Join<\/a><br><a href=\"https:\/\/manualsqlserver.com\/?p=507\" target=\"_blank\" rel=\"noreferrer noopener\">Ver Pivot en SQL Server<\/a><\/p>\n\n\n\n<p>use Northwind<br>go<\/p>\n\n\n\n<p>Select Empleado = CONCAT_WS(&#8216; &#8216;, E.FirstName, E.LastName),<br>C.CategoryName As &#8216;Categor\u00eda&#8217;,<br>Sum(D.Quantity) As &#8216;Cantidad&#8217;,<br>Year(O.OrderDate) As &#8216;A\u00f1o&#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>join Products As P on D.ProductID = P.ProductID<br>join Categories As C on P.CategoryID = C.CategoryID<br>Group by CONCAT_WS(&#8216; &#8216;, E.FirstName, E.LastName), C.CategoryName, Year(O.OrderDate)<br>order by Empleado, Cantidad desc<br>go<br><strong>El listado de los empleados es el siguiente.<\/strong><\/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\/06\/ManualSQL_UnpivotSQLServer_01.png\" alt=\"\" class=\"wp-image-2031\" width=\"596\" height=\"770\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/06\/ManualSQL_UnpivotSQLServer_01.png 517w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/06\/ManualSQL_UnpivotSQLServer_01-232x300.png 232w\" sizes=\"auto, (max-width: 596px) 100vw, 596px\" \/><\/figure>\n\n\n\n<p>Generar para facilidad una tabla con las ventas anteriores. Puede usar CTE<br><a href=\"https:\/\/manualsqlserver.com\/?p=289\" target=\"_blank\" rel=\"noreferrer noopener\">Ver Generar una tabla con Into<\/a><\/p>\n\n\n\n<p>Drop table if exists VentasEmpleadosYearCategoria<br>Select Empleado = CONCAT_WS(&#8216; &#8216;, E.FirstName, E.LastName),<br>C.CategoryName As &#8216;Categor\u00eda&#8217;,<br>Sum(D.Quantity) As &#8216;Cantidad&#8217;,<br>Year(O.OrderDate) As &#8216;A\u00f1o&#8217;<br>into VentasEmpleadosYearCategoria<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>join Products As P on D.ProductID = P.ProductID<br>join Categories As C on P.CategoryID = C.CategoryID<br>Group by CONCAT_WS(&#8216; &#8216;, E.FirstName, E.LastName), C.CategoryName, Year(O.OrderDate)<br>order by Empleado, Cantidad desc<br>go<\/p>\n\n\n\n<p><strong>Mostrar las ventas de cada empleado por A\u00f1o.<\/strong><br>select *<br>from VentasEmpleadosYearCategoria<br>pivot (Sum(Cantidad) for A\u00f1o in ([1996],[1997],[1998])) 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\/06\/ManualSQL_UnpivotSQLServer_02.png\" alt=\"\" class=\"wp-image-2032\" width=\"659\" height=\"743\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/06\/ManualSQL_UnpivotSQLServer_02.png 580w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/06\/ManualSQL_UnpivotSQLServer_02-266x300.png 266w\" sizes=\"auto, (max-width: 659px) 100vw, 659px\" \/><\/figure>\n\n\n\n<p><strong>Mostrar ahora de las categor\u00edas Beverages, Condiments y Confections<\/strong><br>select *<br>from VentasEmpleadosYearCategoria<br>pivot (Sum(Cantidad) for Categor\u00eda in ([Beverages], [Condiments],[Confections])) 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\/06\/ManualSQL_UnpivotSQLServer_03.png\" alt=\"\" class=\"wp-image-2033\" width=\"690\" height=\"691\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/06\/ManualSQL_UnpivotSQLServer_03.png 652w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/06\/ManualSQL_UnpivotSQLServer_03-300x300.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/06\/ManualSQL_UnpivotSQLServer_03-150x150.png 150w\" sizes=\"auto, (max-width: 690px) 100vw, 690px\" \/><\/figure>\n\n\n\n<p style=\"font-size:22px\"><strong>Ejercicio 2<\/strong><\/p>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Usando el operador UnPivot<\/strong><\/p>\n\n\n\n<p><strong>Listado de clientes y sus compras por a\u00f1o. Para las compras se va a usar una FDU, puede ser tambi\u00e9n una subconsulta<\/strong><\/p>\n\n\n\n<p>La FDU para el c\u00e1lculo de las compras de cada cliente por A\u00f1o. (<a href=\"https:\/\/manualsqlserver.com\/?p=335\" target=\"_blank\" rel=\"noreferrer noopener\">Ver Funciones definidas por el usuario<\/a>)<br>Create function dbo.fduCompraClientesPorYear (@Anio int, @CodigoCliente nchar(5))<br>returns Numeric(9,2)<br>As<br>Begin<br>Declare @TotalCompra Numeric(9,2)<br>Set @TotalCompra =<br>(<br>Select Sum((D.Quantity * D.UnitPrice) * (1- D.Discount))<br>from Customers As C<br>join Orders As O on C.CustomerID = O.CustomerID<br>join [Order Details] As D on O.OrderID = D.OrderID<br>where Year(O.OrderDate) = @Anio<br>and C.CustomerID = @CodigoCliente<br>)<br>Return @TotalCompra<br>End<br>go<\/p>\n\n\n\n<p><strong>La instrucci\u00f3n para el total de compras por cliente por a\u00f1o.<\/strong><\/p>\n\n\n\n<p>select C.CompanyName As &#8216;Cliente&#8217;,<br>isnull(dbo.fduCompraClientesPorYear(1996, C.CustomerID),0) As &#8216;1996&#8217;,<br>isnull(dbo.fduCompraClientesPorYear(1997, C.CustomerID),0) As &#8216;1997&#8217;,<br>isnull(dbo.fduCompraClientesPorYear(1998, C.CustomerID),0) As &#8216;1998&#8217;<br>from Customers As C<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\/06\/ManualSQL_UnpivotSQLServer_04.png\" alt=\"\" class=\"wp-image-2034\" width=\"667\" height=\"689\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/06\/ManualSQL_UnpivotSQLServer_04.png 691w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/06\/ManualSQL_UnpivotSQLServer_04-290x300.png 290w\" sizes=\"auto, (max-width: 667px) 100vw, 667px\" \/><\/figure>\n\n\n\n<p><strong>Generando una tabla (no es necesario pero es mas sencillo)<\/strong><br>drop table if exists ComprasPorClientePorAnio<br>select C.CompanyName As &#8216;Cliente&#8217;,<br>isnull(dbo.fduCompraClientesPorYear(1996, C.CustomerID),0) As &#8216;1996&#8217;,<br>isnull(dbo.fduCompraClientesPorYear(1997, C.CustomerID),0) As &#8216;1997&#8217;,<br>isnull(dbo.fduCompraClientesPorYear(1998, C.CustomerID),0) As &#8216;1998&#8217;<br>into ComprasPorClientePorAnio<br>from Customers As C<br>go<\/p>\n\n\n\n<p style=\"font-size:22px\"><strong>Ahora usando Unpivot<\/strong><\/p>\n\n\n\n<p><strong>Se va a mostrar los clientes y luego los a\u00f1os y las cantidades.<\/strong><br>Select Cliente, A\u00f1os, Totales<br>from ComprasPorClientePorAnio As C<br>unpivot (Totales for A\u00f1os in ([1996], [1997], [1998])) As Ventas<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\/06\/ManualSQL_UnpivotSQLServer_05.png\" alt=\"\" class=\"wp-image-2035\" width=\"707\" height=\"896\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/06\/ManualSQL_UnpivotSQLServer_05.png 565w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/06\/ManualSQL_UnpivotSQLServer_05-237x300.png 237w\" sizes=\"auto, (max-width: 707px) 100vw, 707px\" \/><figcaption>Resultado operador UnPivot<\/figcaption><\/figure>\n\n\n\n<p>La imagen siguiente muestra los resultados de la instrucci\u00f3n Select y del Unpivot. Note el cliente resaltado.<\/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\/06\/ManualSQL_UnpivotSQLServer_06-1024x489.png\" alt=\"\" class=\"wp-image-2036\" width=\"929\" height=\"443\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/06\/ManualSQL_UnpivotSQLServer_06-1024x489.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/06\/ManualSQL_UnpivotSQLServer_06-300x143.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/06\/ManualSQL_UnpivotSQLServer_06-768x367.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/06\/ManualSQL_UnpivotSQLServer_06.png 1509w\" sizes=\"auto, (max-width: 929px) 100vw, 929px\" \/><figcaption>Dos conjuntos de resultados, el de la derecha usando el operador Unpivot.<\/figcaption><\/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 3<\/strong><\/p>\n\n\n\n<p class=\"has-medium-font-size\"><strong>Usando el operador UnPivot<\/strong><\/p>\n\n\n\n<p><strong>Listado de Empleados y sus ventas por a\u00f1o. Para las Ventas se va a usar una subconsulta<\/strong> <br><strong>Generando la tabla ComprasPorEmpleadoPorAnio.<\/strong><br><a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=219\" target=\"_blank\">Ver Subconsultas<\/a><br><a rel=\"noreferrer noopener\" href=\"https:\/\/manualsqlserver.com\/?p=289\" target=\"_blank\">Ver Generar una tabla con Into<\/a><\/p>\n\n\n\n<p>drop table if exists ComprasPorEmpleadoPorAnio<br>select Empleado = CONCAT_WS(&#8216; &#8216;, Em.FirstName, Em.LastName),<br>Format(isnull((select Sum((D.Quantity * D.UnitPrice) * (1- D.Discount))<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) = 1996<br>and E.EmployeeID= Em.EmployeeID),0),&#8217;###,##0.00&#8242;) As &#8216;1996&#8217;,<br>Format(isnull((select Sum((D.Quantity * D.UnitPrice) * (1- D.Discount))<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) = 1997<br>and E.EmployeeID= Em.EmployeeID ),0),&#8217;###,##0.00&#8242;) As &#8216;1997&#8217;,<br>Format(isnull((select Sum((D.Quantity * D.UnitPrice) * (1- D.Discount))<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) = 1998<br>and E.EmployeeID= Em.EmployeeID ),0),&#8217;###,##0.00&#8242;) As &#8216;1998&#8217;<br>into ComprasPorEmpleadoPorAnio<br>from Employees As Em<br>go<\/p>\n\n\n\n<p>La tabla de ComprasPorEmpleadoPorAnio<br>select * from ComprasPorEmpleadoPorAnio<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\/06\/ManualSQL_UnpivotSQLServer_07.png\" alt=\"\" class=\"wp-image-2037\" width=\"733\" height=\"489\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/06\/ManualSQL_UnpivotSQLServer_07.png 604w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/06\/ManualSQL_UnpivotSQLServer_07-300x201.png 300w\" sizes=\"auto, (max-width: 733px) 100vw, 733px\" \/><\/figure>\n\n\n\n<p><strong>Ahora, usando Unpivot presentar los clientes y de cada cliente los a\u00f1os y sus totales.<\/strong><\/p>\n\n\n\n<p>select Empleado, A\u00f1o, Totales<br>from ComprasPorEmpleadoPorAnio<br>unpivot (Totales for A\u00f1o in ([1996], [1997], [1998])) 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\/06\/ManualSQL_UnpivotSQLServer_08.png\" alt=\"\" class=\"wp-image-2038\" width=\"660\" height=\"743\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/06\/ManualSQL_UnpivotSQLServer_08.png 467w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/06\/ManualSQL_UnpivotSQLServer_08-266x300.png 266w\" sizes=\"auto, (max-width: 660px) 100vw, 660px\" \/><\/figure>\n\n\n\n<p><strong>Puede notar el resultado del operador Unpivot<\/strong><\/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\/06\/ManualSQL_UnpivotSQLServer_09-1024x426.png\" alt=\"\" class=\"wp-image-2039\" width=\"874\" height=\"363\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/06\/ManualSQL_UnpivotSQLServer_09-1024x426.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/06\/ManualSQL_UnpivotSQLServer_09-300x125.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/06\/ManualSQL_UnpivotSQLServer_09-768x320.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2020\/06\/ManualSQL_UnpivotSQLServer_09.png 1254w\" sizes=\"auto, (max-width: 874px) 100vw, 874px\" \/><figcaption><em>Dos conjuntos de resultados, el de la derecha usando el operador Unpivot.<\/em><\/figcaption><\/figure>\n\n\n\n<p><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Unpivot en SQL Server Los operadores PIVOT y UNPIVOT de la instrucci\u00f3n Select permiten cambiar una expresi\u00f3n con valores de tabla en otra tabla donde las columnas de la tabla origen se transponen en la tabla destino.<\/p><p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=2029\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":2030,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[24,48,227],"class_list":["post-2029","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-programacion","tag-cte","tag-pivot","tag-unpivot","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2029","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=2029"}],"version-history":[{"count":1,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2029\/revisions"}],"predecessor-version":[{"id":2040,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2029\/revisions\/2040"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/2030"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2029"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2029"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2029"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}