Comma Separated Value in SQL Query

 

--- create Table

create table Tbl_Exp(Id int identity(1,1) primary key,Emp_Name varchar(50),Working_Day varchar(5))

-- insert some test data

insert into Tbl_Exp values('Adi','M')

insert into Tbl_Exp values('Adi','T')

insert into Tbl_Exp values('Adi','W')

insert into Tbl_Exp values('Adi','TH')

insert into Tbl_Exp values('Pavan','F')

insert into Tbl_Exp values('Pavan','S')

insert into Tbl_Exp values('Pavan','M')

insert into Tbl_Exp values('Madhan','T')

insert into Tbl_Exp values('Madhan','W')

select * from Tbl_Exp

--how to use stuff [to replace some data], by using below query we are going to Replace 1st 2 letters with "***"

select stuff('abcdef',1,2,'***')

-- How to get comma separated value using XML path

Select ','+Working_Day from Tbl_Exp for xml path('')

-- how to remove 1 st values using stuff

select stuff((Select ','+Working_Day from Tbl_Exp for xml path('')),1,1,'')

-- Final Query

select distinct Emp_Name,

(select stuff((Select ','+Working_Day from Tbl_Exp as a where a.Emp_Name=b.Emp_Name for xml path('')),1,1,'') as a) as 'comma_separated'

from Tbl_Exp as b

How to Display the Previous Row and Next Row value in SELECT statement

 In SQL Server by using LAG & LEAD We can find then Previous & Next row values as below example.

-- create Table

create table Tbl_Test(Id int)

-- Insert some test data into Table

insert into Tbl_Test values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)

-- find Current,Previous Row Value

select Id,Lag(Id,1,0) over(Order by Id) as Previous_Value  from Tbl_Test

-- find Current,Next Row Value 

select Id,LEAD(Id,1,0) over(Order by Id) as Next_Value  from Tbl_Test

-- Display Previous,Current, Next row Values

select Lag(Id,1,0) over(Order by Id) as Previous_Value,Id as Current_Value,LEAD(Id,1,0) over(Order by Id) as Next_Value  from Tbl_Test

-- Display 2nd Previous,Current, 2nd Next row Values

select Lag(Id,2,0) over(Order by Id) as '2nd_Previous_Value',Id as Current_Value,LEAD(Id,2,0) over(Order by Id) as '2nd_Next_Value'  from Tbl_Test

tab delimited text file writing and reading in c#

 

using System;

using System.IO;

using System.Text;

namespace tab_delimited

{

    class Program

    {

        static void Main(string[] args)

        {

            string fileName = @"C:\Temp\Adi.txt";

            try

            {

                // Check if file already exists. If yes, delete it.     

                if (File.Exists(fileName))

                {

                    File.Delete(fileName);

                }

                var delimiter = "\t";

                var newline = "\n";

                StringBuilder sb = new StringBuilder();

                sb.Append("1" + delimiter + "2" + delimiter + "3" + delimiter + "4" + delimiter + "5");

                sb.Append(newline);

                sb.Append("6" + delimiter + "7" + delimiter + "8" + delimiter + "9" + delimiter + "10");

                sb.Append(newline);

                sb.Append("11" + delimiter + "12" + delimiter + "13" + delimiter + "14" + delimiter + "15");

                sb.Append(newline);

                sb.Append("16" + delimiter + "17" + delimiter + "18" + delimiter + "19" + delimiter + "20");

                // Create a new file     

                using (FileStream fs = File.Create(fileName))

                {

                    //// Add some text to file    

                    Byte[] title = new UTF8Encoding(true).GetBytes(sb.ToString());

                    fs.Write(title, 0, title.Length);

                }

                // Open the stream and read it back.    

                using (StreamReader sr = File.OpenText(fileName))

                {

                    string s = "";

                    while ((s = sr.ReadLine()) != null)

                    {

                        Console.WriteLine(s);

                    }

                }

                Console.ReadLine();

            }

            catch (Exception Ex)

            {

                Console.WriteLine(Ex.ToString());

            }

        }

    }

}

Output: