Concat string Example using CTE

WITH cte(Centerid,PatientId,patientName,RefDocName,PhoneNo ,visitid,VisitDate,StudyName,StudyState) as (
  Select
       P.CenterId,
       p.externalID as PatientId,
        P.FirstName as patientName,
        R.FirstName as RefDocName,
        P.Phone1 as PhoneNo,
        V.visitid,
        V.VisitDate,
        bm.billingname as StudyName,
        CASE when s.StudyState=4 THEN 'Inprogress'
             when s.StudyState=3 THEN 'Completed' End as StudyState
  from TblPatient
 
  as P JOIN TblVisits as V On V.Patientid=P.patientid and V.CenterId=P.CenterId
  JOIN TblStudy as S On S.Visitid=V.visitid and S.Centerid=V.Centerid
  JOIN TblBillingMaster as BM On BM.Billingid=S.Studytypeid and Bm.CenterId=S.CenterId
  JOIN tblReferralDoctor as R ON R.ReferalID=v.ReferalID and R.CenterId=V.CenterId
 
  Where P.Obsolete=0 and V.Obsolete=0 AND S.StudyState In(3,4) and bm.ModalityTypeID!=12 )

  

SELECT CenterId,PatientId,patientName,RefDocName,PhoneNo,visitid,VisitDate,StudyState as Status, STUFF((SELECT ',' + b.StudyName from cte b Where a.Centerid=B.Centerid and a.visitid=b.visitid  FOR XML PATH(''), TYPE ).value('.','varchar(max)') ,1,1, '') AS StudyName
FROM cte a
    GROUP BY    a.CenterId,a.PatientId,a.patientName,a.RefDocName,a.PhoneNo,a.visitid,a.VisitDate,a.StudyState
    
 

Delete Duplicate Rows Example

Delete A FROM (
     SELECT ROW_NUMBER()OVER(PARTITION BY PatientId ORDER BY LASTMODIFIEDDATE   DESC) RANK1,PatientId
     FROM TblPatient WHERE CenterId = 10 ) A  WHERE RANK1 > 1

Alter Example

--ADD Column
IF NOT EXISTS(Select * from INFORMATION_SCHEMA.COLUMNS
where Table_name='tblsample' and Column_name='MobileNo')
BEGIN
ALTER TABLE tblsample add MobileNo bigint NULL
END

--Create Table
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS         
WHERE TABLE_NAME = 'TblReportMaster' )       
BEGIN       
CREATE TABLE [dbo].[TblReportMaster](       
 [ReportID] [int] NULL,       
 [ReportDisplayName] [nvarchar](100) NULL,       
 [SPName] [nvarchar](100) NULL       
) ON [PRIMARY]       
END

--DROP COLUMN
IF  EXISTS(Select * from INFORMATION_SCHEMA.COLUMNS where Table_name='tblsample' and Column_name='SEX')
BEGIN
ALTER TABLE tblsample
DROP COLUMN SEX
END

--Set Primary key and Foreign Key
IF EXISTS(Select * from INFORMATION_SCHEMA.COLUMNS where Table_name='tblsample' and Column_name='SEX')
BEGIN
ALTER TABLE salesman
ADD CONSTRAINT prim_sales PRIMARY KEY (no),
CONSTRAINT foreign_sales FOREIGN KEY(product_no)
REFERENCES product
END

With Query Example

 with sam as 
(
 select * from TblSample
  )
  select * from  sam as s join TblSample1 as Sn on sn.id=s.id

OutPut
ID	Name	Age	Sex	ID	Name	Age	Sex
1	vino	23	Male	1	vino	23	Male
2	karthick	25	Male	2	karthick	25	Male
3	Rajesh	24	Male	3	Rajesh	24	Male

Insert Query Example

CREATE PROC  [dbo].[Tblsample_insert]
(
 @Name varchar(40),
 @Age int,
 @Sex varchar(45)
 )
AS
BEGIN
INSERT INTO [LINQ].[dbo].[TblSample]
           ([Name]
           ,[Age]
           ,[Sex])
     VALUES
           (@NAME,
            @Age,
            @Sex)
END

Merge Example

MERGE tblsample AS TARGET
USING
      (SELECT Id,
              Name,
              Age,
              Sex FROM tblsample1) AS Mer ON TARGET.id=Mer.id

 WHEN MATCHED AND
      
       (TARGET.Name<>mer.Name)or
       (TARGET.Age<>mer.Age)or
       (TARGET.Sex<>mer.Sex)

 THEN UPDATE
 SET    TARGET.Name=mer.Name,
        TARGET.Age=mer.Age,
        TARGET.Sex=mer.Sex
   
 WHEN NOT MATCHED BY TARGET
 THEN INSERT
        (
         Name,
         Age,
         Sex
         )
 VALUES
        ( mer.Name,
          mer.Age,
          mer.Sex
         );

Pivot example

DECLARE @listCol VARCHAR(2000)
DECLARE @query VARCHAR(4000)
SELECT  @listCol = STUFF(( SELECT DISTINCT
                                '],[' + ltrim(str(YEAR(transactiondate)))
                        FROM   TblBillingTransaction
                        ORDER BY '],[' + ltrim(str(YEAR(transactiondate)))
                        FOR XML PATH('')
                                    ), 1, 2, '') + ']'
print @listCol
SET @query =

'select * from(select scanamount, datepart(m,transactiondate) as Month ,year(transactiondate) as transactiondate  from TblBillingTransaction where Obsolete=0 and BillingId!=0)as v
pivot (sum(scanamount) for transactiondate
in('+@listCol+') ) as PVT order by 1 asc'


EXECUTE (@query)

RESULT

Month	2010	2011
1	1678000	8983115
2	4612000	11186996
3	5699800	10595608
4	5960000	9839065
5	6264010	9913435
6	7591395	11367465
7	9239360	12233055
8	7814870	11166010
9	8532125	11613364
10	8831590	12208845
11	8928700	7786655
12	10024105	NULL

STUFF Example

STUFF ( character_expression , start , length ,character_expression )

The following example returns a character string created by deleting three characters from the first string, abcdef, starting at position 2, at b, and inserting the second string at the deletion point.

SELECT STUFF('abcdef', 2, 3, 'ijklmn');
GO
Result
aijklmnef

At first sight it might not be clear directly how to insert a character (or a string) instead of substituting characters in the original string.
To insert a string, without replacing/substituting characters in the original string you need to specify a length of 0 (zero).

SELECT STUFF('abcghi', 4, 0, 'DEF');

Result
abcDEFghi

"Identify Missing Identity Values"

-- Step #1: Create Table and Populate with ValuesCREATE TABLE #CarType ([ID]
[Name] INT IDENTITY, VARCHAR(20) )INSERT INTO #CarType ( [Name] ) VALUES ( 'Bentley' )INSERT INTO #CarType ( [Name] ) VALUES ( 'BMW' )INSERT INTO #CarType ( [Name] ) VALUES ( 'Ferrari' )INSERT INTO #CarType ( [Name] ) VALUES ( 'Lamborghini' )INSERT INTO #CarType ( [Name] ) VALUES ( 'Hummer' )INSERT INTO #CarType ( [Name] ) VALUES ( 'Jaguar' )INSERT INTO #CarType ( [Name] ) VALUES ( 'Lexus' )INSERT INTO #CarType ( [Name] ) VALUES ( 'Mercedes Benz' )INSERT INTO #CarType ( [Name] ) VALUES ( 'Porsche' )INSERT INTO #CarType ( [Name] ) VALUES ( 'Volvo' )SELECT * FROM #CarType--The output of the SELECT statement will be as follows:ID Name------ --------------1 Bentley
2 BMW
3 Ferrari
4 Lamborghini
5 Hummer
6 Jaguar
7 Lexus
8 Mercedes Benz
9 Porsche
10 Volvo--Let's say certain records have been deleted from the table, as shown in the following script:
-- Step #2: Delete IDsDELETE FROM #CarType WHERE [ID] IN (3, 4, 9)SELECT * FROM #CarType--The table now has the following records:ID Name------ --------------1 Bentley
2 BMW
5 Hummer
6 Jaguar
7 Lexus
8 Mercedes Benz
10 Volvo--One way to identify the missing/deleted identity values is to loop through the table
--starting from 1 up to the maximum identity value as shown in the following script:
-- Step #3 (Option #1): Identify Missing IDENTITY ValuesDECLARE
DECLARE
DECLARE @ID INT @MaxID INT @MissingCarTypeIDs TABLE ( [ID] INT )SELECT @MaxID = [ID] FROM #CarTypeSET @ID = 1WHILE @ID <= @MaxIDBEGIN
IF NOT EXISTS (SELECT 'X' FROM #CarTypeWHERE [ID] = @ID)INSERT INTO @MissingCarTypeIDs ( [ID] )VALUES ( @ID )SET @ID = @ID + 1END
SELECT * FROM @MissingCarTypeIDs

Alter table

IF NOT EXISTS(SELECT * from INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='tblpatient' and COLUMN_NAME='Externalid')BEGIN
ALTER TABLE tblpatient ADD vinoth NVARCHAR(9) NULLEND

Very fast Clearing of a Table That has an Indexed View

The new feature allows very fast clearing of a table that has an indexed view on it by creating a staging table (call it T1_Stage) with the same structure as our T1 table, switching T1 to T1_Stage, and then dropping T1_Stage. All this can be done without dropping the view or even the index on it. Here’s the code to achieve the task:

CREATE TABLE dbo.T1_STAGE

(

  col1 INT NOT NULL PRIMARY KEY,

  col2 INT NOT NULL,

  col3 NUMERIC(12, 2) NOT NULL

);



ALTER TABLE dbo.T1 SWITCH TO dbo.T1_STAGE;



DROP TABLE dbo.T1_STAGE;

Check Temp Table

IF OBJECT_ID('tempdb..#temp') > 0          
     DROP TABLE #temp 
 
     SELECT * INTO  #temp FROM (SELECT * FROM TABLENAME)

     SELECT * FROM  #temp
     SELECT COUNT(*) FROM  #temp

MsSqlserver 2008 Cursor Example

DECLARE @RevFormatID INT
DECLARE @Centerid INT

DECLARE  @vin CURSOR
SET @vin= CURSOR  FOR  
SELECT DISTINCT ReportingCenterID
FROM vCenterSelect           
WHERE ISNULL(RevFormatID,1)=  1

OPEN @vin             
             
FETCH NEXT             
FROM @vin INTO @CenterId 
WHILE @@FETCH_STATUS = 0         
BEGIN   

SELECT UserId,CenterID from TblUserCenterMapping WHERE CenterID=@Centerid
            
FETCH NEXT             
FROM @vin INTO @CenterId   
END             
CLOSE @vin             
DEALLOCATE @vin