SQL syntax explanation and examples of MSSql row-and-column Conversion

1. Column and column Conversion

The data structure of the original table (Here It Can Be A view V_AnswerList), as shown in:

Write the stored procedure:

Create procedure [dbo]. [pro_Sum_1]
AS
BEGIN
DECLARE @ SQL varchar (8000)
SET @ SQL = 'select d_Name as unit, s_Name as staff'
SELECT @ SQL = @ SQL + ', (case q_No when ''' + q_No + ''' then a_Answer else ''' + ''+ ''' end) ['+ q_No +']'
FROM (select distinct q_No
FROM V_AnswerList) AS V_AnswerList
SELECT @ SQL = @ SQL + 'from V_AnswerList' EXEC (@ SQL)
END

 

After implementation:

 

This is because a_Answer is of the string type. I don't laugh at using the aggregate function to connect it. If a_Answer is of the int type, in the stored procedure, you can Sum (case q_No when...) like this ....).

Another stored procedure with parameters:

For reference:

Create procedure [dbo]. [pro_Sum_2]
(
@ S_No varchar (20)
)
AS
BEGIN

DECLARE @ s_No_1 varchar (20 );
DECLARE @ SQL varchar (8000 );

Set
@ S_No_1 = @ s_No;
SET
@ SQL = 'select d_Name as unit, s_Name as staff'
SELECT @ SQL = @ SQL + ', (case q_No when ''' + q_No + ''' then a_Answer else ''' + ''+ ''' end) ['+ q_No +']'
FROM (select distinct q_No
FROM V_AnswerList) AS V_AnswerList
SELECT @ SQL = @ SQL + 'from V_AnswerList where s_No =' + @ s_No_1
EXEC (@ SQL)

END

 

1. -- another example of row and column Conversion
Original table: name, subject score
Zhang San Chinese 80
Zhang San, mathematics 90
Zhang San physical 85
Li Si Chinese 85
Li Si physical 82
Li Si English 90
Li Si politics 70
Wang Wu English 90

Converted table: name, mathematics, physics, English, Chinese, and Politics
Li Si 0 82 90 85 70
Wang Wu 0 0 90 0 0
Zhang San 90 85 0 80 0

Instance:
Create table cj -- create table cj
(
ID Int IDENTITY () not null, -- creates a column ID and Adds 1 to each new record.
Name Varchar (50 ),
Subject Varchar (50 ),
Result Int,
Primary key (ID) -- Define ID as the primary key of table cj
);
-- Truncate table cj
-- Select * from cj
Insert into cj
Select 'zhang san', 'China', 80 union all
Select 'zhang san', 'mat', 90 union all
Select 'zhang san', 'Physical ', 85 union all
Select 'Li si', 'China', 85 union all
Select 'lily', 'Physical ', 82 union all
Select 'Li si', 'English ', 90 union all
Select 'Li si', 'political ', 70 union all
Select 'wang 5', 'English ', 90
-- Column and column Conversion
Declare @ SQL varchar (8000)
Set @ SQL = 'select Name as name'
Select @ SQL = @ SQL + ', sum (case Subject when ''' + Subject + ''' then Result else 0 end) [' + Subject + ']'
From (select distinct Subject from cj) as cj -- lists the names of all unique subjects.
Select @ SQL = @ SQL + 'from cj group by name'
Exec (@ SQL)

2. Column and column conversion-Merge
Original table: class student ID
1 1
1 2
1 3
2 1
2 2
3 1
Converted table: class student ID
1 1, 2, 3
2 1, 2
3 1

Instance:
Create table ClassNo -- Create table ClassNo
(
ID Int IDENTITY () not null, -- creates a column ID and Adds 1 to each new record.
Class Varchar (50), -- Class column
Number Varchar (50), -- Student ID column
Primary Key (ID) -- Define ID as the Primary Key of table ClassNo
);
-- Truncate Table ClassNo
-- Select * from ClassNo
Insert Into ClassNo
Select 1, 1, Union all
Select 1, 2 Union all
Select 1, 3 Union all
Select 2, 1 Union all
Select 2, 2 Union all
Select 3, 1

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 (50) + ',' from ClassNo Where Class = @ Class
Set @ str = SubString (@ str, 1, len (@ str)-1)
Return (@ str)
End

-- Call the custom function to obtain the result.
Select Distinct Class, dbo. KFReturn (Class) From ClassNo

3: column-to-row
-- Drop Table ColumnToRow
Create table ColumnToRow
(
ID Int IDENTITY () not null, -- creates a column ID and Adds 1 to each new record.
A int,
B int,
C int,
D int,
E int,
F int,
G int,
H int,
Primary Key (ID) -- Define ID as the Primary Key of the table ColumnToRow
);
-- Truncate Table ColumnToRow
-- Select * from ColumnToRow
Insert Into ColumnToRow
Select, Union all
Select, 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 70 is the string 'from ColumnToRow union all Select ID from ColumnToRow union all select', because it will calculate the value of the ID column, so cut it off
Exec ('select' + @ SQL + 'from columntorow ')

4. How to obtain all column names of a data table
The method is as follows: first obtain the systemid of the data table from the sysobjects system table, and then retrieve all the column names of the data table from the syscolumns table.
The SQL statement is as follows:
Declare @ objid int, @ objname char (40)
Set @ objname = 'columntorow'
-- 1st Methods
Select @ objid = id from sysobjects where id = object_id (@ objname)
Select 'column _ name' = name from syscolumns where id = @ objid order by colid
-- Or can be written
Select name as 'column _ name' from syscolumns where id = @ objid order by colid
-- 2nd methods:
Select name as 'column _ name' from SysColumns where id = object_id (@ objname) Order by colid

 

5. Use SQL statements to change the User Password
Sysadmin role is required to modify other users.
Exec Sp_password 'original password', 'changed password', 'account'
Exec sp_password null, OK, sa

 

6. How can I determine which fields in a table cannot be blank?
Declare @ objname Varchar (50)
Set @ objname = 'columntorow'
Select Column_Name from information_schema.Columns where is_nullable = 'no' and Table_Name = @ objname

 

7. How can I find a table with the same fields in the database?
A. query the names of known Columns
Select a. name as Columnname, B. name as tablename from SysColumns a inner join sysobjects B on a. id = B. id
And B. type = 'U' and a. name = 'field name you are looking'
B. query all the names of unknown columns 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. query row N data
Assume that id is the 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. the first day of a month
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 a year
Select dateadd (yy, DATEDIFF (yy, 0, getdate (), 0)
D. The 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. The first Monday of this 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 ))