Convert Semicolon Separated String in Rows Along With Respective Data

I have a situation, have a data in table with Primary key and one data field (assume it is a company) and third field contain the information of Person working in it in semicolon/comma separated format.

CREATE TABLE SemicolonSep

(

ID INT,

Company Nvarchar(20) ,

person VARCHAR(100)

)

INSERT SemicolonSep SELECT 1, 'A', 'Person1;Person2;Person3'

INSERT SemicolonSep SELECT 2, 'B', 'Person1'

INSERT SemicolonSep SELECT 3, 'C', ''

INSERT SemicolonSep SELECT 4, 'D', 'Person1;Person2;Person5;Person7'

INSERT SemicolonSep SELECT 5, 'E', 'Person1;Person2;Person5;Person7;Person15;Person17'

select * from SemicolonSep

;WITH temp(id, Company, NewData, person) AS

(

SELECT

id,

Company,

cast(LEFT(person, CHARINDEX(';', person + ';') - 1) as nvarchar),

STUFF(person, 1, CHARINDEX(';', person + ';'), '')

FROM SemicolonSep

UNION all

SELECT

id,

Company,

cast(LEFT(Person, CHARINDEX(';', Person + ';') - 1) as nvarchar),

STUFF(Person, 1, CHARINDEX(';', Person + ';'), '')

FROM temp

where Person > ''

)

SELECT

id,

Company,

NewData

FROM temp

ORDER BY id

Simple enough, You can use this output any where in further logic breakdowns as well as store output in any temp table or permanent table to use this data further.

Keywords:

1. Convert semicolon separated string in rows
2. Convert comma separated string in rows (just replace semicolumn with comma)
3. Demoralize data
4. Column to rows

SQL Server | DB Restore Error

When you are working on the task of database backup and restores, you might have error while restroe the database "database in use". This error populate because the current database is in use on same machine or over the network. There can be below cases:
1. On SQL Server database query window is open
2. Database query window will be open on any other computer in network
3. Database is currently connected with application

So in order to restore the database you need to disconnect the application from you machine or from another machine over the network.

Now, it is easy to disconnect the query window which is opened in your machine

Problem
It is difficult for you if any body using database over the network. Then how to disconnect all the connections

Solution

Sql Server 2012
DECLARE @query_v nvarchar(4000) = '';  
SELECT @query_v = @query_v + 'kill ' + CONVERT(varchar(10), session_id) + ';' FROM sys.dm_exec_sessions WHERE database_id  = db_id('Your_database_name')
EXEC(@query_v);


For SQL Server less than equal to 2008
DECLARE @query_v nvarchar(4000);
SELECT @query_v = @query_v + 'kill ' + CONVERT(varchar(10), spid) + ';' FROM master..sysprocesses WHERE dbid = db_id('Your_database_name')

EXEC(@query_v);

Note * - 
change the database name in above query, for which you want to restore the database
After change the database name you need to execute the above query


While query execution you can have below error

Msg 6104, Level 16, State 1, Line 1
Cannot use KILL to kill your own process.

Make sure while executing above query, close the current database query window or connect to "master" database

MACRO to List Out All Tab Names Along With Field Header Name

Some times we need spreadsheet information at one place, information like :

1. All tab Names available in spreadsheet
2. All Header Names

This is because one spreadsheet can have many tabs in it and it is not sure that all tabs having same number of header columns. This article can be helpful who are looking for all information at one place. This kind of information is useful in automation programs, to write/update the spreadsheet through automation.

More possibilities are at run time we are not aware of the structure of spreadsheet.

This kind of information further can be used for some automation process (specially in IT industry), since automation can save TIME and MONEY.

One approach, i tried we can use Pentaho Tool (Open Source ETL Tool) for this, but some where this tool not use full where spreadsheet size cross 1GB. Because in my end Pentaho needs 24GB of JVM space to extract data from spreadsheet.

To achieve this we have designed Macro VBA program, which can perform this tasks in minutes, no matters what the size is.

Pre-requisite :

Save your Spreadsheet in macro enable format, if it is not.

Macro Code:

Sub ListMeta()
    Dim Ws As Worksheet
    Dim MetaWS As Worksheet
    Dim HeaderRange As Range
    Dim FieldCount As Integer
    Dim RowNo As Long
    Dim Spreadsheet As Workbook

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    ''''''******* Delete sheet "MetaData-Sheet" ********** 
    Application.DisplayAlerts = False
    On Error Resume Next
    ThisWorkbook.Worksheets("MetaData").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True

    Set Spreadsheet = ThisWorkbook
    Set MetaWS = Spreadsheet.Worksheets.Add
    ' Add Metadata Sheet
 MetaWS.Name = "MetaData"

    
    RowNo = 1

    For Each Ws In Spreadsheet.Worksheets
        If Ws.Name <> MetaWS.Name And Ws.Visible Then
            FieldCount = 1
            RowNo = RowNo + 1
            'Copy the sheet name in the A column
            MetaWS.Cells(RowNo, 1).Value = Ws.Name

            For Each HeaderRange In Ws.Range("A1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1,L1")  ' you can change WS range as per your requirement
   
                FieldCount = FieldCount + 1
                MetaWS.Cells(RowNo, FieldCount).Formula = _
                "='" & Ws.Name & "'!" & HeaderRange.Address(False, False)
            Next HeaderRange
           

        End If
    Next Ws

    MetaWS.UsedRange.Columns.AutoFit

    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub