Proper Case in Sql Server

DECLARE @Text As Varchar(200) = 'PROPERCASE Example in SQL Server by arKa guptA'

SELECT @Text = CONVERT(VARCHAR(500),UPPER(SUBSTRING(@Text,1,1)) + LOWER(SUBSTRING(@Text,2,499)))

SELECT @Text

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'Propercase' AND xtype = 'FN')

BEGIN

      DROP FUNCTION Propercase

END

GO

CREATE FUNCTION Propercase

      (@STRING VARCHAR(8000)) RETURNS VARCHAR(8000)

AS

BEGIN

      SET @STRING = LOWER(@STRING)

      DECLARE @I INT

      SET @I = ASCII('a')

      WHILE @I <= ASCII('z')

      BEGIN

            SET @STRING = REPLACE( @STRING, ' ' + CHAR(@I), ' ' + CHAR(@I-32))

            SET @I = @I + 1

      END

      SET @STRING = CHAR(ASCII(LEFT(@STRING, 1))-32) + RIGHT(@STRING, LEN(@STRING)-1)

      RETURN @STRING

END

      GO

      GRANT EXECUTE ON Propercase TO PUBLIC

      GO

     

SELECT dbo.Propercase('PROPERCASE Example in SQL Server by arKa guptA')

PIVOT in SQL Server

Crosstab queries using PIVOT in SQL Server 2005

You have results listed across.

SalesPerson	Product	SalesAmount
Bob	Pickles	$100.00
Sue	Oranges	$50.00
Bob	Pickles	$25.00
Bob	Oranges	$300.00
Sue	Oranges	$500.00

With a straight query the query results would be listed down, but the ideal solution would be to list the Products across the top for each SalesPerson, such as the following:

SalesPerson	Oranges	Pickles
Bob	$300.00	$125.00
Sue	$550.00

To use PIVOT you need to understand the data and how you want the data displayed.  First you have the data rows, such as SalesPerson and the columns, such as the Products and then the values to display for each cross section.  Here is a simple query that allows us to pull the cross-tab results.

SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles
FROM 
(SELECT SalesPerson, Product, SalesAmount
FROM ProductSales ) ps
PIVOT
(
SUM (SalesAmount)
FOR Product IN
( [Oranges], [Pickles])
) AS pvt

So how does this work?

There are three pieces that need to be understood in order to construct the query.

• (1) The SELECT statement
  • SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles
  • This portion of the query selects the three columns for the final result set (SalesPerson, Oranges, Pickles)
  
  
• (2) The query that pulls the raw data to be prepared
  • (SELECT SalesPerson, Product, SalesAmount FROM ProductSales) ps
  • This query pulls all the rows of data that we need to create the cross-tab results.  The (ps) after the query is creating a temporary table of the results that can then be used to satisfy the query for step 1.
  
  
• (3) The PIVOT expression
  • PIVOT (SUM (SalesAmount) FOR Product IN ( [Oranges], [Pickles]) ) AS pvt
  • This query does the actual summarization and puts the results into a temporary table called pvt

Another key thing to notice in here is the use of the square brackets [ ] around the column names in both the SELECT in part (1) and the IN in part (3).  These are key, because the pivot operation is treating the values in these columns as column names and this is how the breaking and grouping is done to display the data.