Database

total records belonging to each division

;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)

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

Dim orders = _
(
    From oc In context.Order_Completed
    Group Join srs In context.Shipping_Realtime On oc.RateAPICode Equals srs.RateAPICode Into rRates = Group From srs In rRates.DefaultIfEmpty()
    Group Join fsm In Context.Shipping_Free_Matrix On oc.RateAPICode Equals fsm.RateAPICode Into fRates = Group From fsm In fRates.DefaultIfEmpty()
    Where oc.OrderStatus = "QUEUED_FOR_FULFILLMENT" _
    Or oc.OrderStatus = "OUT_FOR_FULFILLMENT" _
    Or oc.OrderStatus = "ORDER_CANCELED" _
    And oc.OrderDate >= DbFunctions.AddDays(Today, -7)
    Order By oc.OrderNumber
    Select New With
    {
        .OrderID = oc.CompletedOrderID,
        .OrderDate = oc.OrderDate,
        .OrderNumber = oc.OrderNumber,
        .OrderStatus = oc.OrderStatus,
        .LoginID = oc.LoginID,
        .GrandTotal = oc.GrandTotal,
        .RateAPICode = oc.RateAPICode,
        .RateCharge = oc.TotalNetCharge,
        .RateAPIName = If(Not srs.RateName is Nothing, srs.RateName, fsm.RateName) 
    }
).AsEnumerable()

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

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

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

SELECT [Cartilage Regeneration],[Foot and Ankle Care],[Hand, Wrist and Elbow Care],[Hip Care],[Joint Replacement],[Knee Care],
[Orthopedic Trauma Surgery],[Pediatric Orthopedics],[Shoulder Care],[Sports Medicine],[Tendon and Ligament Repair] from 
             (
                select  ServiceLevel, ServiceId, Name
                from [Service]             
            ) x 
            pivot 
            (
                max(ServiceId)
                for Name in ([Cartilage Regeneration],[Foot and Ankle Care],[Hand, Wrist and Elbow Care],[Hip Care],[Joint Replacement],
                [Knee Care],[Orthopedic Trauma Surgery],[Pediatric Orthopedics],[Shoulder Care],[Sports Medicine],[Tendon and Ligament Repair])
            )
             p 

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

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