Print "Happy Birthday"

CREATE TABLE #tmp (TmpID int identity (1,1) primary key clustered, EnglishAlpha varchar(1000))
Create Table #DecodeMe (DecodeID int identity(1,1), DecodeMe varchar(256))
;
With Alpha as (SELECT Distinct Top 26 EnglishAlpha = CHAR(ABS(CHECKSUM(NEWID()))%26+65)
From Master.sys.SysColumns t1, Master.sys.SysColumns t2
)

INSERT INTO #tmp (EnglishAlpha)
SELECT EnglishAlpha
From Alpha
Order By EnglishAlpha

Insert Into #tmp(EnglishAlpha)
Select ' '

Insert Into #DecodeMe (DecodeMe)
Select '8.1.16.16.25.27.2.9.18.20.8.4.1.25'
;
WITH extract (id, lft, rght, idx)
AS (
SELECT t.Decodeid
,LEFT(t.DecodeMe, CHARINDEX('.', t.DecodeMe) - 1)
,SUBSTRING(t.DecodeMe, CHARINDEX('.', t.DecodeMe) + 1, DATALENGTH(t.DecodeMe))
,0
FROM #DecodeMe t
UNION ALL
SELECT c.id
,CASE WHEN CHARINDEX('.', c.rght) = 0 THEN c.rght ELSE LEFT(c.rght, CHARINDEX('.', c.rght) - 1) END
,CASE WHEN CHARINDEX('.', c.rght) > 0 THEN SUBSTRING(c.rght, CHARINDEX('.', c.rght) + 1, DATALENGTH(c.rght))
ELSE '' END
,idx + 1
FROM extract c
WHERE DATALENGTH(c.rght) > 0
), genstring as (
select cast(A.EnglishAlpha as Varchar(256)) as EnglishAlpha,t.idx,t.lft,t.id
from #tmp a
Inner Join extract t
On t.lft = a.TmpID
Where t.idx = 0
Union All
Select cast(T.EnglishAlpha + a.englishalpha as varchar(256)) as EnglishAlpha,t.idx + 1,t.lft,t.id
From genstring t
Inner Join extract t2
On t.id = t2.id
And t.idx + 1 = t2.idx
Inner Join #tmp a
on t2.lft = a.tmpid
)
Select id,max(EnglishAlpha) as TextOut from genstring
Group By id
Order By id
Drop Table #tmp
Drop Table #DecodeMe

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