Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

UNPIVOT

UNPIVOT:

UNPIVOT operation is almost opposite OR reverse to PIVOT operation, by rotating columns into rows data.

In given example user order count is returned month wise, but requirement is to display orders for user in rows, we applied UNPIVOT on month for order with specific users.


UNPIVOT Example:

 

  drop table #PivoteData

 

  Select * into #PivoteData from(                

                  

SELECT * FROM (SELECT U.LoginName as Name, 

CASE  Convert(Varchar(2),OrderDate,101)

 WHEN '01' THEN 'January'

 WHEN '02' THEN 'February'

 WHEN '03' THEN 'March'

 WHEN '04' THEN 'April'

 WHEN '05' THEN 'May'

 WHEN '06' THEN 'June'

 WHEN '07' THEN 'July'

 WHEN '08' THEN 'August'

 WHEN '09' THEN 'September'

 WHEN '10' THEN 'October'

 WHEN '11' THEN 'November'

 WHEN '12' THEN 'December'

 ELSE NULL

 END as [Month],

 O.OrderId

 FROM  [User] as U

 INNER JOIN [Order] as O on U.id=O.UserId --where O.state=@State

 

) AS S

PIVOT

(

Count(OrderId)

 

FOR [Month] IN ([January],[February],[March],[April],[May], [June],[ July],[ August],[September],[ October] ,[ November],[ December] ) )

     

AS UserOrderDetails

 

)

 

--Unpivot the table.

SELECT Name, [Month], OrderId

FROM

   (SELECT *

   FROM #PivoteData) p

UNPIVOT

   ([Month] FOR OrderId IN

      ([January],[February],[March],[April],[May], [June],[ July],[ August],[September],[ October] ,[ November],[ December]  )

)AS unpvt;

 

PIVOT relational operator

PIVOT:
PIVOT relational operator we are using to convert row level data to columns, which return   aggregate data for column. PIVOT rotates a table-valued expression by turning the unique values or single column row data from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output.

 Syntax:

SELECT <non-pivoted column>,

    [first pivoted column] AS <column name>,

    ...

    [last pivoted column] AS <column name>

FROM

    (<Query that produce Source data>)

    AS <alias for the source query>

PIVOT

(

    <aggregation function>(<column being aggregated>)

FOR

[<column that contains the values that will become column headers>]

    IN ( [first pivoted column],

    ... [last pivoted column])

) AS <alias for the pivot table>

<optional ORDER BY clause>;

 

Example:

Below query returns data from order and user table

SELECT U.LoginName AS Name,OrderId,OrderDate,Total,

 CASE HasBeenShipped 

 WHEN 0 THEN 'NO'

 ELSE 'YES'

 END AS [HasBeenShipped]

 FROM  [User] as U

 INNER JOIN [Order] AS O ON U.id=O.UserId WHERE U.LoginName in ('Anushka',

'Nagnath',

'Sanskruti',

'Shourya') ORDER BY ORderID ASC


Now I want to check number of orders by month for this data

Pivot Example:

---Number of orders per month for users

 

SELECT * FROM (SELECT U.LoginName as Name, 

CASE  Convert(Varchar(2),OrderDate,101)

 WHEN '01' THEN 'January'

 WHEN '02' THEN 'February'

 WHEN '03' THEN 'March'

 WHEN '04' THEN 'April'

 WHEN '05' THEN 'May'

 WHEN '06' THEN 'June'

 WHEN '07' THEN 'July'

 WHEN '08' THEN 'August'

 WHEN '09' THEN 'September'

 WHEN '10' THEN 'October'

 WHEN '11' THEN 'November'

 WHEN '12' THEN 'December'

 ELSE NULL

 END as [Month],

 O.OrderId

 FROM  [User] as U

 INNER JOIN [Order] as O on U.id=O.UserId --where O.state=@State

 

) AS S

PIVOT

(

Count(OrderId)

 

FOR [Month] IN ([January],[February],[March],[April],[May], [June],[ July],[ August],[September],[ October] ,[ November],[ December] ) )

     

AS UserOrderDetails


 

RAISERROR

RAISERROR:

Used to return error message back to application,system warning or error message generated by Sql server database engine.

Syntax:

RAISERROR ( { msg_id | msg_str | @local_variable }

    { ,severity ,state }

    [ ,argument [ ,...n ] ] )

    [ WITH option [ ,...n ] ]

 

msg_id:

user defined error number stored in sys.messages catalog view using sp_addmessage.  user defined number should be greater than 5000. When msg_id not specified RAISERROR raises an error message with default msg_id 50000.

msg_str:

user defined error message,The error message can have a maximum of 2,047 characters.

When msg_str is specified, RAISERROR raises an error message with an error number of 50000.

Other argiuments are severity,state ,argument,option.

 

SQL Server supports the following functions, to describe error or exception details in catch block:

ERROR_NUMBER(): The number assigned to the error.

ERROR_LINE(): The line number inside the routine that caused the error.

ERROR_MESSAGE(): The error message text, which includes the values supplied for any substitutable parameters, such as times or object names.

ERROR_SEVERITY(): The error’s severity.

ERROR_STATE(): The error’s state number.

ERROR_PROCEDURE(): The name of the stored procedure or trigger that generated the error.

Example:

-- =============================================

-- Author:        Nagnath Kendre

-- Create date: <Create Date,,>

-- Description:   Insert's Emplyee Detail, exception handlled with try catch block and RAISERROR

-- =============================================

CREATE PROCEDURE InsertEmplyeeDetails_SP

      -- Add the parameters for the stored procedure here

      @EmployeeName Nvarchar(250),

      @Country Nvarchar(50),

      @PrimaryLanguage Nvarchar(150),

      @CompanyPlant Nvarchar(150),

      @ManagerId int

AS

BEGIN

      -- SET NOCOUNT ON added to prevent extra result sets from

      -- interfering with SELECT statements.

 SET NOCOUNT ON;

 BEGIN TRY

 BEGIN TRANSACTION

  INSERT INTO Employee

([EmployeeName],[Country],[PrimaryLanguage],[CompanyPlant],[ManagerId],[CreatedDate])

 VALUES (@EmployeeName,@Country,@PrimaryLanguage,@CompanyPlant,@ManagerId,GETDATE())

 

 COMMIT TRANSACTION;

 END TRY

 

BEGIN CATCH

IF @@TRANCOUNT > 0

    ROLLBACK TRANSACTION;

 

    DECLARE @ErrorNumber INT = ERROR_NUMBER();

    DECLARE @ErrorLine INT = ERROR_LINE();

    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();

    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();

    DECLARE @ErrorState INT = ERROR_STATE();

 

    PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));

    PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));

 

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)

 

END CATCH

 

END

GO

 

Result:

EXEC  [dbo].[InsertEmplyeeDetails_SP]

            @EmployeeName = N'Sanskruti',

            @Country = N'INDIA',

            @PrimaryLanguage = N'Marathi',

            @CompanyPlant = N'Pune',

            @ManagerId = abd