dynamic pivot example with sql server

CREATE TABLE Table4 (ColId INT,ColName VARCHAR(10))

INSERT INTO Table4 VALUES(1, 'Country')

INSERT INTO Table4 VALUES(2, 'Month')

INSERT INTO Table4 VALUES(3, 'Day')

select * from Table4

CREATE TABLE Table5 (tID INT,ColID INT,Txt VARCHAR(10))

INSERT INTO Table5 VALUES (1,1, 'US')

INSERT INTO Table5 VALUES (1,2, 'July')

INSERT INTO Table5 VALUES (1,3, '4')

INSERT INTO Table5 VALUES (2,1, 'US')

INSERT INTO Table5 VALUES (2,2, 'Sep')

INSERT INTO Table5 VALUES (2,3, '11')

INSERT INTO Table5 VALUES (3,1, 'US')

INSERT INTO Table5 VALUES (3,2, 'Dec')

INSERT INTO Table5 VALUES (3,3, '25')





SELECT tID

, [Country]

, [Day]

, [Month]

FROM ( SELECT t2.tID

, t1.ColName

, t2.Txt

FROM Table4 AS t1

JOIN Table5

AS t2 ON t1.ColId = t2.ColID

) p PIVOT ( MAX([Txt])

FOR ColName IN ( [Country], [Day],

[Month] ) ) AS pvt

ORDER BY tID ;







DECLARE @cols NVARCHAR(2000)

SELECT @cols = COALESCE(@cols + ',[' + colName + ']',

'[' + colName + ']')

FROM Table4

ORDER BY colName

select @cols







DECLARE @cols NVARCHAR(2000)

SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT

'],[' + t2.ColName

FROM Table4 AS t2

ORDER BY '],[' + t2.ColName

FOR XML PATH('')

), 1, 2, '') + ']'

select @cols

DECLARE @query NVARCHAR(4000)

SET @query = N'SELECT tID, '+

@cols +'

FROM

(SELECT t2.tID

, t1.ColName

, t2.Txt

FROM Table4 AS t1

JOIN Table5 AS t2 ON t1.ColId = t2.ColID) p

PIVOT

(

MAX([Txt])

FOR ColName IN

( '+

@cols +' )

) AS pvt

ORDER BY tID;'

--Executing this with

EXECUTE(@query)

tempdb size with sql server

USE master;

go

SELECT name AS 'File',

CAST(CAST(SIZE*1.0/128 AS DECIMAL(9,2)) AS VARCHAR(12)) + ' Mb' AS 'File Size',

CASE max_size WHEN 0 THEN 'Off'

WHEN -1 THEN 'On'

ELSE 'Will grow to 2 Tb'

END AS 'Auto Growth',

growth AS 'Growth',

CASE WHEN growth = 0 THEN 'size is fixed and will not grow'

WHEN growth > 0 and is_percent_growth = 0 THEN 'Growth in 8Kb pages'

ELSE '%'

END AS 'Increment'

FROM tempdb.sys.database_files;





USE tempdb

GO

EXEC SP_SPACEUSED;

merge example with sql server

CREATE TABLE Stock (Stock VARCHAR(10) PRIMARY KEY, Qty INT CHECK(Qty > 0));

CREATE TABLE Trades (Stock VARCHAR(10) PRIMARY KEY, Delta INT);

GO



INSERT Stock VALUES ('MSFT', 10);

INSERT Stock VALUES ('TXN', 5);



INSERT Trades VALUES ('MSFT', 5);

INSERT Trades VALUES ('TXN', -5);

INSERT Trades VALUES ('SBUX ', 3);

GO



-- Apply changes to the Stock table based on daily trades

-- tracked in the Trades table. Delete a row from the Stock table

-- if all the stock has been sold. Update the quantity in the Stock

-- table if you still hold some stock after the daily trades. Insert

-- a new row if you acquired a new Stock.

-- As a result, TXN is deleted, SBUX inserted, MSFT updated

MERGE Stock S -- target table

USING Trades T -- source table

ON S.Stock = T.Stock

WHEN MATCHED AND (Qty + Delta = 0) THEN

DELETE -- delete stock if entirely sold

WHEN MATCHED THEN

-- update stock if you still hold some stock

UPDATE SET Qty = Qty + Delta

WHEN NOT MATCHED THEN

-- insert a row if the stock is newly acquired

INSERT VALUES (Stock, Delta)

-- output details of INSERT/UPDATE/DELETE operations

-- made on the target table

OUTPUT $action, inserted.Stock, deleted.Stock;



SELECT * FROM Stock;

GO

data dictionary with sql server

SELECT src.TABLE_SCHEMA,src.TABLE_NAME,src.COLUMN_NAME,src.ORDINAL_POSITION,

CASE WHEN DATA_TYPE IN('nvarchar','varchar') THEN src.DATA_TYPE+'<'+CAST(src.LengthOfDataType AS NVARCHAR(20))+'>'

ELSE src.DATA_TYPE END AS DataType

FROM

(

SELECT cols.TABLE_SCHEMA,cols.TABLE_NAME,cols.COLUMN_NAME,cols.DATA_TYPE,cols.ORDINAL_POSITION,

CASE WHEN cols.CHARACTER_MAXIMUM_LENGTH IS NULL THEN '' ELSE cols.CHARACTER_MAXIMUM_LENGTH END AS LengthOfDataType

FROM INFORMATION_SCHEMA.COLUMNS AS cols

JOIN INFORMATION_SCHEMA.tables AS tabs

ON cols.TABLE_NAME = tabs.TABLE_NAME AND cols.TABLE_SCHEMA=tabs.TABLE_SCHEMA

AND tabs.TABLE_TYPE='base table'



)src

all tables size in selected database -- Sql Server

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @TableName VARCHAR(200)
-- Insert statements for procedure here
DECLARE tableCursor CURSOR FOR
SELECT sys.schemas.[name]+'.'+sys.objects.[name]
FROM sys.schemas INNER JOIN sys.objects ON sys.schemas.schema_id=sys.objects.schema_id
WHERE type='U' AND is_ms_shipped=0 ORDER BY sys.schemas.[name]
-- WHERE  is_ms_shipped is Microsoft generated objects
FOR READ ONLY
--A procedure level temp table to store the results
CREATE TABLE #TempTable
(
tableName varchar(200),
numberofRows varchar(100),
reservedSize varchar(50),
dataSize varchar(50),
indexSize varchar(50),
unusedSize varchar(50)
)
--Open the cursor
OPEN tableCursor
--Get the first Record from the cursor
FETCH NEXT FROM tableCursor INTO @TableName
--Loop until the cursor was not able to fetch
WHILE (@@Fetch_Status >= 0)
BEGIN
--Insert the results of the sp_spaceused query to the temp table
INSERT #TempTable
EXEC sp_spaceused @TableName
--Get the next Record
FETCH NEXT FROM tableCursor INTO @TableName
END
--Close/Deallocate the cursor
CLOSE tableCursor
DEALLOCATE tableCursor
--Select all records so we can use the reults
SELECT *
FROM #TempTable
DROP TABLE #TempTable
end

FOREIGN KEY with cascade example --- sql server

IF OBJECT_ID('SalesHistory') > 0
DROP TABLE SalesHistory
GO
CREATE TABLE SalesHistory
(
SaleID int IDENTITY(1,1) NOT NULL,
ProductID TINYINT,
CustomerID INT,
SaleDate datetime NULL,
SalePrice money NULL,
CONSTRAINT pk_SaleID PRIMARY KEY (SaleID)
)
GO
IF OBJECT_ID('Customers') > 0
DROP TABLE Customers
GO
CREATE TABLE Customers
(
CustomerID INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
CONSTRAINT pk_CustomerID PRIMARY KEY (CustomerID)
)
GO
IF OBJECT_ID('Products') > 0
DROP TABLE Products
GO
CREATE TABLE Products
(
ProductID TINYINT,
ProductDescription VARCHAR(100),
CONSTRAINT pk_ProductID PRIMARY KEY (ProductID)
)
GO
ALTER TABLE SalesHistory
ADD CONSTRAINT fk_SalesHistoryCustomerID FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ON DELETE SET NULL ON UPDATE SET NULL
ALTER TABLE SalesHistory
ADD CONSTRAINT fk_SalesHistoryProductID FOREIGN KEY (ProductID) REFERENCES Products(ProductID) ON DELETE CASCADE ON UPDATE CASCADE
go
delete  from Customers
INSERT INTO Products (ProductID, ProductDescription)
SELECT 1, 'BigScreen'
UNION ALL
SELECT 2, 'Computer'
UNION ALL
SELECT 3, 'PoolTable'
GO
INSERT INTO Customers(CustomerID , FirstName , LastName )
SELECT 1, 'Jason', 'Tomes'
UNION ALL
SELECT 2, 'Chris', 'Robards'
UNION ALL
SELECT 3, 'Megan', 'Hill'
UNION ALL
SELECT 4, 'Wanda', 'Guthrie'
UNION ALL
SELECT 5, 'Lilly', 'Cunningham'
UNION ALL
SELECT 6, 'Amanda', 'Travis'
UNION ALL
SELECT 7, 'Willy', 'Grant'
UNION ALL
SELECT 8, 'Zach', 'Tacoma'
UNION ALL
SELECT 9, 'Marty', 'Smith'
UNION ALL
SELECT 10, 'Wendi', 'Jones'
UNION ALL
SELECT 11, 'Angie' , 'Corolla'
UNION ALL
SELECT 12, 'Shelly', 'Hartson'
GO
DECLARE @i SMALLINT
SET @i = 1
WHILE (@i <=100)
BEGIN
INSERT INTO SalesHistory(ProductID, SaleDate, SalePrice, CustomerID)
VALUES(1, DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57), ((DATEPART(ms, GETDATE()) + (@i + 29)) % 12) + 1)
INSERT INTO SalesHistory(ProductID, SaleDate, SalePrice, CustomerID)
VALUES(2, DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13), ((DATEPART(ms, GETDATE()) + (@i + 29)) % 12) + 1)
INSERT INTO SalesHistory(ProductID, SaleDate, SalePrice, CustomerID)
VALUES(3, DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29), ((DATEPART(ms, GETDATE()) + (@i + 29)) % 12) + 1)
SET @i = @i + 1
END
go
select * from SalesHistory (nolock) where CustomerID = 100
select * from Customers (nolock) WHERE CustomerID = 100
DELETE FROM Customers
WHERE CustomerID = 3
begin tran
update Customers
set CustomerID = 100
where customerid=10
commit

select * from Products

DELETE FROM Products
WHERE ProductID = 3
update Products
set ProductID =4
WHERE ProductID = 3
select * from SalesHistory where ProductID = 3
truncate table SalesHistory

Comma separate with quirky update

create table Juls (
    PersonID int,
    Degree varchar(3) )
insert juls
select 55,'md'
union select 55,'phd'
union select 55,'rn'
union select 60,'md'
union select 60,'phd'

declare @List varchar(100), @LastID int
select @List = '', @LastID = ''
select
    PersonID,
    Degree,
    convert(varchar(100),NULL) as list
into
    #rowset
from
    Juls
order by
    PersonID,
    Degree
update
    #rowset
set
    @List = list = case
                       when @LastID <> PersonID then Degree
                       else @List + ',' + Degree
                   end,
    @LastID = PersonID
select
    PersonID,
    max(list)
from
    #rowset
group by
    PersonID
go
drop table #rowset

pagenumber search with sql server

declare @lv_pageSize int, @lv_PageNumber int

set @lv_PageNumber=2

set @lv_pageSize =20

select * from

(
SELECT TOP 100 PERCENT

[primary].*,((row_number() OVER(ORDER BY emp_shiftid asc ) -1)/@lv_pageSize)+1 as pagenumber

FROM(

select *FROM Employee_Shifts

)[primary]

)[Primary] where pagenumber = @lv_PageNumber