Archive for December, 2008

h1

SQL SERVER: Find Duplicate Rows with Row_Number

12/31/2008

This is very known question to find Duplicate Rows from the Table and Delete Duplicate rows from the table

I came to know about “ROW_NUMBER()” of SQL SERVER 2005.

Lets see how we can use this feature:

There is one Table:

CREATE TABLE FindDuplicates(
EmpID INT IDENTITY(1,1),
EmpName VARCHAR(500)
)

INSERT some data into the table:

INSERT INTO FindDuplicates VALUES('A')
INSERT INTO FindDuplicates VALUES('A')
INSERT INTO FindDuplicates VALUES('B')
INSERT INTO FindDuplicates VALUES('C')
INSERT INTO FindDuplicates VALUES('C')
INSERT INTO FindDuplicates VALUES('D')
INSERT INTO FindDuplicates VALUES('D')

Now we have data like:

Find Duplicate Rows

Now, we need to Find the duplicate Rows from the table. So we need to get “A”, “C” and “D”.

Lets see the use of Row_Number():

;With CTE AS(
SELECT ROW_NUMBER() OVER(PARTITION BY EmpName ORDER BY EmpName) As RowID,
EmpID,
EmpName
FROM FindDuplicates
)
SELECT *
FROM CTE
WHERE RowID > 1

Output will be like:

Find Duplicate Rows

So we can Delete Duplicate Rows as we have EmpID (Primary Key)

I Used:

PARTITION BY: which will find same rows and assign ID accordingly.

ORDER BY: In which order we want to assign ID. By Default its Ascending

h1

COALESCE – To Get Comma Separated List SQL SERVER

12/29/2008

Today in one report, i need to display the comma seprated data from table. So use the COALEASE feature and will share with you all.

You can use it like:

DECLARE @result VARCHAR(MAX)
SELECT @result = COALESCE(@result + ', ', '') + TestTejas.ColumnName
FROM
TestTejas

SELECT @result

h1

How to Add Computed Column – SQL SERVER 2005

12/24/2008

Today i come with scenario in which I need to add one computed column.

Computed column means we can manipulate one or more column from the same table and we can use this column in queries as regular column.

Lets try it:

Create One Table: tblTestComputed

CREATE TABLE tblTestComputed(
FirstName VARCHAR(50),
LastName VARCHAR(50)
)

Lets insert some data into it:

INSERT INTO tblTestComputed(FirstName, LastName)
SELECT 'Tejas', 'Shah'
UNION
SELECT 'Hiral', 'Shah'

So now I run:

SELECT * FROM tblTestComputed

I will get output like:

Add Computed column

Now, I need display name as “Shah Tejas”, “Shah Hiral” like that. so what I did is, I added one new column as:


ALTER TABLE tblTestComputed
ADD FullName AS (ISNULL(LastName,'') + ' ' + ISNULL(FirstName,''))

So, now if I run:

SELECT * FROM tblTestComputed

So, Output like:

Add computed Columns

So, I can use this new column “FullName” everywhere to display Full Name.

Computed Column is auto updated, means if FirstName OR lastName is changed then content of this column is also changed accordingly

one more thing We can not update computed column as it computed by other columns


h1

How to Read values from XML Variable/Columns with SQL SERVER 2005

12/23/2008

Read Attributes value from XML variable:

DECLARE @xml XML
SELECT @xml = 
'<TejasShah>
  <Att ID="Tejas" Text="TejasShah" />
  <Att ID="Tejas2" Text="TejasShah2" />
  <Att ID="Tejas3" Text="TejasShah3" />
</TejasShah>'

SELECT  x.v.value('@ID[1]', 'VARCHAR(100)') AS [ID],
        x.v.value('@Text[1]', 'VARCHAR(100)') AS [Text]
FROM    @xml.nodes('/TejasShah/Att') x(v)

Output will be:

Read values from xml variable

In other way, we can  also get value from ELEMENTS of xml variable. Lets see:

DECLARE @xml XML
SELECT @xml = '
<TejasShah>
  <Att>
    <ID>Tejas</ID>
    <Text>TejasShah</Text>
  </Att>
  <Att>
    <ID>Tejas2</ID>
    <Text>TejasShah2</Text>
  </Att>
  <Att>
    <ID>Tejas3</ID>
    <Text>TejasShah3</Text>
  </Att>
</TejasShah>'
SELECT  x.v.value('ID[1]', 'VARCHAR(100)'),
        x.v.value('Text[1]', 'VARCHAR(100)')
FROM    @xml.nodes('/TejasShah/Att') x(v)

Output will be the same as above:

Read values from XML Elements

h1

SQL SERVER – Get Time in AM/PM Format

12/22/2008

Today i come with one situation where I need to get Time in AM/PM format.

I tried with different way and come up with the simple solution, that i am going to share with all of you.


SELECT
GETDATE() AS CurrentDate,
RIGHT(CONVERT(VARCHAR, GETDATE(), 100),7) AS CurrentTime,
CONVERT(VARCHAR(10), GETDATE(), 101) + + RIGHT(CONVERT(VARCHAR, GETDATE(), 100),7) AS CurrentDateTime

SQL DateTime

Syntax to Get SQL DateTime With Am/Pm Format

SQL Date time in AM/PM Format

Get SQL Date time in AM/PM Format

I have read very interesting article on SQL Authority.Com . You can find the many ways to get date in different formats.