GROUPBY,ROLLUP AND CUBE In SQL Server

     Here I am discussing about ROLLUP and CUBE in SQL Server. Both functions are used for aggregation. So we can check this with an example.

DECLARE @TransLog TABLE
(
ID INT PRIMARY KEY IDENTITY(1, 1),
ItemName VARCHAR(50) NOT NULL,
CompanyName VARCHAR(50) NOT NULL,
Amount MONEY NOT NULL
)


INSERT INTO @TransLog
SELECT 'Mobile','Walmart',5000
UNION ALL
SELECT 'Mobile','Amazon',6000
UNION ALL
SELECT 'Mobile','Amazon',4000
UNION ALL
SELECT 'Mobile','FlipKart',6000
UNION ALL
SELECT 'TV','Walmart',3000
UNION ALL
SELECT 'TV','Amazon',2000
UNION ALL
SELECT 'TV','FlipKart',8000
UNION ALL
SELECT 'TV','FlipKart',6000


SELECT * FROM @TransLog




     The above table shows Different product and amount received for each product. If we want to calculate total amount received for each product we can use GROUPBY in SQL Server.

SELECT
ItemName, SUM(Amount) AS TotalAmount
FROM @TransLog
GROUP BY ItemName






Next we want item wise total and grand total of whole product , here we are applying ROLLUP

SELECT
COALESCE (ItemName, 'Grand Total') AS ItemName,
SUM(Amount) AS TotalAmount
FROM @TransLog
GROUP BY ROLLUP (ItemName)




     We got item wise total and grand total using ROLLUP .Then Next I want all company wise total and Grand total using CUBE

SELECT
COALESCE (ItemName,'All TransType') AS ItemName,
COALESCE (CompanyName, 'All Company') AS Company,
SUM(Amount) AS Total
FROM @TransLog
GROUP BY CUBE (CompanyName, ItemName)











Split comma separated string In SQL Server



           In this short article I will share with an example, how to split and convert a comma separated / delimited string to a different column.We have to create function to split string using delimiter. here i am sharing simple way to split a string in SQL Server by casting data to XML.



DECLARE @TEMPTABLE AS TABLE
(
  Remarks VARCHAR(50)
)

INSERT INTO @TEMPTABLE
SELECT 'Amal,TravelCost,100'
UNION ALL
SELECT 'Arun,TravelCost,200'
UNION ALL
SELECT 'Manu,TravelCost,300'
UNION ALL
SELECT 'Vinu,TravelCost,400'

SELECT * FROM @TEMPTABLE

SELECT
CAST(('<X>'+REPLACE(Remarks,',' ,'</X><X>') +'</X>') AS XML).value('(/X)[1]', 'varchar(128)') AS Customer,
CAST(('<X>'+REPLACE(Remarks,',' ,'</X><X>') +'</X>') AS XML).value('(/X)[2]', 'varchar(128)') AS TransType,
CAST(('<X>'+REPLACE(Remarks,',' ,'</X><X>') +'</X>') AS XML).value('(/X)[3]', 'varchar(128)') AS Amount
FROM @TEMPTABLE






  
     In the above example first we cast the data to XML and split string to separate column by finding the position of XML closing tag.

You can also split string using STRING_SPLIT function in SQL server .

SELECT VALUE
FROM @TEMPTABLE
CROSS APPLY STRING_SPLIT(Remarks, ',')




 

Dead Lock In SQL Server


     
     A deadlock is a situation where in two transactions wait for each other to give up their respective locks. Transaction A attempts to update table 1 and subsequently read data from table 2, then transaction B attempts to update table 2 and subsequently read data from table 1. In such situations, transaction A holds locks that transaction B needs to complete its task and vice versa; neither transaction can complete until the other transaction releases locks. So in this situation SQL server throws exception and kill the process and roll back it for other process

how to trace DeadLock in SQL Server
  Below administrative query will help you to find the DeadLock, If you click on xml data you will get a dead lock report




SELECT
xed.value('@timestamp', 'datetime2(3)') as CreationDate,
xed.query('.') AS XEvent
FROM
(
SELECT CAST([target_data] AS XML) AS TargetData
FROM sys.dm_xe_session_targets AS st
INNER JOIN sys.dm_xe_sessions AS s
ON s.address = st.event_session_address
WHERE s.name = N'system_health'
AND st.target_name = N'ring_buffer'
) AS Data
CROSS APPLY TargetData.nodes('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData(xed)
ORDER BY CreationDate DESC


Using STUFF and STRING_AGG In SQL Server



        The STUFF function is insert a string into another string .It removes the specified length of characters and insert new string to matching position 

   SELECT STUFF('Hi Rahul', 1, 2, 'Mr') AS Name





       From the above query it will replace Hi to Mr. You can use the STUFF with XML Path for concatenate the column data easily.

DECLARE @TempTable TABLE(Id INT,Name VARCHAR(20))

INSERT INTO @TempTable
SELECT 1, 'Raju'
UNION ALL
SELECT 2, 'Manu'
UNION ALL
SELECT 3, 'Vinu'

SELECT * FROM @TempTable

SELECT
   STUFF(
                (SELECT 
                ',' + Name 
           FROM @TempTable FOR XML PATH ('')), 1, 1, ''
               ) AS NAMES
FROM @TempTable WHERE ID = 1




       
      To handle this exact scenario, MS introduced new functionality called STRING_AGG in SQL 2016,2017. STRING_AGG Concatenates the values of string expressions and places separator values between them.

DECLARE @TempTable TABLE(ID INT,Name VARCHAR(20))

INSERT INTO @TempTable
SELECT 1, 'Raju'
UNION ALL
SELECT 2, 'Manu'
UNION ALL
SELECT 3, 'Vinu'

SELECT * FROM @TempTable


SELECT
     ID, STRING_AGG(Name, ',') AS NAMES
FROM @TempTable
GROUP BY ID 


      I believe this would be help full method for what you are trying to accomplish with the XML/STUFF method.