Pivot result in T-SQL

Sometimes it is required to get query output in tabular form based on cross-tab format. There may be multiple reasons for such output like direct display to front-end, export to file or may be simple analysis. As such sql server provides PIVOT feature to accomplish this task.

Consider a case where shop keeper wants to know what were the sales of each individual item of the shop based on each individual sale invoice. Below is output of how pivot feature can give the desired result:

This is achieved with following query:

SELECT row_number() OVER (ORDER BY SalesInvoiceCode) Ser , * 

FROM   

(

select  

sim.SalesInvoiceCode,

case 

when a.ID = 1935 then isnull(c.[Description],a.Item) else a.Item 

end as [Item],

sum(isnull(c.Rate, a.SaleRate) *  isnull(c.Quantity,0)) as Sale

from ERP_SCM_INV_ITEM a

inner join ERP_MASTER_Setup_ItemNature b on b.ItemNatureID_PK = a.ItemNatureID_FK and b.ItemNature = 'posmenu'

inner join ERP_FMS_SalesInvoice_Detail c on c.ItemID_FK=a.ID

inner join ERP_FMS_SalesInvoice_Master sim on sim.SalesInvoiceID_PK = c.SalesInvoiceID_FK

inner join POS_OrderPayment pop  on pop.InvoiceId = sim.SalesInvoiceID_PK

inner join ERP_FMS_SalesInvoice_Types sit on sit.SalesInvoiceTypeID_PK = pop.DiningType

where sim.SalesInvoiceCode like '%pos%'

and cast(sim.CreatedDate as date) = '2020-01-13'  

and sim.TransactionID_FK > 0 and (sim.closed = 0 and sim.IsCancel is NULL)

and pop.FK_POS_LocationId = '1' 

group by 

case 

when a.ID = 1935 then isnull(c.[Description],a.Item) else a.Item 

end ,

sim.SalesInvoiceCode

) t

pivot(

sum(Sale)

for Item in ([Barbosa Cake],[Coffee],[Delivery Charges],[Sitting Charges]

)

)

AS pivot_table

union

SELECT NULL as Ser,* FROM

(

select  

'Total' as SalesInvoiceCode, 

case 

when a.ID = 1935 then isnull(c.[Description],a.Item) else a.Item 

end as [Item],

sum(isnull(c.Rate, a.SaleRate) *  isnull(c.Quantity,0)) as Sale

from ERP_SCM_INV_ITEM a

inner join ERP_MASTER_Setup_ItemNature b on b.ItemNatureID_PK = a.ItemNatureID_FK and b.ItemNature = 'posmenu'

inner join ERP_FMS_SalesInvoice_Detail c on c.ItemID_FK=a.ID

inner join ERP_FMS_SalesInvoice_Master sim on sim.SalesInvoiceID_PK = c.SalesInvoiceID_FK

inner join POS_OrderPayment pop  on pop.InvoiceId = sim.SalesInvoiceID_PK

inner join ERP_FMS_SalesInvoice_Types sit on sit.SalesInvoiceTypeID_PK = pop.DiningType

where sim.SalesInvoiceCode like '%pos%'

and cast(sim.CreatedDate as date) = '2020-01-13'   

and sim.TransactionID_FK > 0 and (sim.closed = 0 and sim.IsCancel is NULL)

and pop.FK_POS_LocationId = '1' 

group by 

case 

when a.ID = 1935 then isnull(c.[Description],a.Item) else a.Item 

end 

)

t2

pivot(

sum(Sale)

for Item in ([Barbosa Cake],[Coffee],[Delivery Charges],[Sitting Charges]

)

)

AS pivot_table2

order by SalesInvoiceCode

Shrink SQL server database log

Database log increases with increasing transactions and database activity. At times it is not required to maintain the log after some period. So in such cases it becomes quite beneficial to shrink the database log file as it saves much disk space. Below query can be used to do the needful.

Output:

sometimes it is better to set the database to simple recovery before compressing log.

Consider below query in those cases:
use @databaseName;
Go 
ALTER DATABASE @databaseName SET RECOVERY SIMPLE
DBCC shrinkfile (@logFileName, 8);
ALTER DATABASE @databaseName SET RECOVERY FULL

Retrieve Comma Separated Values

Some times there are certain scenarios where data needs to be gathered as a collection. For instance multiple records need to be set as single record, in a such cases setting row data as comma separated column value can be quite effective.

SQL server allows to incorporate this with its built-in feature of stuff allowing row data to be merged as a single column value.

Code:

select  

(STUFF((SELECT ',' + CAST(a.username AS VARCHAR(10))+''' ' [text()]

FROM [users] a

FOR XML PATH(''), TYPE)

.value('.','NVARCHAR(MAX)'),1,2,' '))

as AllNames 

Output:

User-Defined Functions in SQL Server

In SQL Server user can define function to return a table or some value. Following are two ways of creating function:

1- Returning Table

-----------------Returing table

create function printTwo

(@projectId varchar, @resourceId varchar)

returns table as

return

(

select @projectId as [proj],@resourceId as [resc]

);

2- Returning Scalar(single) value

-----------------Returing scalar

create function printOne

(

@value int

)

returns int

WITH EXECUTE AS CALLER 

as

begin

return @value

end 

3- Checking the output by calling in different ways 

select dbo.printOne(1) 'Result';

select * from dbo.printTwo('1','2');

select dbo.printOne(1) 'Result', * from dbo.printTwo('a','b');

Export sql server query output to CSV - via BCP utility

To export SQL query data to a comma separated value (CSV) file we can use Bulk Copy Program (bcp) utility. Follow below steps to get the desired CSV file.

1. Enable xp_cmdshell property to use the bcp utility 
   
2. Write the desired query and passed it in the xp_cmdshell command 
   

    3. Output CSV

HTTP Request Via SQL Server Query

Hello again, in this post we will see how to perform an Hypertext transfer protocol (HTTP) request through SQL server query. However remember that this action is not a general practice, it should be performed for specific cases like for instances:

• Data to be posted to some system at day end or specific time interval
• XML or JSON based data needs to be retrieved from integrated systems
• In a case where security is not a major concern (like internal systems)

This method is supposed to be less efficient in terms of execution time as compared to the traditional web request done by the web applications. So I would recommend to use this only if you have a specific requirement like ones mentioned above or if you do not have any other way then this.

Following are steps to create HTTP request via SQL query:

1. First check if  OLE (Object Linking and Embedding) Automation Procedures are enabled from the following built-in stored procedure:

           EXEC sp_configure 'Ole Automation Procedures'; 

           The config_value and run_value should be 1

     2. If not enabled already then execute another built-in SQL SP of sp_configure:

           exec sp_configure 'show advanced options', 1;  

           GO  

           RECONFIGURE;  

           GO  

           exec sp_configure 'Ole Automation Procedures', 1;  

           GO  

           RECONFIGURE;  

           GO  

    3. Now to perform a simple request consider the below query where data is retrieved in JSON format:

DECLARE @obj int,

        @url VarChar(MAX),

        @response VarChar(8000),

        @requestHeader VarChar(MAX),

        @requestBody VarChar(MAX),

@status int

SET @url = 'http://65.181.112.199:88/default.aspx?source=CategoryCombo'

EXEC sp_OACreate 'MSXML2.ServerXMLHttp', @obj OUT

EXEC sp_OAMethod @obj, 'Open', NULL, 'GET', @url, 'false'

Exec sp_OAMethod @obj, 'Send'

EXEC sp_OAGetProperty @obj, 'responseText', @response OUT

Exec sp_OAMethod @obj, 'status', @status OUTPUT

EXEC sp_OADestroy @obj

SELECT @response [RESPONSE], @status [Status]

   4. Result:

For more information visit stack overflow:

http://stackoverflow.com/questions/19307704/how-read-json-data-from-url-in-sql-server

SQL Server Stored Procedure with input parameter

Hello. Hope you are doing good. In this post we will learn how to create an SQL stored procedure with input parameter.

Consider following steps:

1. Use create procedure command to create procedure like "create procedure abc"
2. Define input parameter with @ to denote parameter values like '@Name varchar(15)', note that @Name is a parameter of type varchar with 15 character length. You can add multiple parameters by separating each with a comma (,)
3. Write the SQL command you want the procedure to execute. For this example we will print name with today's date:

     create procedure showDateWithName 

     @Name varchar(15)

     as

     begin

     select 'Hello '+@Name +'! today is '+ convert(varchar,GETDATE(),106);

     end

• To use the stored procedure run the execute command:

       execute showDateWithName 'Hasan'

Procedure Result