{"id":2675,"date":"2022-08-11T21:13:27","date_gmt":"2022-08-11T21:13:27","guid":{"rendered":"https:\/\/manualsqlserver.com\/?p=2675"},"modified":"2022-08-11T21:15:15","modified_gmt":"2022-08-11T21:15:15","slug":"pivot-en-sql-server","status":"publish","type":"post","link":"https:\/\/manualsqlserver.com\/?p=2675","title":{"rendered":"Pivot en SQL Server &#8211; Cursos"},"content":{"rendered":"\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer__D.png\"><img loading=\"lazy\" decoding=\"async\" width=\"220\" height=\"260\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer__D.png\" alt=\"\" class=\"wp-image-2676\"\/><\/a><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Pivot en SQL Server<\/h2>\n\n\n\n<p>Pivot y UnPivot son operadores relacionales que permiten mostrar datos de una consulta en un formato cambiado, tanto de columnas a filas o de filas a columnas.<\/p>\n\n\n\n<p>Pivot cambia los valores \u00fanicos de una columna y muestra los resultados en varias columnas con cada uno de los valores \u00fanicos, pivot permite adem\u00e1s realizar agregaciones. Unpivot realiza la acci\u00f3n contraria a lo que hace Pivot, cambia las columnas de una consulta en valores de una sola columna.<\/p>\n\n\n\n<!--more-->\n\n\n\n<p class=\"has-large-font-size\"><strong>Para m\u00e1s informaci\u00f3n ver<br><\/strong><a href=\"https:\/\/manualsqlserver.com\/?p=507\" target=\"_blank\" rel=\"noreferrer noopener\">Pivot SQL Server<br><\/a><a href=\"https:\/\/manualsqlserver.com\/?p=2268\" target=\"_blank\" rel=\"noreferrer noopener\">Pivot y procedimientos almacenados<br><\/a><a href=\"https:\/\/manualsqlserver.com\/?p=2029\" target=\"_blank\" rel=\"noreferrer noopener\">Unpivot en SQL Server<br><\/a><a href=\"https:\/\/manualsqlserver.com\/?p=82\" target=\"_blank\" rel=\"noreferrer noopener\">Crear Base de datos<br><\/a><a href=\"https:\/\/manualsqlserver.com\/?p=117\" target=\"_blank\" rel=\"noreferrer noopener\">Crear tablas<br><\/a><a href=\"https:\/\/manualsqlserver.com\/?p=294\" target=\"_blank\" rel=\"noreferrer noopener\">Insertar registros<\/a><\/p>\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-2636008503986218\" data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script<\/script>\n\n\n\n<p class=\"has-large-font-size\"><strong>Sintaxis de Pivot<\/strong><\/p>\n\n\n\n<p>SELECT ,<br>[ColumnaPivot1] As 'Alias',<br>[ColumnaPivot2] As 'Alias',<br>\u2026<br>[ColumnaPivotN] As 'Alias'<br>FROM<br>Tabla | Consulta<br>AS<br>PIVOT<br>(Columna Agregada)<br>FOR<br>[Encabezados de columnas]<br>IN ( [ColumnaPivot1], [ColumnaPivot2], \u2026 [ColumnaPivotN])<br>) AS<br>[ORDER BY \u2026]<\/p>\n\n\n\n<p class=\"has-large-font-size\"><strong>Crear una base de datos, luego una tabla con datos de alumnos para luego realizar los diversos ejercicios<\/strong><\/p>\n\n\n\n<p><strong>Creando la base de datos<br><\/strong>Create database TrabajaPivotUnPivot<br>go<br><strong>Abriendo la base de datos<br><\/strong>use TrabajaPivotUnpivot<br>go<\/p>\n\n\n\n<p><strong>Creando una tabla con cursos, en a\u00f1o y la cantidad de alumnos capacitados.<br><\/strong>Create table CursosAlumnos<br>(<br>CursosAlumnosCodigo nchar(5),<br>CursosAlumnosDescripcion nvarchar(30),<br>CursosAlumnosAnio int,<br>CursosAlumnosCantidadAlumnos int,<br>CursosAlumnosMontoIngresos Numeric(9,2),<br>constraint CursosAlumnosPK Primary key (CursosAlumnosCodigo)<br>)<br>go<br><strong>Insertando los datos para luego hacer los ejercicios<br><\/strong>insert into CursosAlumnos<br>values ('24026','SQL Server',2018,185,13000),<br>('16018','Aplicaciones M\u00f3viles',2019,90,60000),<br>('36963','SQL Server',2017,185,130000),<br>('15978','Aplicaciones M\u00f3viles',2018,50,40000),<br>('75321','Power BI',2018,88,250000),<br>('95174','Power BI',2019,250,850505),<br>('54685','SQL Server',2017,26,98000)<br>go<br>Listado de los registros de la tabla<br>Select * from dbo.CursosAlumnos<br>go<br>El resultado se muestra en la siguiente imagen<\/p>\n\n\n\n<figure class=\"wp-block-image size-large\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_01.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"253\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_01-1024x253.png\" alt=\"\" class=\"wp-image-2677\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_01-1024x253.png 1024w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_01-300x74.png 300w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_01-768x190.png 768w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_01-795x196.png 795w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_01.png 1094w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><figcaption>Tabla con datos de alumnos, cursos, cantidad de alumnos e ingresos.<\/figcaption><\/figure>\n\n\n\n<script async=\"\" src=\"\/\/pagead2.googlesyndication.com\/pagead\/js\/adsbygoogle.js\"><\/script>\n<ins class=\"adsbygoogle\" style=\"display:block; text-align:center;\" data-ad-layout=\"in-article\" data-ad-format=\"fluid\" data-ad-client=\"ca-pub-2636008503986218\" data-ad-slot=\"1902193434\"><\/ins>\n<script>\n     (adsbygoogle = window.adsbygoogle || []).push({});\n<\/script<\/script>\n\n\n\n<p class=\"has-larger-font-size\"><strong>Ejercicios<\/strong><\/p>\n\n\n\n<p class=\"has-larger-font-size\"><strong>Ejercicio 1<\/strong><\/p>\n\n\n\n<p><strong>Mostrar los cursos y la cantidad de alumnos por a\u00f1o.<br><\/strong>with TotalAlumnos As<br>(<br>select<br>[CursosAlumnosAnio], [CursosAlumnosDescripcion], [CursosAlumnosCantidadAlumnos]<br>from CursosAlumnos<br>)<br>select<br>[CursosAlumnosAnio] As 'A\u00f1o',<br>IsNull([SQL Server],0) As 'SQL Server',<br>IsNull([Aplicaciones M\u00f3viles],0) As 'Aplicaciones M\u00f3viles',<br>IsNull([Power BI],0) 'Power BI'<br>from TotalAlumnos<br>pivot (Sum(CursosAlumnosCantidadAlumnos) for<br>CursosAlumnosDescripcion in ([SQL Server],[Aplicaciones M\u00f3viles],[Power BI]))<br>As PVT<br>go<br>El resultado se muestra en la siguiente imagen<\/p>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_02.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_02.png\" alt=\"\" class=\"wp-image-2678\" width=\"518\" height=\"192\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_02.png 461w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_02-300x111.png 300w\" sizes=\"auto, (max-width: 518px) 100vw, 518px\" \/><\/a><\/figure>\n\n\n\n<p>Note que los a\u00f1os se muestran en la primera columna y luego por cada curso se suman la cantidad de alumnos.<\/p>\n\n\n\n<p class=\"has-larger-font-size\"><strong>Ejercicio 2<\/strong><\/p>\n\n\n\n<p><strong>Mostrar los a\u00f1os y la cantidad de ingresos por curso.<br><\/strong>with TotalAlumnos As<br>(<br>select<br>[CursosAlumnosAnio], [CursosAlumnosDescripcion], [CursosAlumnosMontoIngresos]<br>from CursosAlumnos<br>)<br>select<br>[CursosAlumnosAnio] As 'A\u00f1o',<br>IsNull([SQL Server],0) As 'SQL Server',<br>IsNull([Aplicaciones M\u00f3viles],0) As 'Aplicaciones M\u00f3viles',<br>IsNull([Power BI],0) 'Power BI'<br>from TotalAlumnos<br>pivot (Sum([CursosAlumnosMontoIngresos]) for<br>CursosAlumnosDescripcion in ([SQL Server],[Aplicaciones M\u00f3viles],[Power BI]))<br>As PVT<br>go<br>El resultado se muestra en la siguiente imagen<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_03.png\"><img loading=\"lazy\" decoding=\"async\" width=\"478\" height=\"188\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_03.png\" alt=\"\" class=\"wp-image-2679\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_03.png 478w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_03-300x118.png 300w\" sizes=\"auto, (max-width: 478px) 100vw, 478px\" \/><\/a><figcaption>Ingresos por cada curso y por a\u00f1o.<\/figcaption><\/figure>\n\n\n\n<p class=\"has-larger-font-size\"><strong>Ejercicio 3<\/strong><\/p>\n\n\n\n<p><strong>Mostrar los cursos y la cantidad de ingresos por a\u00f1o.<br><\/strong>with TotalAlumnos As<br>(<br>select<br>[CursosAlumnosAnio], [CursosAlumnosDescripcion], [CursosAlumnosMontoIngresos]<br>from CursosAlumnos<br>)<br>select<br>[CursosAlumnosDescripcion] As 'Curso',<br>IsNull([2017],0) As '2017',<br>IsNull([2018],0) As '2018',<br>IsNull([2019],0) '2019'<br>from TotalAlumnos<br>pivot (Sum([CursosAlumnosMontoIngresos]) for<br>[CursosAlumnosAnio] in ([2017],[2018],[2019]))<br>As PVT<br>go<br>El resultado se muestra en la siguiente imagen<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_04.png\"><img loading=\"lazy\" decoding=\"async\" width=\"541\" height=\"175\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_04.png\" alt=\"\" class=\"wp-image-2680\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_04.png 541w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_04-300x97.png 300w\" sizes=\"auto, (max-width: 541px) 100vw, 541px\" \/><\/a><figcaption>Cursos e ingresos por a\u00f1o.<\/figcaption><\/figure>\n\n\n\n<p class=\"has-larger-font-size\"><strong>Ejercicio 4<\/strong><\/p>\n\n\n\n<p><strong>Listado de productos por categor\u00eda y la cantidad de unidades vendidas por Trimestres<br>Se usar\u00e1 un procedimiento para hacer din\u00e1mica la categor\u00eda.<br><\/strong>Usando la base de datos Northwind<br>use Northwind<br>go<br>with Ventas As<br>(<br>select<br>C.CategoryName,<br>Sum(D.Quantity) As 'Cantidad' , Datepart(QUARTER, O.OrderDate) As 'Trimestre'<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>join Categories As C on P.CategoryID = C.CategoryID<br>where O.OrderDate between '01\/01\/1997' and '31\/12\/1997'<br>group by C.CategoryName, Datepart(QUARTER, O.OrderDate)<br>)<br>select<br>CategoryName As 'Categoria',<br>Isnull([1],0) As 'Primero',isNull([2],0) As 'Segundo',<br>IsNull([3],0) As 'Tercero',isNull([4],0) As 'Cuarto'<br>from Ventas<br>pivot<br>( sum(Cantidad) for Trimestre in ([1],[2],[3],[4]) ) As Trimestral<br>go<br>El resultado se muestra en la siguiente imagen<\/p>\n\n\n\n<figure class=\"wp-block-image size-full is-resized\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_05.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_05.png\" alt=\"\" class=\"wp-image-2681\" width=\"541\" height=\"329\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_05.png 482w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_05-300x182.png 300w\" sizes=\"auto, (max-width: 541px) 100vw, 541px\" \/><\/a><figcaption>Cantidad de unidades vendidas por producto.<\/figcaption><\/figure>\n\n\n\n<p><strong>Procedimiento para hacerlo din\u00e1mico por a\u00f1o, donde el par\u00e1metro ser\u00e1 el a\u00f1o.<\/strong><\/p>\n\n\n\n<p>Create or alter procedure spVentasUnidadesPorCategoriaAnualTrimestre (@Anio int)<br>As<br>Declare @FechaInicial Date = (select DATEFROMPARTS(@Anio, 1, 1))<br>Declare @FechaFinal Date = (select DATEFROMPARTS(@Anio, 12, 31));<br>with Ventas As<br>(<br>select<br>C.CategoryName,<br>Sum(D.Quantity) As 'Cantidad' , Datepart(QUARTER, O.OrderDate) As 'Trimestre'<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>join Categories As C on P.CategoryID = C.CategoryID<br>where O.OrderDate between @FechaInicial and @FechaFinal<br>group by C.CategoryName, Datepart(QUARTER, O.OrderDate)<br>)<br>select<br>CategoryName As 'Categoria',<br>Isnull([1],0) As 'Primero',isNull([2],0) As 'Segundo',<br>IsNull([3],0) As 'Tercero',isNull([4],0) As 'Cuarto'<br>from Ventas<br>pivot<br>( sum(Cantidad) for Trimestre in ([1],[2],[3],[4]) ) As Trimestral<br>go<\/p>\n\n\n\n<p>Ejecutar para el a\u00f1o 1997<br>Execute spVentasUnidadesPorCategoriaAnualTrimestre 1997<br>go<br>El resultado se muestra en la siguiente imagen<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_06.png\"><img loading=\"lazy\" decoding=\"async\" width=\"485\" height=\"300\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_06.png\" alt=\"\" class=\"wp-image-2682\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_06.png 485w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_06-300x186.png 300w\" sizes=\"auto, (max-width: 485px) 100vw, 485px\" \/><\/a><\/figure>\n\n\n\n<p>Ejecutar para el a\u00f1o 1998<br>Execute spVentasUnidadesPorCategoriaAnualTrimestre 1998<br>go<br>El resultado se muestra en la siguiente imagen<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><a href=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_07.png\"><img loading=\"lazy\" decoding=\"async\" width=\"483\" height=\"301\" src=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_07.png\" alt=\"\" class=\"wp-image-2683\" srcset=\"https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_07.png 483w, https:\/\/manualsqlserver.com\/wp-content\/uploads\/2022\/08\/ManualSQL_PivotSQLServer_07-300x187.png 300w\" sizes=\"auto, (max-width: 483px) 100vw, 483px\" \/><\/a><figcaption>Unidades vendidas en 1998, note que los dos \u00faltimos trimestres no tienen registrada ventas.<\/figcaption><\/figure>\n","protected":false},"excerpt":{"rendered":"<p>Pivot en SQL Server Pivot y UnPivot son operadores relacionales que permiten mostrar datos de una consulta en un formato cambiado, tanto de columnas a filas o de filas a columnas. Pivot cambia los valores \u00fanicos de una columna y muestra los resultados en varias columnas con cada uno de los valores \u00fanicos, pivot permite &hellip; <\/p>\n<p><a class=\"more-link btn\" href=\"https:\/\/manualsqlserver.com\/?p=2675\">Seguir leyendo<\/a><\/p>\n","protected":false},"author":1,"featured_media":2676,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[48,227],"class_list":["post-2675","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-programacion","tag-pivot","tag-unpivot","nodate","item-wrap"],"_links":{"self":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2675","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=2675"}],"version-history":[{"count":2,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2675\/revisions"}],"predecessor-version":[{"id":2687,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/posts\/2675\/revisions\/2687"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=\/wp\/v2\/media\/2676"}],"wp:attachment":[{"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2675"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2675"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/manualsqlserver.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2675"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}