How to select all columns except one column from a table ?

DECLARE @ColList Varchar(1000), @SQLStatment VARCHAR(4000)
SET @ColList = ''
select @ColList = @ColList + Name + ' , ' from syscolumns where id = object_id('Table1') AND Name != 'Column20'
SELECT @SQLStatment = 'SELECT ' + Substring(@ColList,1,len(@ColList)-1) + ' From Table1'
EXEC(@SQLStatment)

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005, I have found that XML PATH method can handle

If there is a table called STUDENTS

SubjectID StudentName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward

Result I expected was:

SubjectID StudentName
---------- -------------
1 Mary, John, Sam
2 Alaina, Edward

I used the following T-SQL:

Select Main.SubjectID,
Left(Main.Students,Len(Main.Students)-1) As "Students"
From(Select distinct ST2.SubjectID,
(Select ST1.StudentName + ',' AS [text()]
From dbo.Students ST1
Where ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
For XML PATH ('')) [Students]
From dbo.Students ST2) [Main]

To concatinate different rows in a single string

DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name FROM People
SELECT @Names

To change column name of a table

SP_RENAME 'TableName.OldColumnName', 'NewColumnName', 'COLUMN'

eg.
SP_RENAME 'student.marks', 'total_marks', 'COLUMN'

Make query for inserting datetime field in excel

="INSERT INTO temp(out_time, in_time) values('"&TEXT(A3,"hh:mm")&"','"&TEXT(B3,"hh:mm")&"')"

or

="INSERT INTO student
(name, dob, reference, s_no, branch) values ('"&C2&"','"&TEXT(I2,"dd-mmm-yyyy")& "','"&E2&"',"&A2&",'"&F2&"')"

This method is to get scope identity from a table where both columns are auto increment

Create Table Testing (  
   id int identity,
   somedate datetime default getdate()
)
insert into Testing  
output inserted.*
default values

or

INSERT INTO Testing  SELECT NULL
select max(id) from Testing

Dynamic PIVOT in SQL Server 2005

The PIVOT operator available in SQL Server 2005 is used to generate the Cross-tab results
Consider this example

select * from
(
select Year(OrderDate) as pivot_col,e.lastname, o.OrderDate FROM northwind..Employees as e
INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID)
) as t
pivot
(
Count(OrderDate) for pivot_col in ([1996],[1997])
) as p

which shows total orders of each employees for years 1996 and 1997

What if we want to have this for all the years available in the table
You need to use dynamic sql


--This procedure is used to generate Dynamic Pivot results
The approach is very similar to my Dynamic Crosstab with multiple PIVOT Columns blog post
which can be used in SQL Server 2000
create procedure dynamic_pivot
(
@select varchar(2000),
@PivotCol varchar(100),
@Summaries varchar(100)
) as

declare @pivot varchar(max), @sql varchar(max)
select @select =replace(@select,'select ','select '+@PivotCol+' as pivot_col,')


create table #pivot_columns (pivot_column varchar(100))

Select @sql='select distinct pivot_col from ('+@select+') as t'

insert into #pivot_columns
exec(@sql)

select @pivot=coalesce(@pivot+',','')+'['+pivot_column+']'from #pivot_columns

select @sql=
'
select * from
(
'+@select+'
) as t
pivot
(
'+@Summaries+' for pivot_col in ('+@pivot+')
) as p
'
exec(@sql)


Purpose : Find total sales made by each employee for each year(from Employees and Orders table from Northwind databases)
Usage :

EXEC dynamic_pivot
'SELECT e.lastname, o.OrderDate FROM northwind..Employees as e
INNER JOIN northwind..Orders as o ON (e.EmployeeID=o.EmployeeID) ',
'Year(OrderDate)',
'Count(OrderDate)'

Purpose : Find total sales made by each company for each product(from products, order details and suppliers table from Northwind database)
Usage :

EXEC dynamic_pivot
'SELECT s.companyname,coalesce(od.unitprice*od.quantity ,0) as total_cost FROM northwind..products as p
inner join northwind..[order details] as od on p.productid=od.productid
inner join northwind..suppliers as s on p.supplierid=s.supplierid',
'productname',
'sum(total_cost)'