Try this

CREATE TABLE tblTest (ID INT, NAME VARCHAR(20),AGE INT)
INSERT INTO tblTest VALUES(1,'AAA',22),(1,'AAA',22),(2,'BBB',33),(2,'BBB',33),(2,'BBB',33),(3,'CCC',44),(4,'DDD',55)

--create an alternate table to store the duplicate records
CREATE TABLE tblTestDuplicate (ID INT, NAME VARCHAR(20),AGE INT)

-- insert those duplicate records to this new table
INSERT INTO tblTestDuplicate
SELECT *
FROM tblTest
GROUP BY ID,NAME,AGE
HAVING COUNT(ID) > 1

--delete the duplicate records from the original table
DELETE FROM
tblTest
WHERE ID IN (SELECT ID FROM tblTestDuplicate)

--insert all the records into the original table
INSERT INTO tblTest
SELECT *
FROM tblTestDuplicate

--Project the new records
SELECT *
FROM tblTest
ORDER BY ID

--clean up
DROP TABLE tblTestDuplicate
DROP TABLE tblTest

/*

ID NAME AGE
1 AAA 22
2 BBB 33
3 CCC 44
4 DDD 55

*/ Hope this helps

@samflexx, I have simulated your situation and have made a solution that I think matches your requirement. Since you said that

total records belonging to each division

the obvious idea that cropped up is to use the ROLLUP function. I am presenting the solution. Let us know if it has helped you

;WITH CTE AS(
SELECT
1 AS EMPNUM
,'File1'AS FILENAME
,'Name1' AS EMPNAME
,'Division1' AS Division UNION ALL
SELECT
2 AS EMPNUM
,'File2'AS FILENAME
,'Name2' AS EMPNAME
,'Division2' AS Division UNION ALL
SELECT
3 AS EMPNUM
,'File12'AS FILENAME
,'Name12' AS EMPNAME
,'Division1' AS Division UNION ALL
SELECT
4 AS EMPNUM
,'File14'AS FILENAME
,'Name14' AS EMPNAME
,'Division1' AS Division UNION ALL
SELECT
5 AS EMPNUM
,'File21'AS FILENAME
,'Name21' AS EMPNAME
,'Division2' AS Division UNION ALL
SELECT
6 AS EMPNUM
,'File3'AS FILENAME
,'Name3' AS EMPNAME
,'Division3' AS Division) -- table create and data insertion part

-- Query starts
SELECT
X.*
FROM(
SELECT
EMPNUM = ISNULL(CAST(t.EMPNUM AS VARCHAR(10)), ' ')
,FILENAME = ISNULL(CAST(t.FILENAME AS VARCHAR(10)), ' ')
,EMPNAME = ISNULL(CAST(t.EMPNAME AS VARCHAR(10)), ' ')
,Division = CASE
WHEN t.Division IS NULL AND t.EMPNUM IS NULL THEN 'Total :-'
WHEN t.EMPNUM IS NULL THEN 'Total ' + t.Division + ' Count :-'
ELSE t.Division END
,DivisionCount = COUNT(t.Division)
FROM CTE t
GROUP BY ROLLUP(t.Division,t.EMPNUM,t.EMPNAME,t.FILENAME))X
WHERE (LEN(X.FILENAME) > 0 AND LEN(X.EMPNAME) > 0)
OR (LEN(X.FILENAME) = 0 AND LEN(X.EMPNAME) = 0 AND LEN(X.EMPNUM) = 0)

The output

EMPNUM FILENAME EMPNAME Division DivisionCount
1 File1 Name1 Division1 1
3 File12 Name12 Division1 1
4 File14 Name14 Division1 1
Total Division1 Count :- 3
2 File2 Name2 Division2 1
5 File21 Name21 Division2 1
Total Division2 Count :- 2
6 File3 Name3 Division3 1
Total Division3 Count :- 1
Total :- 6

Try

SELECT SUSER_SNAME([Transaction SID])
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'

e.g.

USE [TestDB]
GO

--Create table tblTestEmployee
CREATE TABLE [dbo].[tblTestEmployee]
(
[EmployeeID] [int] IDENTITY(1,1) NOT NULL,
[EmpName] [varchar](100) NOT NULL,
[Address] [varchar](100) NOT NULL
)

--Populate some records to the tblTestEmployee
INSERT INTO [dbo].[tblTestEmployee]
VALUES('Emp1','Address1'),('Emp2','Address2'),('Emp3','Address3'),('Emp4','Address4')

--Drop the column Address
ALTER TABLE [dbo].[tblTestEmployee] DROP COLUMN [Address]

--Find who has done that
GO
SELECT SUSER_SNAME([Transaction SID])
FROM fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'
GO

Result sa You can also have a look at Audit SQL Server database and see who deleted a column value Hope this helps

Respected Sir, Hope you are doing well. The question being asked can be solved in 2 ways, as per my understanding. #1: If the data storage format is like StudentRoll,Name,English,Physics,Biology,Maths etc. (which he didn't asked ) In this case, in-order to flatten the data, first we will use UNPIVOT . Once done, the next step will be to use Row_Number() and get the N-th highest marks. Finally , to get the original structure back, we should apply PIVOT. #2: If the storage format is like SName,Subject,Marks(which he asked ) In this case, since the data is already flattened by Subjects, we must apply the RANKING function (as I applied Row_Number()) here and get the N-th highest one. Kindly let me know, if my understanding of your question is correct in which case please provide the feedback of the response.

Try this

DECLARE @T TABLE(SName VARCHAR(20), Subject VARCHAR(20), Marks INT, ExamDate DATE)
INSERT INTO @T
SELECT 'A', 'Subject1', 77, '2011-01-01' UNION ALL
SELECT 'A', 'Subject2', 97, '2011-01-01' UNION ALL
SELECT 'B', 'Subject1', 80 ,'2012-04-01' UNION ALL
SELECT 'B', 'Subject2', 70, '2012-03-01' UNION ALL
SELECT 'C', 'Subject1', 44, '2011-01-01' UNION ALL
SELECT 'C', 'Subject2', 90, '2011-01-01' UNION ALL
SELECT 'D', 'Subject1', 79 ,'2012-04-01' UNION ALL
SELECT 'D', 'Subject2', 66, '2012-03-01'

SELECT X.*
FROM ( SELECT
t.*
,Rn = DENSE_RANK() OVER(PARTITION BY t.Subject ORDER BY t.Marks DESC)
FROM @T t) X WHERE X.Rn = 3

/*

SName Subject Marks ExamDate Rn
A Subject1 77 2011-01-01 3
B Subject2 70 2012-03-01 3

*/

@indian1433, you can try with sp_MSforeachdb and sp_MSforeachtable .Please check out and let us know if the hint has helped you. N.B.~ Since you mentioned that it's just for internal purpose, so I have mentioned those two undocumented Stored Procedure. Please don't use it in production since they r undocumented and there is no guarantee that in future MS will not remove them (:

@indian1433, here is a way (The first version is static)

DECLARE @T TABLE(StudentNames VARCHAR(20), Id INT, AggregateMarks INT, TotalMarks INT)
INSERT INTO @T VALUES
('NAME1',1,300,503),
('NAME2',2,250,500),
('NAME3',3,378,504),
('NAME4',4,490,500),
('NAME5',5,399,500)

SELECT X.ID,X.TotalMarks,t1.[AggregateMarks],X.[NAME1],X.[NAME2],X.[NAME3],X.[NAME4],X.[NAME5]
FROM @T
PIVOT
(
MAX(AggregateMarks)
FOR StudentNames
IN ([NAME1],[NAME2],[NAME3],[NAME4],[NAME5])
) AS X
JOIN @T t1 on t1.Id = X.Id
ORDER BY 1

I have done this one to show you how it must go like. The dynamic version follows

CREATE TABLE #T (StudentNames VARCHAR(20), Id INT, AggregateMarks INT, TotalMarks INT)
INSERT INTO #T VALUES
('NAME1',1,300,503),
('NAME2',2,250,500),
('NAME3',3,378,504),
('NAME4',4,490,500),
('NAME5',5,399,500)

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(StudentNames)
from #T
group by StudentNames, id
order by id
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')

set @query = N'SELECT X.ID,X.TotalMarks,t1.AggregateMarks, ' + @cols + N' from #T
PIVOT
(
MAX(AggregateMarks)
FOR StudentNames IN (' + @cols + N')
) AS X
JOIN #T t1 on t1.Id = X.Id
ORDER BY 1'

print @query

exec sp_executesql @query

DROP TABLE #T

Hope this helps.

Try this

DECLARE @T TABLE(ID_NO INT IDENTITY,FIRST_NAME VARCHAR(50), LAST_NAME VARCHAR(50))
INSERT INTO @T VALUES('X','ABC'),('Y','ABC'),('M','PQR'),('N','PQR')
--SELECT * FROM @T
;WITH CTE AS(
SELECT
Rn = ROW_NUMBER() OVER(PARTITION BY t2.LAST_NAME ORDER BY (SELECT 1))
,t2.LAST_NAME
+ ',' +
STUFF(( SELECT '-' + t1.FIRST_NAME
FROM @T t1
WHERE t1.LAST_NAME = t2.LAST_NAME
FOR XML PATH(''),TYPE) .value('.','NVARCHAR(MAX)'),1,1,'')AS Concat_FirstName
FROM @T t2
)
SELECT
STUFF(( SELECT '/' + c1.Concat_FirstName
FROM CTE c1
WHERE c1.Rn = c2.Rn
FOR XML PATH(''),TYPE)
.value('.','NVARCHAR(MAX)'),1,1,'') AS Concat_FirstName
FROM CTE c2 WHERE Rn= 1
GROUP BY c2.Rn

Result Concat_FirstName ABC,X-Y/PQR,M-N Hope this helps