Yojanson

martes, 5 de mayo de 2015

Consultar Tamaño de una tabla en SQL

EXEC sp_spaceused N'BASE.dbo.TABLA';

Resultado:

jueves, 6 de noviembre de 2014

Ejemplo Consulta Recursiva SQL

declare @MyEmployees TABLE
(
EmployeeID smallint NOT NULL,
FirstName nvarchar(30) NOT NULL,
LastName nvarchar(40) NOT NULL,
Title nvarchar(50) NOT NULL,
DeptID smallint NOT NULL,
ManagerID int NULL

)

INSERT INTO @MyEmployees VALUES
(1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16, N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

WITH DirectReports (ManagerID, EmployeeID, Title, Nivel)
AS
(
SELECT e.ManagerID, e.EmployeeID, e.Title,
0 AS Level
FROM @MyEmployees AS e
WHERE ManagerID IS NULL

Union all

SELECT e.ManagerID, e.EmployeeID, e.Title,
Nivel + 1
FROM @MyEmployees AS e
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)

Select * from DirectReports

Resultado

Ejemplo Consulta Pivot Table sql Basico

Declare @datos table (identificacion varchar(50), telefono int)

insert into @datos (identificacion,telefono)
values('215200298', 20125120),('215200298',22865822),('2222222', 12522555),
('215200298', 23335533)

Declare @tablaFinal table(identificacion varchar(50), telefono int, cantidad varchar(10))

insert into @tablaFinal(identificacion,telefono,cantidad)
Select t1.identificacion,t1.telefono,'Tel'+ cast(t1.Cant as varchar(5))Cantidad

From (
Select *, ROW_NUMBER() over (partition by identificacion order by telefono) Cant from @datos
)T1

Select
tabla.identificacion,
tabla.Tel1,
tabla.Tel2,
tabla.Tel3
from
@tablaFinal pivot(max(telefono) for cantidad in ([Tel1],[Tel2],[Tel3])) as Tabla

Resultado: