Learn, Practice & Share: December 2011

Saturday, December 31, 2011

String To Integer

-- SQL cast string to integer

SELECT intValue = CAST ('123456' as INT)

-- Result: 123456

-- SQL convert string to integer

SELECT intValue = CONVERT (INT, '00123456')

-- Result: 123456

SELECT intValue = CAST ('123,456' as INT)

/* Error

Msg 245, Level 16, State 1, Line 5

Conversion failed when converting the varchar value '123,456' to data type int.

Remaping keys with sequential update

CREATE TABLE Product(
ProductID int identity(1,1) PRIMARY KEY, -- Clustered Unique Index created
ProductName nvarchar(64) not null UNIQUE, -- Non-Clustered Unique Index created
ModifiedDate date default(getdate()));

CREATE TABLE ProductPhoto (
ProductPhotoID int identity(1,1) PRIMARY KEY, -- Clustered Unique Index created
ProductID int REFERENCES Product(ProductID),
LargePhoto varbinary(max),
ModifiedDate date default(getdate()));

USE tempdb;

SELECT *

INTO SODetail

FROM AdventureWorks2008.Sales.SalesOrderDetail

-- (121317 row(s) affected)

SELECT TOP 100 *

FROM SODetail

ORDER BY SalesOrderID,

SalesOrderDetailID

;

WITH cteDistinctSO

AS (SELECT DISTINCT SalesOrderID

FROM SODetail)

SELECT [SequentialID] = Identity(INT,1,1),

SalesOrderID

INTO #SequentialMapping

FROM cteDistinctSO

ORDER BY SalesOrderID ASC

--(31465 row(s) affected)

SELECT TOP 10 *

FROM #SequentialMapping

ORDER BY SalesOrderID

-- SQL Server sequential update

UPDATE s

SET s.SalesOrderID = m.[SequentialID]

FROM SODetail s

INNER JOIN #SequentialMapping m

ON s.SalesOrderID = m.SalesOrderID

SELECT TOP 100 *

FROM SODetail

ORDER BY SalesOrderID,

SalesOrderDetailID

DROP TABLE tempdb.dbo.SODetail

DROP TABLE #SequentialMapping

Top X in each group

-- TOP 3 / TOP 10 / TOP n per group - QUICK SYNTAX

;WITH CTE

AS (SELECT PSC.Name AS SubCategory,

P.Name AS ProductName,

ROW_NUMBER()

OVER(PARTITION BY P.ProductSubcategoryID

ORDER BY P.ProductID) AS RowID

FROM Production.ProductSubcategory PSC

LEFT JOIN Production.Product P -- or INNER JOIN

ON P.ProductSubcategoryID = PSC.ProductSubcategoryID)

SELECT Subcategory,

ProductName

FROM CTE

WHERE RowID <= 3

ORDER BY Subcategory,ProductName;

-- OR

DECLARE @TopN tinyint = 3;

WITH cteTopNSales

AS (SELECT Row_number()

OVER(PARTITION BY sod.ProductID

ORDER BY Sum(sod.LineTotal) DESC) AS SeqNo,

FirstName + ' ' + LastName AS [Name],

ProductName = p.Name,

'$' + Convert(VARCHAR,Convert(MONEY,Sum(sod.LineTotal)),

1) AS TotalBySalesPerson,

p.ProductNumber,

sod.ProductID

FROM Sales.SalesOrderDetail AS sod

INNER JOIN Production.Product AS p

ON sod.ProductID = p.ProductID

INNER JOIN Sales.SalesOrderHeader soh

ON sod.SalesOrderID = soh.SalesOrderID

INNER JOIN Person.Person c

ON soh.SalesPersonID = c.BusinessEntityID

WHERE soh.SalesPersonID IS NOT NULL

GROUP BY FirstName + ' ' + LastName,

sod.ProductID,

p.ProductNumber,

p.Name)

SELECT * FROM cteTopNSales cte

-- Display top 3 for each group

WHERE SeqNo <= @TopN

-- SeqNo = 2 will find the second highest in each group

ORDER BY ProductID,

SeqNo

-- OR

;WITH CTE AS

(SELECT TOP 1 WITH TIES PSC.Name AS SubCategory,

P.Name AS ProductName

FROM Production.ProductSubcategory PSC

INNER JOIN Production.Product P -- alternate LEFT JOIN

ON P.ProductSubcategoryID = PSC.ProductSubcategoryID

ORDER BY ROW_NUMBER()

OVER(PARTITION BY P.ProductSubcategoryID

ORDER BY ProductID) / (10+1) )

SELECT * FROM CTE

ORDER BY Subcategory,

ProductName;

-- OR

SELECT Subcategory,

ProductName

FROM (SELECT PSC.Name AS SubCategory,

P1.Name AS ProductName,

(SELECT COUNT(* )

FROM Production.ProductSubcategory PSC

LEFT JOIN Production.Product P2

ON P2.ProductSubcategoryID = PSC.ProductSubcategoryID

WHERE P2.ProductSubcategoryID = P1.ProductSubcategoryID

AND P2.ProductID <= P1.ProductID) AS RowID

FROM Production.ProductSubcategory PSC

LEFT JOIN Production.Product P1

ON P1.ProductSubcategoryID = PSC.ProductSubcategoryID) AS X

WHERE RowID <= 10

ORDER BY Subcategory,

ProductName;

Column Delimited List

-- Using XML PATH (SQL Server 2005 and on)

-- T-SQL create comma delimited list from single column result - QUICK SYNTAX

SELECT ColorCommaDelimitedList =

Stuff((SELECT ', ' + Color AS [text()]

FROM

(SELECT DISTINCT Color FROM AdventureWorks2008.Production.Product

) x

For XML PATH ('')),1,1,'')

ColorCommaDelimitedList

Black, Blue, Grey, Multi, Red, Silver, Silver/Black, White, Yellow

------------

-- Using XML PATH & CTE (SQL Server 2005 and on)

-- T-SQL create comma delimited list using CTE - Common Table Expression

;WITH cteColor AS

(SELECT DISTINCT Color FROM AdventureWorks2008.Production.Product)

SELECT ColorCommaDelimitedList =

Stuff((SELECT ', ' + Color AS [text()]

FROM cteColor

For XML PATH ('')),1,1,'')

ColorCommaDelimitedList

Black, Blue, Grey, Multi, Red, Silver, Silver/Black, White, Yellow

------------

-- Using COALESCE and local variable (SQL Server 2000 and before)

DECLARE @YearList VARCHAR(MAX)

SELECT @YearList = COALESCE(@YearList + ', ','') + CAST(OrderYear AS VARCHAR(4))

FROM (SELECT DISTINCT OrderYear = YEAR(OrderDate)

FROM AdventureWorks2008.Sales.SalesOrderHeader) x

ORDER BY OrderYear

SELECT YearList = @YearList

/* YearList

2001, 2002, 2003, 2004 */

----------

-- Using local variable (SQL Server 2000 and before)

-- T-SQL creating comma delimited list with local variable & multiple statements

USE AdventureWorks;

DECLARE @CommaLimitedList VARCHAR(MAX) = ''

SELECT @CommaLimitedList = Color + ', ' + @CommaLimitedList

FROM (SELECT DISTINCT Color FROM Production.Product WHERE Color is not null) x

SELECT CommaDelimitedList=@CommaLimitedList

CommaDelimitedList

Yellow, White, Silver/Black, Silver, Red, Multi, Grey, Blue, Black,

------------

-- Using XML PATH & correlated subquery for sublist

-- Create comma delimited sublist

SELECT Subcategory = ps.[Name],

ColorList = Stuff((SELECT DISTINCT ', ' + Color AS [text()]

FROM AdventureWorks2008.Production.Product p

WHERE p.ProductSubcategoryID = ps.ProductSubcategoryID

FOR XML PATH ('')),1,1,'')

FROM AdventureWorks2008.Production.ProductSubcategory ps

ORDER BY Subcategory;

Subcategory ColorList

....

Helmets Black, Blue, Red

Hydration Packs Silver

Jerseys Multi, Yellow

....

------------

-- Preparing spaces delimited list

-- T-SQL make spaces delimited list of ProductNumbers
SELECT Alpha.List.value('.','varchar(256)') AS DelimitedList
FROM   (SELECT   TOP ( 5 ) ProductNumber + '    '
        FROM     AdventureWorks2008.Production.Product
        ORDER BY ProductNumber DESC
        FOR XML PATH(''), TYPE) AS Alpha(List);
/*
DelimitedList
WB-H098    VE-C304-S    VE-C304-M    VE-C304-L    TT-T092
*/

Find all stored procedures where a column is being used/referenced

USE AdventureWorks2008;

DECLARE @SchemaName sysname = N'Production';

DECLARE @TableName sysname = N'Product';

DECLARE @ColumnName sysname = N'ProductID';

SELECT QUOTENAME(refing.referencing_schema_name) +

N'.' + QUOTENAME(refing.referencing_entity_name) As SprocName

FROM sys.dm_sql_referencing_entities(QUOTENAME(ISNULL(@SchemaName,N'dbo')) +

N'.' + QUOTENAME(@TableName),'object') refing

CROSS APPLY sys.dm_sql_referenced_entities(QUOTENAME(refing.referencing_schema_name) +

N'.' + QUOTENAME(refing.referencing_entity_name), 'object') refed

WHERE EXISTS(SELECT * FROM sys.objects

WHERE refing.referencing_id = object_id and type ='P')

AND refed.referenced_schema_name = @SchemaName

AND refed.referenced_entity_name = @TableName

AND refed.referenced_minor_name = @ColumnName

ORDER BY SprocName;

Number to word Conversion

CREATE FUNCTION fnNumberToWords(@Number as BIGINT)

RETURNS VARCHAR(1024)

BEGIN

DECLARE @Below20 TABLE (ID int identity(0,1), Word varchar(32))

DECLARE @Below100 TABLE (ID int identity(2,1), Word varchar(32))

INSERT @Below20 (Word) VALUES

( 'Zero'), ('One'),( 'Two' ), ( 'Three'),

( 'Four' ), ( 'Five' ), ( 'Six' ), ( 'Seven' ),

( 'Eight'), ( 'Nine'), ( 'Ten'), ( 'Eleven' ),

( 'Twelve' ), ( 'Thirteen' ), ( 'Fourteen'),

( 'Fifteen' ), ('Sixteen' ), ( 'Seventeen'),

('Eighteen' ), ( 'Nineteen' )

INSERT @Below100 VALUES ('Twenty'), ('Thirty'),('Forty'), ('Fifty'),

('Sixty'), ('Seventy'), ('Eighty'), ('Ninety')

DECLARE @English varchar(1024) =

(

SELECT Case

WHEN @Number = 0 THEN ''

WHEN @Number BETWEEN 1 AND 19

THEN (SELECT Word FROM @Below20 WHERE ID=@Number)

WHEN @Number BETWEEN 20 AND 99

THEN (SELECT Word FROM @Below100 WHERE ID=@Number/10)+ '-' +

dbo.fnNumberToWords( @Number % 10)

WHEN @Number BETWEEN 100 AND 999

THEN (dbo.fnNumberToWords( @Number / 100))+' Hundred '+

dbo.fnNumberToWords( @Number % 100)

WHEN @Number BETWEEN 1000 AND 999999

THEN (dbo.fnNumberToWords( @Number / 1000))+' Thousand '+

dbo.fnNumberToWords( @Number % 1000)

WHEN @Number BETWEEN 1000000 AND 999999999

THEN (dbo.fnNumberToWords( @Number / 1000000))+' Million '+

dbo.fnNumberToWords( @Number % 1000000)

WHEN @Number BETWEEN 1000000000 AND 999999999999

THEN (dbo.fnNumberToWords( @Number / 1000000000))+' Billion '+

dbo.fnNumberToWords( @Number % 1000000000)

WHEN @Number BETWEEN 1000000000000 AND 999999999999999

THEN (dbo.fnNumberToWords( @Number / 1000000000000))+' Trillion '+

dbo.fnNumberToWords( @Number % 1000000000000)

WHEN @Number BETWEEN 1000000000000000 AND 999999999999999999

THEN (dbo.fnNumberToWords( @Number / 1000000000000000))+' Quadrillion '+

dbo.fnNumberToWords( @Number % 1000000000000000)

WHEN @Number BETWEEN 1000000000000000000 AND 999999999999999999999

THEN (dbo.fnNumberToWords( @Number / 1000000000000000000))+' Quintillion '+

dbo.fnNumberToWords( @Number % 1000000000000000000)

ELSE ' INVALID INPUT' END

)

SELECT @English = RTRIM(@English)

SELECT @English = RTRIM(LEFT(@English,len(@English)-1))

WHERE RIGHT(@English,1)='-'

RETURN (@English)

END

Learn, Practice & Share

Labels

Saturday, December 31, 2011

Replicate Sample

String To Integer

Remaping keys with sequential update

Top X in each group

Column Delimited List

Find all stored procedures where a column is being used/referenced

Number to word Conversion

Total Pageviews