Feeds:
Posts
Comments

Archive for the ‘SQL Solutions’ Category

Earlier, I have written a blog post about how to split a single row data into multiple rows using XQuery. Today, I came across a situation where I had to split a single column data into multiple columns using delimiter.

Lets me create a sample to demonstrate the solution.

Sample :

USE TEMPDB 
GO
DROP TABLE [dbo].[tbl_Employee]
GO
CREATE TABLE [dbo].[tbl_Employee](
      [Employee ID] INT IDENTITY(1,1) ,
      [Employee Name] VARCHAR (100) NOT NULL)
GO
INSERT INTO dbo.[tbl_Employee] ([Employee Name]) 
VALUES ('Andreas Berglund T')
GO
INSERT INTO dbo.[tbl_Employee] ([Employee Name]) 
VALUES ('Sootha Charncherngkha T')
GO
INSERT INTO dbo.[tbl_Employee] ([Employee Name]) 
VALUES ('Peng Wu')
GO

--Browse the data
SELECT * FROM dbo.[tbl_Employee]
GO

Split single column into multiple columns.1.0

Solution :
Given below is the solution, where we need to convert the column into xml and then split it into multiple columns using delimiter. You can use any delimiter in the given below solution. In my sample, I used a blank space as a delimiter to split column into multiple columns.

USE TEMPDB 
GO

DECLARE @delimiter VARCHAR(50)
SET @delimiter=' '  -- <=== Here, you can change the delimiter.
;WITH CTE AS 
( 
    SELECT 
        [Employee ID], 
        [Employee Name],
        CAST('' + REPLACE([Employee Name], @delimiter , '') + '' AS XML) 
        AS [Employee Name XML]
    FROM  [tbl_Employee] 
)
SELECT 
     [Employee ID], 
     [Employee Name],
     [Employee Name XML].value('/M[1]', 'varchar(50)') As [First Name],
     [Employee Name XML].value('/M[2]', 'varchar(50)') As [Last Name],
     [Employee Name XML].value('/M[3]', 'varchar(50)') As [Middle Name]
 
FROM CTE 
GO

Split single column into multiple columns.1.1

Let me know if you come across this situation and its solution.

Read Full Post »

Earlier this week, my team & I were working on financial reports and we developed some giant scripts in order to generate report data. Once we were done with the report, we came to know that we did not implement dynamic sorting in the report. Ooopssss !!!. Now do we need to re-write the query and convert into dynamic query ? 😦 Of course NOT.
What you can actually do is, write a conditional case and make it dynamic sort but it will increase the size of your query depending upon how many conditions you have.

In today’s post I will implement the dynamic sorting using CHOOSE function & CASE Statement. First of all, I will find the column number using CASE Statement and then pass the Column order number to CHOOSE function to actually sort the column. It will actually reduce the size of your query.

Given below is the script which will dynamically sort the query.

Use AdventureWorks2014
GO

DECLARE @SortCoulmnName VARCHAR(50) = 'OrderDate';
DECLARE @SortColumnNumber AS INT 

SET @SortColumnNumber = CASE 
When @SortCoulmnName='SalesOrderID' THEN 0 
WHEN @SortCoulmnName='OrderDate' THEN 1
WHEN @SortCoulmnName='DueDate' THEN 2
WHEN @SortCoulmnName='ShipDate' THEN 3 
ELSE 0
END
-- By Default, it will sort on first column

SELECT SalesOrderID
,OrderDate
,DueDate
,ShipDate 
,Status
FROM
Sales.SalesOrderHeader
ORDER BY 
CHOOSE(@SortColumnNumber,SalesOrderID,OrderDate,DueDate,ShipDate) DESC
GO

dynamic order by 1.1

Let me know if you came across this issue and its solution as well.

Read Full Post »

On 26th May, we had a very informative session presented by Mr. Mohammed Owais (CTO at CAZAR) in SQL Server User Group meetup about Backups – not as simple as you think. He covered almost each and every aspect from full backup till tail log backup, however, a very nice question has been raised by an audience – ‘how to check the status of the backup / recovery along with the percentage via TSQL ?’. Because in most cases we have more than one DBA in an organization and sometimes they are geographically dispersed and if one of them takes backup / restore, how the others will come to know that he is performing any backup / restore using T-SQL.

Given below is the script which will give you the backup / restore progress along with the exact percentage and the user name (who is taking the backup).

USE master
GO
SELECT
  A.session_id As [Session ID]
, login_name As [Login Name]
, [command] As [Command]
, [text] AS [Script]
, [start_time] As [Start Time]
, [percent_complete] AS [Percentage]
, DATEADD(SECOND,estimated_completion_time/1000, GETDATE())
as [Estimated Completion time]
, [program_name] As [Program Name]
FROM sys.dm_exec_requests A
CROSS APPLY sys.dm_exec_sql_text(A.sql_handle) B
INNER JOIN sys.dm_exec_sessions C ON A.session_id=C.session_id
WHERE A.command in ('BACKUP DATABASE','RESTORE DATABASE')
GO

1 : While Taking Backup

USE master;
GO
BACKUP DATABASE AdventureWorks2012
TO DISK = 'C:\Data\AdventureWorks2012.Bak'
   WITH FORMAT,
      MEDIANAME = 'SQLServerBackups',
      NAME = 'Full Backup of AdventureWorks2012';
GO

raresql-Backup-Restore.1.2

2 : While Restoring Backup

USE master;
GO
RESTORE DATABASE AdventureWorks2012
FROM DISK = 'C:\Data\AdventureWorks2012.BAK'
WITH NORECOVERY
GO

raresql-Backup-Restore.1.1

Let me know if you came across this issue and its solution as well.

Read Full Post »

How to convert hexadecimal to binary became very interesting when I was trying to read SQL Server log. Most part of SQL Server log data is in Hexadecimal so you need to convert it from hexadecimal to multiple formats to read it properly. Generally, programmers use the hexadecimal table to convert it into binary. But I developed this solution using remainder method and used it in almost all my solutions, wherever I used SQL Server log. Given below is the script.

Script :

--DROP FUNCTION dbo.[UDF_Convert_Hex_to_Binary]
--GO
CREATE FUNCTION dbo.[UDF_Convert_Hex_to_Binary]
(
      @HEX VARBINARY(MAX)
)
RETURNS VARCHAR(MAX)
BEGIN

DECLARE @BINARY VARCHAR(MAX)

;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)

SELECT @BINARY=ISNULL(@BINARY,'')
+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 128) % 2)
+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 64) % 2)
+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 32) % 2)
+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 16) % 2)
+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 8) % 2)
+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 4) % 2)
+ CONVERT(NVARCHAR(1), (SUBSTRING(@HEX, Nums.n, 1) / 2) % 2)
+ CONVERT(NVARCHAR(1), SUBSTRING(@HEX, Nums.n, 1) % 2) 

FROM N4 Nums
WHERE Nums.n<=LEN(@HEX) 

RETURN @BINARY
END
GO

Example :

Select dbo.[UDF_Convert_Hex_to_Binary](0x1cFEEE) AS [Hex to Binary]
GO
Select dbo.[UDF_Convert_Hex_to_Binary](0x2efd) AS [Hex to Binary]
GO
--OUTPUT

How to convert hexadecimal to binary.1.1

Read Full Post »