SELECT TOP N and BOTTOM N Rows Using SQL Server

With CTETempas(Select
ContractNumber ,ROW_NUMBER() OVER (Order BY ContractNumber) as TopFive ,ROW_NUMBER() OVER (Order BY ContractNumber Desc) as BottomFiveFROM Contracts)Select ContractNumber From CTETemp Where TopFive <=5 or BottomFive <=5ORDERBY TopFive asc

actual table:

ContractNumber	CustAddId	RenewalDate	Renew	ContractValue	DaysUntilRen
1	1	12/8/2008 0:00	1	300	-994
2	2	1/15/2009 0:00	0	280	-956
3	3	1/14/2009 0:00	0	280	-957
4	4	1/21/2009 0:00	1	300	-950
5	5	NULL	NULL	49.99	NULL
6	6	1/16/2009 0:00	1	280	-955
7	7	9/15/2008 0:00	1	260	-1078
8	8	1/3/2009 0:00	0	280	-968
9	9	2/2/2009 0:00	1	300	-938
10	10	1/16/2009 0:00	1	280	-955
11	11	12/18/2008 0:00	NULL	280	-984
12	12	3/9/2009 0:00	1	280	-903
13	13	5/9/2009 0:00	0	300	-842
14	14	6/21/2009 0:00	0	280	-799
15	15	9/11/2008 0:00	1	260	-1082
16	16	3/16/2009 0:00	1	260	-896
17	17	7/27/2008 0:00	0	280	-1128
18	18	1/2/2009 0:00	1	280	-969
19	19	2/2/2009 0:00	1	300	-938
20	20	1/31/2009 0:00	1	280	-940
21	21	2/4/2009 0:00	1	280	-936

The UNPIVOT operator performs the reverse operation of PIVOT, by rotating columns into rows.

The UNPIVOT operator performs the reverse operation of PIVOT, by rotating columns into rows.

Sample Denormalized Table
CREATE TABLE #Student
(
StudentID int ,
Marks1 float,
Marks2 float,
Marks3 float
)
INSERT INTO #Student VALUES (1, 5.6, 7.3, 4.2)
INSERT INTO #Student VALUES (2, 4.8, 7.9, 6.5)
INSERT INTO #Student VALUES (3, 6.8, 6.6, 8.9)
INSERT INTO #Student VALUES (4, 8.2, 9.3, 9.1)
INSERT INTO #Student VALUES (5, 6.2, 5.4, 4.4)
SELECT * FROM #Student
SELECT StudentID, MarksNo, MarksRecd
FROM
(SELECT StudentID,
Marks1, Marks2, Marks3
FROM #Student) stu
UNPIVOT
(MarksRecd FOR MarksNo IN (Marks1, Marks2, Marks3)
) AS mrks

SQL Server: Export Table to CSV

bcp yourdb..test out D:\test.csv -T -f D:\format.fmt

6 Common Uses of the undocumented Stored Procedure sp_MSforeachdb

Print all the database names in a SQL Server Instance

EXEC sp_MSforeachdb 'USE ?; PRINT DB_NAME()'

Print all the tables in all the databases of a SQL Server Instance

EXEC sp_MSforeachdb 'USE ? SELECT DB_NAME() + ''.'' + OBJECT_NAME(object_Id) FROM sys.tables'

EXEC sp_MSforeachdb 'USE ? SELECT OBJECT_NAME(object_Id) FROM sys.tables where DB_NAME() NOT IN(''master'', ''model'', ''msdb'', ''tempdb'')'

Display the size of all databases in a SQL Server instance

EXEC sp_MSforeachdb 'USE ?; EXEC sp_spaceused'

Determine all the physical names and attributes(size,growth,usage) of all databases in a SQL Server instance

EXEC sp_MSforeachdb 'USE ? EXEC sp_helpfile;'

Change Owner of all databases to 'sa'
EXEC sp_MSforeachdb 'USE ?; EXEC sp_changedbowner ''sa'''

Check the Logical and Physical integrity of all objects in the database

sp_MSforeachdb 'DBCC CHECKDB(?)'

 

TSQL Beginners Challenge 4 - Concatenating values from multiple rows

http://beyondrelational.com/blogs/tcb/archive/2010/03/28/TSQL-Beginners-Challenge-4-Concatenating-values-from-multiple-rows.aspx

 Solution :

 

SELECT id, Stuff(name, ( Len(name) - 3 ), Len(name), '') FROM (SELECT DISTINCT id,
(SELECT a.name + ' ' + b.chk + ' ' FROM tc4 a WHERE a.id = b.id FOR XML PATH('')) AS name FROM (SELECT *, CASE WHEN r%2 = 0 THEN ' AND ' WHEN r%3 = 0 THEN ' OR ' ELSE '' END AS chk FROM (SELECT *,COUNT(id) OVER(PARTITION BY id ) AS r FROM tc4) df) b)bb

Using OVER() with Aggregate Functions

One of new features in SQL 2005 that I haven't seen much talk about is that you can now add aggregate functions to any SELECT (even without a GROUP BY clause) by specifying an OVER() partition for each function. Unfortunately, it isn't especially powerful, and you can't do running totals with it, but it does help you make your code a little shorter and in many cases it might be just what you need.
The way it works is similar to joining an aggregated copy of a SELECT to itself. For example, consider the following:

You can now easily return the total CNT per Area as an additional column in this SELECT, simply by adding an aggregate SUM() function with an OVER() clause:

for Example

select

Area, nvcQuestionName, [CNT], nvcResultDesc,Sum(CNT) OVER (Partition by Area) as Total from sample1

ORDER BY Clause and TOP WITH TIES

WITH TIES can be used only with TOP and ORDER BY, both the clauses are required. Let us understand from one simple example how this clause actually works. Suppose we have 100 rows in the table and out of that 50 rows have same value in column which is used in ORDER BY; when you use TOP 10 rows, it will return you only 10 rows, but if you use TOP 10 WITH TIES, it will return you all the rows that have same value as that of the last record of top 10 — which means a total of 50 records

For Example: select top 10 with ties * from tablename order by 1