"Go" SQL row and column conversions

Original address: http://blog.csdn.net/kiki113/article/details/4105929 column conversion and other classic SQL statements

1.--Row and column conversions

Original table: Name account Score
Zhang San Language 80
Zhang San Mathematics 90
Zhang San Physics 85
John Doe Language 85
John Doe Physics 82
John Doe English 90
John Doe Politics 70
Harry English 90

Converted tables: Names mathematical physics English language politics
Lee 40 82 90 85 70
King 50 0 90 0 0
Sheet 390 85 0 80 0

Instance:
CREATE TABLE CJ--Creating Tables CJ
(
ID Int IDENTITY (first) NOT null--creates the column ID and adds 1 each time a new record is added
Name Varchar (50),
Subject Varchar (50),
Result Int,
Primary key (ID)--Define the primary key for the ID table CJ
);
--truncate Table CJ
--select * from CJ
Insert into CJ
Select ' Zhang San ', ' language ', UNION ALL
Select ' Zhang San ', ' Math ', UNION ALL
Select ' Zhang San ', ' physics ', ' UNION ALL '
Select ' John Doe ', ' language ', ' union ALL '
Select ' John Doe ', ' physics ', all
Select ' John Doe ', ' English ', UNION ALL
Select ' John Doe ', ' politics ', union ALL
Select ' Harry ', ' English ', 90
--row and column conversions
Declare @sql varchar (8000)
Set @sql = ' Select name as name '
Select @sql = @sql + ', sum (case Subject "+subject+" then Result else 0 end) [' +subject+ ']
From (select distinct Subject from CJ) as CJ-List all the unique subject names
Select @sql = @sql + ' from CJ Group by name '
Exec (@sql)

2. Row and column conversions-merge
Original table: Class School Number
1 1
1 2
1 3
2 1
2 2
3 1
Converted table: Class number
1
2
3 1

Instance:
CREATE TABLE Classno--Creating Tables Classno
(
ID Int IDENTITY (first) NOT null--creates the column ID and adds 1 each time a new record is added
Class Varchar (50),--Class column
Number Varchar (50),--Learning
Primary key (ID)--Define the primary key with ID as table Classno
);
--truncate Table Classno
--select * from Classno
Insert into Classno
Select the Union All
Select Union All
Select 1,3 Union All
Select 2,1 Union All
Select 2,2 Union All
Select 3,1

To create a merged function
--drop Function Kfreturn
Create Function Kfreturn (@Class Varchar (50))
Returns Varchar (8000)
As
Begin
Declare @str Varchar (8000)
Set @str = ' '
Select @str = @str + cast (number as Varchar) + ', ' from classno Where Class = @Class
Set @str = SubString (@str, 1,len (@str)-1)
Return (@str)
End

--Call the custom function to get the result
Select Distinct class,dbo. Kfreturn (Class) from Classno

3: Column career
--drop table Columntorow
Create table Columntorow
(
   ID Int IDENTITY (+)   Not null, --Creates a column ID, and adds 1
   a  int,
   b  int,
   C, each time a new record is added   int,
   d  int,
   e  int,
   f  int,
   g& nbsp int,
   h  int,
   Primary Key (ID)        -- Define the primary key        ID for table columntorow;
);
--truncate Table columntorow 
--select * from Columntorow
Insert into columntorow 
Select 15,9,1,0,1,2,4,2 UNION ALL
Select 22,34,44,5,6,7,8,7 UNION ALL
Select 33,44,55,66,77,88,99,12

Declare @sql Varchar (8000)
Set @sql = ' '
Select @sql = @sql + rtrim (name) + ' from Columntorow UNION All Select ' from syscolumns Where id = object_id (' Columntorow ‘)
Set @sql = SubString (@sql, 1,len (@sql)-70)
The length of the--70 is the string ' from Columntorow the union ALL select ID from the Columntorow union ALL select ' because it takes the value of this column of ID as well, so we're going to cut it off.
Exec (' Select ' + @sql + ' from Columntorow ')

4. How to obtain all column names for a data table
The method is as follows: Get the systemid of the data table from the sysobjects system table first, and then get all the column names of the data table in the Syscolumns table.
The SQL statements are as follows:
Declare @objid int, @objname char (40)
Set @objname = ' Columntorow '
--1th method
Select @objid = ID from sysobjects where id = object_id (@objname)
Select ' column_name ' = name from syscolumns where id = @objid ORDER by colid
-or it can be written
Select name as ' column_name ' from syscolumns where id = @objid ORDER by colid
--The 2nd method:
Select name as ' column_name ' from syscolumns where id = object_id (@objname) Order by colid

5. Change the user's password by using an SQL statement
Modify others, need sysadmin role
Exec sp_password ' original password ', ' changed password ', ' account '
Exec sp_password Null,ok,sa

6. How can I tell which fields in a table are not allowed to be empty?
Declare @objname Varchar (50)
Set @objname = ' Columntorow '
Select column_name from INFORMATION_SCHEMA. Columns where is_nullable = ' No ' and table_name = @objname

7. How do I find a table with the same fields in my database?
A. Check for known column names
Select A.name as columnname,b.name as TableName from syscolumns a inner joins sysobjects b on a.id = b.ID
and B.type = ' U ' and a.name = ' field name you are looking for '
B. Unknown column name All column names that appear in different tables
Select S.name as tablename,s1.name as ColumnName from syscolumns s1,sysobjects s
where s1.id = s.id and S.type = ' U ' and Exists (Select 1 from syscolumns s2 where s1.name = S2.name and s1.id <> S2. Id

8. Querying the nth row of data
Suppose the ID is a primary key:
SELECT *
From (select Top N * from table) AA
Where NOT EXISTS (select 1 from (select Top N-1 * from table) BB where aa.id=bb.id)

9. SQL Server Date Calculation
A. First day of one months
SELECT DATEADD (mm, DATEDIFF (Mm,0,getdate ()), 0)
B. Monday of the Week
SELECT DATEADD (wk, DATEDIFF (Wk,0,getdate ()), 0)
C. The first day of the year
SELECT DATEADD (yy, DATEDIFF (Yy,0,getdate ()), 0)
D. First day of the quarter
SELECT DATEADD (QQ, DATEDIFF (Qq,0,getdate ()), 0)
E. Last day of last month
SELECT DateAdd (Ms,-3,dateadd (mm, DATEDIFF (Mm,0,getdate ()), 0))
F. Last day of last year
SELECT DateAdd (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()), 0))
G. Last day of the month
SELECT DateAdd (Ms,-3,dateadd (mm, DATEDIFF (M,0,getdate ()) +1, 0))
H. First Monday of the month
Select DATEADD (wk, DATEDIFF (Wk,0,dateadd (Dd,6-datepart (Day,getdate ()), GETDATE ()), 0)
I. The last day of the year
SELECT DateAdd (Ms,-3,dateadd (yy, DATEDIFF (Yy,0,getdate ()) +1, 0))

"Go" SQL row and column conversions