SQL PIVOT table

Overview:

                It gives you the transition from rows to columns. Below example explains how we can use PIVOT on table data.

Code:

Prerequisites

/*Create a tempeorary table*/

CREATE TABLE #PivotExample(Col1 VARCHAR(20), Col2 VARCHAR(20), Col3 FLOAT)

GO

/*Inserting data into the tempeorary table*/

INSERT INTO #PivotExample(Col1, Col2, Col3) VALUES('A','x',5.4)

,('A','y',3.4)

,('A','z',5.2)

,('A','x',4.4)

,('B','y',3.4)

,('B','z',1.4)

,('B','x',3.2)

,('B','z',7.1)

GO

/*Displays the data from the table*/

SELECT * FROM #PivotExample

GO

PIVOT usage

/*Pivot table logic*/

SELECT Col2, A, B

FROM (

SELECT Col1, Col2, Col3

FROM #PivotExample) actualTable

PIVOT (SUM(Col3) FOR Col1 IN (A, B)) AS pivotTable

ORDER BY Col2

GO

/*Removing tempeorary table*/

DROP TABLE #PivotExample

GO

Result:

Data in #PivotExample table

PIVOT Result (Applied on “Col1”)

SQL String Split Function

Overview:

 The SQL code gives you the string spilt functionality of given delimiter. You can define your own delimiter character, in below code I have used ‘:’ as a delimiter. If you want to use other delimiter replace ‘:’ with your character.

SQL Code:  

CREATE FUNCTION dbo.fnSplitString (@string nvarchar(MAX))

   RETURNS @tblTemp TABLE (splittedValue nvarchar(MAX) NOT NULL) AS

BEGIN

   DECLARE @posIndex        int,

           @nextposIndex    int,

           @valuelen   int

   SELECT @posIndex = 0, @nextposIndex = 1

   WHILE @nextposIndex > 0

   BEGIN

      SELECT @nextposIndex = charindex(':', @string, @posIndex + 1)

      SELECT @valuelen = CASE WHEN @nextposIndex > 0

                              THEN @nextposIndex

                              ELSE len(@string) + 1

                         END - @posIndex - 1

      INSERT @tblTemp (splittedValue)

         VALUES (substring(@string, @posIndex + 1, @valuelen))

      SELECT @posIndex = @nextposIndex

   END

   RETURN

END

Usage:

SELECT      *

FROM  dbo.fnSplitString('ABC:DEF:GEH')

Result: