Matías López: abril 2019

Pivot y Unpivot en MSSQL

Armando Query's con Pivot y Unpivot en MSSQL (SQLServer2008 R2)

Creamos una Base de Datos, con un esquema simple de una tabla:

USE [Nombre_Base_Datos];
GO

-- Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
Emp3 int, Emp4 int, Emp5 int);
GO

Luego insertamos algunos datos:

INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO

Ahora probamos Pivot (varias SubQuery's con Union) en una Query:

-- And a simple union pivot for each column...
SELECT 'Orders N° 1' AS Orders_Sorted_By_pvt,
[1], [2], [3], [4], [5]
FROM
(SELECT VendorID, Emp1
FROM pvt) AS SourceTable
PIVOT
(
AVG(Emp1)
FOR VendorID IN ([1], [2], [3], [4], [5])
) AS PivotTable
UNION
SELECT 'Orders N° 2' AS Orders_Sorted_By_pvt,
[1], [2], [3], [4], [5]
FROM
(SELECT VendorID, Emp2
FROM pvt) AS SourceTable
PIVOT
(
AVG(Emp2)
FOR VendorID IN ([1], [2], [3], [4], [5])
) AS PivotTable
UNION
SELECT 'Orders N° 3' AS Orders_Sorted_By_pvt,
[1], [2], [3], [4], [5]
FROM
(SELECT VendorID, Emp3
FROM pvt) AS SourceTable
PIVOT
(
AVG(Emp3)
FOR VendorID IN ([1], [2], [3], [4], [5])
) AS PivotTable
UNION
SELECT 'Orders N° 4' AS Orders_Sorted_By_pvt,
[1], [2], [3], [4], [5]
FROM
(SELECT VendorID, Emp4
FROM pvt) AS SourceTable
PIVOT
(
AVG(Emp4)
FOR VendorID IN ([1], [2], [3], [4], [5])
) AS PivotTable
UNION
SELECT 'Orders N° 5' AS Orders_Sorted_By_pvt,
[1], [2], [3], [4], [5]
FROM
(SELECT VendorID, Emp5
FROM pvt) AS SourceTable
PIVOT
(
AVG(Emp5)
FOR VendorID IN ([1], [2], [3], [4], [5])
) AS PivotTable;
GO

Finalmente probamos Unpivot en una Query:

-- Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
(SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
FROM pvt) p
UNPIVOT
(Orders FOR Employee IN
(Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO

Canvas y SVG

Arriba: Ejemplo con Canvas.

Abajo: Ejemplo con SVG.

Fuente para leer conceptos:
Canvas | W3Schools
SVG | W3Schools

Bien., Armemos una HTML WebPage de Ta Te Ti con Canvas y otro con SVG. Ahí va el código:

<!DOCTYPE html>
<html>
	<head>
		<title>Tic Tac Toe</title>
	</head>
	<body>
		<h1>Tic Tac Toe</h1>
		<h2>Made with HTML5!</h2>
		<hr />
		<br />
		<h3>It's a Tic Tac Toe drawing made with Canvas and Filled with JavaScript.</h3>
		<br />
		<table>

		<!-- La parte de Canvas... -->
		<tbody>
			<tr>
				<td>
					<canvas id="id11" width="100" height="100" style="border:1px solid #d3d3d3;">
					Your browser does not support the HTML5 canvas tag.</canvas>
				</td>
				<td>
					<canvas id="id12" width="100" height="100" style="border:1px solid #d3d3d3;">
					Your browser does not support the HTML5 canvas tag.</canvas>
				</td>
				<td>
					<canvas id="id13" width="100" height="100" style="border:1px solid #d3d3d3;">
					Your browser does not support the HTML5 canvas tag.</canvas>
				</td>
			</tr>
			<tr>
				<td>
					<canvas id="id21" width="100" height="100" style="border:1px solid #d3d3d3;">
					Your browser does not support the HTML5 canvas tag.</canvas>
				</td>
				<td>
					<canvas id="id22" width="100" height="100" style="border:1px solid #d3d3d3;">
					Your browser does not support the HTML5 canvas tag.</canvas>
				</td>
				<td>
					<canvas id="id23" width="100" height="100" style="border:1px solid #d3d3d3;">
					Your browser does not support the HTML5 canvas tag.</canvas>
				</td>
			</tr>
			<tr>
				<td>
					<canvas id="id31" width="100" height="100" style="border:1px solid #d3d3d3;">
					Your browser does not support the HTML5 canvas tag.</canvas>
				</td>
				<td>
					<canvas id="id32" width="100" height="100" style="border:1px solid #d3d3d3;">
					Your browser does not support the HTML5 canvas tag.</canvas>
				</td>
				<td>
					<canvas id="id33" width="100" height="100" style="border:1px solid #d3d3d3;">
					Your browser does not support the HTML5 canvas tag.</canvas>
				</td>
			</tr>
		</tbody>
		</table>
		<br />
		<hr />
		<br />
		<h3>It's a Tic Tac Toe drawing made with SVG.</h3>
		<br />
		<table>

		<!-- La parte de SVG... -->
		<tbody>
			<tr>
				<td>
					<svg width="100" height="100" style="border:1px solid #d3d3d3;">
                    <polygon points="0,0 100,100 100,0 0,100" stroke="black" stroke-width="1" fill="none"/>
                    Sorry, your browser does not support inline SVG.
                    </svg>
				</td>
				<td>
					<svg width="100" height="100" style="border:1px solid #d3d3d3;">
                    <circle cx="50" cy="50" r="48" stroke="black" stroke-width="1" fill="none" />
                    Sorry, your browser does not support inline SVG.
                    </svg>
				</td>
				<td>
					<svg width="100" height="100" style="border:1px solid #d3d3d3;">
                    <polygon points="0,0 100,100 100,0 0,100" stroke="black" stroke-width="1" fill="none"/>
                    Sorry, your browser does not support inline SVG.
                    </svg>
				</td>
			</tr>
			<tr>
				<td>
					<svg width="100" height="100" style="border:1px solid #d3d3d3;">
                    <circle cx="50" cy="50" r="48" stroke="black" stroke-width="1" fill="none" />
                    Sorry, your browser does not support inline SVG.
                    </svg>
				</td>
				<td>
					<svg width="100" height="100" style="border:1px solid #d3d3d3;">
                    <polygon points="0,0 100,100 100,0 0,100" stroke="black" stroke-width="1" fill="none"/>
                    Sorry, your browser does not support inline SVG.
                    </svg>
				</td>
				<td>
					<svg width="100" height="100" style="border:1px solid #d3d3d3;">
                    <circle cx="50" cy="50" r="48" stroke="black" stroke-width="1" fill="none" />
                    Sorry, your browser does not support inline SVG.
                    </svg>
				</td>
			</tr>
			<tr>
				<td>
					<svg width="100" height="100" style="border:1px solid #d3d3d3;">
                    <polygon points="0,0 100,100 100,0 0,100" stroke="black" stroke-width="1" fill="none"/>
                    Sorry, your browser does not support inline SVG.
                    </svg>
				</td>
				<td>
					<svg width="100" height="100" style="border:1px solid #d3d3d3;">
                    <circle cx="50" cy="50" r="48" stroke="black" stroke-width="1" fill="none" />
                    Sorry, your browser does not support inline SVG.
                    </svg>
				</td>
				<td>
					<svg width="100" height="100" style="border:1px solid #d3d3d3;">
                    <polygon points="0,0 100,100 100,0 0,100" stroke="black" stroke-width="1" fill="none"/>
                    Sorry, your browser does not support inline SVG.
                    </svg>
				</td>
			</tr>
		</tbody>
		</table>
		
		<script>

			// JavaScript para llenar los espacios Canvas...
			function setCircle(c) {
			  var ctx = c.getContext("2d");
			  ctx.beginPath();
			  ctx.arc(50,50,48,0,2*Math.PI);
			  ctx.stroke();
			}

			function setCross(c) {
			  var ctx = c.getContext("2d");
			  ctx.moveTo(0,0);
			  ctx.lineTo(100,100);
			  ctx.stroke();
			  
			  var ctx2 = c.getContext("2d");
			  ctx2.moveTo(0,100);
			  ctx2.lineTo(100,0);
			  ctx2.stroke();
			}

			var c = document.getElementById("id11");
			setCross(c);

			c = document.getElementById("id12");
			setCircle(c);

			c = document.getElementById("id13");
			setCross(c);

			c = document.getElementById("id21");
			setCircle(c);

			c = document.getElementById("id22");
			setCross(c);

			c = document.getElementById("id23");
			setCircle(c);

			c = document.getElementById("id31");
			setCross(c);

			c = document.getElementById("id32");
			setCircle(c);

			c = document.getElementById("id33");
			setCross(c);
		</script>
	</body>
</html>

Hice algunas anotaciones en el código...
Espero que les sea de utilidad!

Matías López

lunes, 15 de abril de 2019

Pivot y Unpivot en MSSQL

lunes, 8 de abril de 2019

Canvas y SVG