Transcript Programming Microsoft SQL Server

Programming Microsoft SQL
Server
David Henson
[email protected]
www.certifiednetworks.com
Logistics
•
•
•
•
Hours: 9:00am to 5:00pm
Lunch
Phones
Parking
Course Material
• Handouts of SQL scripts
• “SQL Server 2008 for Developers”,
Murach press
Recommended Reading
• “Professional SQL Server 2000
Programming”, Wrox Press
•One of the best technical resources:
“Inside SQL Server 2000”,
Microsoft Press
Course Outline
•
•
•
•
•
•
•
Chapter 1: Configuration/Tools
Chapter 2: T-SQL Language Elements
Chapter 3: Foundation T-SQL Statements
Chapter 4: Joins
Chapter 5: Subqueries and Summary Queries
Chapter 6: Changing Data
Chapter 7: Scripts, Batches and TSQL
Extensions
Course Outline
•
•
•
•
•
•
•
Chapter 8: Views
Chapter 9: Stored Procedures
Chapter 10: User Defined Functions
Chapter 11: Triggers
Chapter 12: Cursors
Chapter 13: Transactions and Locks
Chapter 14: CLR Integration
Chapter 1:
Configuration/Tools
Machine Setup
• Windows 2003, Standard Installation
• SQL Server 2008, Enterprise Edition
– Trial at www.microsoft.com/sql
• SQLClass Database
– Will be built during class
– \\instructor\public
Tools - Overview
•
•
•
•
•
SQL Management Studio
Query Editor Window
SQLCMD.exe
Books Online
Misc. Tools:
– Visual Studio .Net 2008
Older Tools:
• Query Analyzer (isqlw.exe)
Microsoft SQL Server Management Studio
Query Editor Window
SQLCMD
•C:\SQLCMD –Sserver -E
•Osql /? For help, or see books
online
Books Online
• Best help possible for syntax
• www.google.com best help possible for
strangeness
Miscellaneous Tools
• Profiler
• Server Config Tool
• Surface Area Configuration Tool
– 2005
• Client Config Tool
• Visual Studio 2005/ 2008
Chapter 2: T-SQL Language
Elements
Statement Types
• DDL – Data Definition Language
– CREATE TABLE Orders(….)
• DCL
– GRANT SELECT ON Orders To Public
• DML
–
–
–
–
SELECT Max(OrderDate) From Orders
INSERT Orders VALUES(….)
DELETE Orders WHERE OrderID = 10
UPDATE Orders
SET OrderAmount = 0 WHERE OrderID = 10
T-SQL Elements
•
•
•
•
•
•
•
•
Comments
Identifiers
Variables
Datatypes
System Functions
Operators
Expressions
Control-of-flow
Comments
• Block Comment
/*Multi-line comments here*/
• Double Dash
SELECT * FROM Orders -- This is a comment
• Edit/Advanced/Comment Out
Identifiers
• Objects are identified internally by number
• Object Interaction is by name
• Standard Identifiers
– Orders
– Customers
• Delimited Identifiers
– [Spaces are evil]
– [1Person]
Variables
• Must Be Declared
• Must Start with @ symbol
DECLARE @SaleDate datetime
SELECT @SaleDate = getdate()
PRINT @SaleDate
Datatypes
• Numbers
Bigint - +/- 2 to the 63rd power 8 bytes
Int - +/- 2,147,483,648
4 bytes
Smallint - 32,768 to 32,767
2 bytes
Tinyint 0 to 255
1 byte
Bit 0,1 or null
2 bytes
Decimal(precision,scale)
Default max precision 38 decimals
Datatypes
• Money, Smallmoney
• Datetime, Smalldatetime
– Getdate()
•
•
•
•
•
Char, Varchar
Nchar, nvarchar
Text
Timestamp/rowversion
Uniqueidentifier
– Newid()
System Functions
• Getdate()
• Cast()
• Convert()
• “Global Variables”
– SELECT @@version
Operators
•
•
•
•
+-*/%
= > < >= <= <>
Concatination +
AND, OR, NOT
Expressions
• Symbols and operators that evaluate to a
single value
• Must be used in the context of another
query
Control of flow
• Somewhat limited compared to other
languages
– WHILE
– IF ELSE
– BEGIN END block
– CASE
– WAITFOR
– CONTINUE/BREAK
Lab 2A: T-SQL Language
Elements
• In this instructor led lab, students will
practice using various T-SQL elements
Chapter 3: Foundation T-SQL
Statements
ANSI SQL
• History
• Purpose
• Four Main DML Statements:
– SELECT
– INSERT
– UPDATE
– DELETE
SELECT
• Retrieve/Filter Rows
SELECT <selectlist>
FROM <table1, n…>
WHERE <conditional expression>
ORDER BY <fieldlist>
• Over 30 pages of online help!
INSERT
• Adds new rows to tables
INSERT Customers VALUES(
‘Henson’,
‘Dave’,
‘[email protected]’
)
Update
• Modifies existing records
Delete
• Wipes out rows forever
• Truncate Table Customers
Temp tables
• Preceed object name with a # during
creation
• New in SQL 2000 – table variables
Lab 3A: Foundation T-SQL
• In this instructor led lab, we will establish
business requirements and build our class
database from the ground up.
Chapter 4: Joins
Data Environments
• OLTP
– Structured/Optimized for transactions
– Data is organized in many tables
• OLAP
– Structured/Optimized for reporting
– Large space requirements
– Redundant Data
Putting Things Back Together
Again
• Joins pull together data from two or more
tables
• Basic Syntax
SELECT *
FROM Orders o
INNER JOIN OrderDetails od
ON o.OrderID = od.OrderID
Join Types
•
•
•
•
•
INNER JOIN
LEFT OUTER JOIN
RIGHT OUTER JOIN
FULL JOIN
CROSS JOIN
Inner Join
• Exclusive nature of inner join
• Only rows that match in both input tables
are returned
Outer Join
•
•
All rows from “left” or “right are returned
Any matching rows from the other side
are returned
•
Business questions:
1. “Show me all customers, and their
purchases for the month”
2. “Show me all products, and their profit last
week”
Full Join
• All data from both inputs are returned
• Null is returned in cells where there is no
intersection
Cross Join
• Cartesian product of two inputs
• Produces all intersections
• Can produce mass amounts of
data…good for populating databases
Joining multiple tables
• Every join is between two “input” tables
• One table could be an “intermediate result”
of another join
• Many errors(and hours of frustration)
come from improper mixing of inner and
outer joins
Self Joins
• Join a table to itself for recursive lookups
• Must use table aliases
• Example:
– Employees table contains ManagerID field,
which must be a valid EmployeeID
Chapter 5:Subqueries and
Summary Queries
Grouping Summarizing
• GROUP BY clause is often used for
reporting, in conjunction with joins
• Basic Structure:
SELECT <item>, <aggregate>
FROM <tablelist>
<join sets>
GROUP BY <item>
Having Clause
• Having is the where clause applied to the
aggregated data
SELECT ProductID, Sum(SaleAmount)
FROM Sales
GROUP BY ProductID
HAVING Sum(SaleAmount) > 1000
Aggregate functions
•
•
•
•
•
•
•
•
Count(), Count(*)
Sum()
Min()
Max()
Avg()
Ceiling()
Floor()
Other statistical aggregates…
Unions
• Joins two complete result sets together
• Field number and types must match
• Good for adding summary lines to reports
and pulling data together from multiple
servers
Union Example
SELECT
Convert(varchar(20),saleDate,101),
qty * price as 'Amount'
FROM uniondemo
UNION all
SELECT
'Grand Total' as 'GrandTotal',
sum(qty * price)
FROM uniondemo
Lab 5A:
Joins/Grouping/Summarizing
• In this lab, students will use joins and
grouping/summarizing to create business
reports
Chapter 6: Creating/Altering
Objects
Object Names
• Four part name must be unique:
– Server.Database.Owner.Object
• Short vs. Long
• Abbreviations
• Using Natural Language Queries
Granting Access to Objects
•
Three layers of SQL Security:
1)
Login
–
2)
User
–
3)
Windows or Standard
Database specific membership, tied to the login
Object Permissions
–
Permissions and permission type specific to the object
•
•
•
Sp_addlogin
Sp_adduser
GRANT statement
•
New in SQL 2005
–
–
CREATE LOGIN statement
CREATE USER statement
CREATE Database
CREATE DATABASE [SQLClass]
ON (
NAME = 'SQLClass',
FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\data\SQLClass.mdf' ,
SIZE = 1,
FILEGROWTH = 10%)
LOG ON (
NAME = 'SQLClass_log',
FILENAME = 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\data\SQLClass_log.LDF' ,
FILEGROWTH = 10%)
COLLATE SQL_Latin1_General_CP1_CI_AS
Setting Database Options
exec sp_dboption
'SQLClass', 'read only', 'false'
Create Table
CREATE TABLE [dbo].[Customers] (
[CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
[CustomerEmail] [varchar] (30) NULL ,
[CustomerState] [varchar] (5) NOT NULL
) ON [PRIMARY]
GO
Creating Other Objects
•
•
•
•
View
Stored Procedure
User Defined Function(UDF)
Datatype
Alter Database
• Add/remove data and log files
• Alter properties of files
ALTER DATABASE Test1
MODIFY FILE (
NAME = test1dat3,
SIZE = 20MB
)
• Alter filenames and locations
Alter Table
• Adding fields – last position only
• Adding a primary key
Managing Database Size
• Microsoft SQL Server Management Studio –
recommended method
• Scripts
DBCC SHRINKDATABASE
( database_name [ , target_percent ]
[ , { NOTRUNCATE | TRUNCATEONLY } ]
)
DROP Statement
• Destroys object forever
• Object can not be in use
• Examples:
– DROP TABLE Customers
– DROP PROC pr_salesreport
Reverse-Scripting Objects
Information Schema Views
• ANSI SQL method for extracting metadata
– Implemented as a set of views in MS SQL
• Information_schema.tables
• Information_schema.columns
Chapter 6: Constraints
Constraint Types
• Domain
• Entity
• Referential Integrity
Key Constraints
• Primary Key
• Foreign Key
• Unique
Constraint Scope
•Column Level
CREATE TABLE [dbo].[Customers] (
[CustomerID] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY ,
[CustomerEmail] [varchar] (30) NULL ,
[CustomerState] [varchar] (5) NULL
) ON [PRIMARY]
GO
•Table Level
ALTER TABLE [dbo].[Products] ADD
CONSTRAINT [PK_Products] PRIMARY KEY
(
[ProductID],
[ProductName]
) ON [PRIMARY]
GO
CLUSTERED
Check Constraints
ALTER TABLE [dbo].[Products] ADD
CONSTRAINT [CK_Products_No_Nissan] CHECK
([Productname] <> 'nissan sentra')
Default Constraints
Disabling Constraints
• Bad Data During Constraint Creation?
ALTER TABLE [dbo].[Products]
WITH NOCHECK
ADD CONSTRAINT [CK_Products_No_Nissan]
CHECK ([Productname] <> 'nissan sentra')
• Temporarily During Data Load
ALTER TABLE Products
NOCHECK
CONSTRAINT CK_Products_No_Nissan
Other Data Integrity Objects
• Rule Object
• Default Object
• Non-ansi, outdated
Other Data Integrity Methods
• Triggers
– Good for non-normalized data
• Procedural
– No inserts allowed, only use of Stored Procs that
perform the insert/update/delete after checking data
– Can perform modifications in more than one table
• Application Role
– Client application gets hard coded password
– Updates only allowed through that application
Chapter 7
: Scripts and Batches
Scripts
• Text file containing one or more batches
• Can make use of local variables or system
functions like @@identity
Batches
• A group of on or more SQL statements
into one logical unit
• Any syntax errors in the batch makes the
whole batch fail
• Runtime errors will not undo previous
batch statements
• A batch is not equivalent to a transaction
• Marked by the GO keyword, which is not
sent to the server
Batch Use
• Certain create statements require their
own batch
• Use batches to complete one section of
code before another section starts
• Local variables only have scope in the
current batch
Using EXEC
• Allows dynamic execution of an SQL string
• Cannot reference local variables, only the
string variable it is using
– System vars like @@identity still resolve
• Concatenation should be done in a
variable before passing it to EXEC
• Cannot be used in a UDF
Chapter 8: Views
What is a view
• Stored in the database as a query
• Has standard object name
• Works like a table, with limitations
– Updates restricted
• Provides a layer of abstraction
– Renaming fields
– Hiding fields
• Enterprise edition allows indexing
Creating Views
CREATE VIEW TopSellers
AS
SELECT TOP 10 *
FROM PRODUCTS
ORDER BY ItemsSold
Restrictions
• Expressed as one select statement
– Subqueries and joins are OK
• ORDER BY only allowed if TOP is specified
• Max 1024 columns
• Updates
– Allowed if referencing only one table
– “Instead of” triggers make them updatable in other
cases
• Partioned views increase performance
• Derived columns must have column names
Performance
• Views on Views reduce performance
• Enterprise Edition allows performance
gains:
– Indexed Views
– Partitioned Views
• Indexes on base tables are used
Security
• Can be used to hide sensitive data
• Ownership chain evaluation
Dropping Views
• Schema binding prevents dropping
dependent objects
Lab 8A: Creating and Using Views
Chapter 9: Stored Procedures
Basic Syntax
Types of sprocs
• System stored procedure:
– Name begins with sp_
– Created in master database
– Intention is for application in any database
– Often used by sysadmins
• Local stored procedure:
– Defined in the local database
– Name often starts with pr_
Executing an sproc
• EXEC pr_GetTopProducts
Parameters
• @param as datatype = default [OUT]
Executing an sproc with
parameters
• By Name:
EXEC pr_GetTopProducts
@StartID = 1, @EndID = 10
• By Position:
EXEC pr_GetTopProducts 1, 10
• Leveraging Default values
EXEC pr_GetTopProducts @EndID=10
Order of parameters
• Place parameters with default values at
the end of the list for flexibility of use
Output parameters
• Used to send non-recordset information
back to client
• Procedural insert example, which returns
identity field
Error checking and data validation
• Sprocs that modify data can conditionally
check the data first
Return
• Used to return the success/failure status of
the sproc
• The return statement halts all other
execution
Raising errors
RAISERROR(‘demo error’, 1, 1)
Extended Stored Procs
• Xp_cmdshell ‘dir’
Debugging
Chapter 10: User Defined
Functions
Basic Syntax
CREATE FUNCTION dbo.fn_total(@param1
datatype)
RETURNS datatype2
AS
BEGIN
DECLARE @localvar datatype2
--populate @localvar here
RETURN @localvar
END
3 Types
• Scalar
– Returns a single value
– Evaluated for every row if used in select line
• Inline table values
– Returns a variable of type table
– Single select statement defines the table
• Multi-statement table valued
– Multiple statements populate table values
Uses of Functions
• Can greatly simplify the select line
• Can improve reliablility of data by reducing
the number of joins and encapsulating
queries
• Good choice for summarizing transactional
data
UDF Considerations
• Can not use non-deterministic functions
like getdate(), which depends on system
settings to provide output
• Must use two part name
Creating System Functions
• Create the function in master database
• Change the owner to
system_function_schema:
sp_changeobjectowner
'fn_somefunc2',
'system_function_schema'
Chapter 11:Triggers
Basic Syntax
CREATE TRIGGER trg_one
ON tablename
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SELECT * FROM Inserted
SELECT * FROM Deleted
END
Uses of triggers
• No change of front end code is required to
perform:
– Automation
– Notification
– Logging/Auditing
– Maintaining de-normalized data
Trigger Types
• For or After
– Synonymous – Operates at the end of the
transaction, in addition to the data change
operation
• Instead Of
– Operates in place of the data change
command
• DML
– INSERT, UPDATE & DELETE
• DDL
– On creation/deletion of objects
Trigger Considerations
• “Inserted” table
• “Deleted” table
• Ater trigger participates in the transaction
– Can rollback any data changes if needed
• User needs permissions on any object hit
by the trigger
Chapter 12: Cursors
Record Operations
vs.
Set Operations
Cursor Types
• Sensitivity to database changes
– Dynamic
• Yes – all changes, but most overhead
– Keyset
• Yes, but only for updates and deletes
– Static
• No-Runs from a copy in TempDB
Basic Syntax
DECLARE demo_cursor CURSOR
READ_ONLY
FOR SELECT ProductID FROM Northwind..Products ORDER BY ProductID
DECLARE @ProductName nvarchar(50)
OPEN demo_cursor
FETCH NEXT FROM demo_cursor INTO @ProductName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
DECLARE @message varchar(100)
SELECT @message = 'The product is: ' + @ProductName
PRINT @message
END
FETCH NEXT FROM demo_cursor INTO @ProductName
END
CLOSE demo_cursor
DEALLOCATE demo_cursor
GO
Cursor Options
•
•
•
•
•
GLOBAL/LOCAL
STATIC
KEYSET
DYNAMIC
FAST FORWARD
The Fetch Statement
•
•
•
•
•
•
NEXT
PRIOR
FIRST
LAST
ABSOLUTE
RELATIVE
@@Fetch_Status
• -2 Means row was deleted
• -1 Means past the last row
• Global to all cursors on the current
connection
@@Cursor_Rows
• Returns number of rows available for the
last cursor that was opened on this
connection
• Returns -1 if cursor is dynamic
Performance Issues
• Avoid Cursors when possible
• Consider client-side processing in lieu of
cursors
Chapter 13: Transactions and
Locks
Concurrency Problems
• Lost Updates
– Two transactions select the same row, then update the row
based on the values selected. The last one to save wins.
• Dirty Reads
– One transaction selects data from another
uncommited(unfinished) update
• Nonrepeatable Reads
– Select statement of the same data returns different values
because another transaction has changed the data between
reads
• Phantom Reads
– Update or delete is performed on a set when another transaction
is performing and insert or delete than affects a row in the set
Transaction Isolation Level
• READ UNCOMMITTED
– Allows all concurrency problems
• READ COMMITTED
– Default. Prevents dirty reads.
• REPEATABLE READ
– Only allows phantom reads.
• SNAPSHOT
– New in 2005. Uses row versioning feature to store a copy of the
data in TempDB database. Like serializable with less locking
problems.
• SERIALIZABLE
– Safest, most locking timeout issues
Modifiying the transaction isolation
level
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
Lockable Resources
• Database
• Allocation Unit
– Collection of pages
•
•
•
•
•
•
•
•
Metadata
File
Table
Heap or B-tree
Extent
Page
Key
Row
Lock Modes
• Shared Locks
– Schema Stability (Sch-S)
– Intent Shared(IS)
• Prevents other transactions from gaining (X)
– Shared(S)
– Update(U)
• Read but can’t update until escalated to (X)
• Exclusive Locks
–
–
–
–
–
Shared with Intent Exclusive(SIX)
Intent Exclusive(IX)
Exclusive(X)
Bulk Update(BU)
Schema Modification(Sch-M)
Basic Transaction Syntax
BEGIN TRAN [tranname]
--Multiple data edits here
If @@Error = 0
COMMIT TRAN
Else
ROLLBACK TRAN
Considerations
• Exactly two outcomes of a
transaction:success or failure
• Enforced by locks
• Critical for data integrity between tables
– Foreign key only enforces data contents in
one direction
• Contention problems are geometric in
nature
Managing locks
• Sp_who2
• Sp_lock
Chapter 14: .Net/CLR
Integration
Definitions
• CLR
– Common Language Runtime
• Manages Security
• Manages Memory and Cleanup of Resources
• Assembly
– Compiled .Net Code
• .dll
• .exe
– Houses Classes with Methods and Properties
CLR Objects in SQL Server
•
•
•
•
•
Stored Procedures
Functions
Triggers
Aggregate Functions(UDA)*
User-Defined Types*
– Can perform validation
– Provides for Methods and Types
*No builtin SQL equivalent
When to Use CLR Objects
• Performance is not paramount
• To perform tasks not available in SQL
• Leverage a class library
– Stats
– Mathmatics
– Encryption
• When it works better for you
Enabling CLR
Or
EXEC sp_configure @configname=‘clr enabled’, @configvalue=‘1’
RECONFIGURE WITH OVERRIDE
Adding CLR Objects with Visual
Studio
• Visual Studio Express – CLR projects not
supported
CLR Behind the Scenes
• Compile the code into an assembly
csc /target:library sp1.cs /r:"C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Binn\sqlaccess.dll"
• Use CREATE ASSEMBLY
CREATE ASSEMBLY CLRAssembly
FROM 'C:\tmp\Sp1.dll'
WITH PERMISSION_SET = SAFE
• Use CREATE PROC
CREATE PROC EchoCLR
@Message NVARCHAR(255)
AS
EXTERNAL NAME CLRAssembly.[Sp1].Echo
GO
Chapter 15: SQL 2005/TSQL
Extensions
Common Table Expressions
• CTE’s are an ANSI 1999 Standard
• WITH clause used as a preface for
SELECT, INSERT, UPDATE or DELETE
• Like a “Temporary View”
• CTE Always Produces a Set of Rows and
the Names of Columns for Those Rows
Simple CTE Example
WITH pi(constant)
AS
(
SELECT 3.14159265358979
)
SELECT constant FROM pi
CTE Heirarchical Example
CREATE TABLE Employees
(
EmployeeID int primary key nonclustered,
EmployeeName varchar(50),
ManagerID int
CONSTRAINT FK_ManagerID
FOREIGN KEY References Employees(EmployeeID)
)
CTE Heirarchical Example
--EmployeeID 1 is the boss
INSERT employees(EmployeeID, EmployeeName, ManagerID)
VALUES(1,'Andrew',1)
--Reports to EmployeeID 1
INSERT employees(EmployeeID, EmployeeName, ManagerID)
VALUES(2,'Dave',1)
INSERT employees(EmployeeID, EmployeeName, ManagerID)
VALUES(3,'Anne',1)
--Reports to EmployeeID 3
INSERT employees(EmployeeID, EmployeeName, ManagerID)
VALUES(4,'Alex',3)
INSERT employees(EmployeeID, EmployeeName, ManagerID)
VALUES(5,'Sandy',3)
CTE Heirarchical Example
CREATE FUNCTION SubordinateCount(@ManagerID int)
RETURNS int
AS
BEGIN
DECLARE @count int;
WITH Reports(EmployeeID)
AS
(
SELECT EmployeeID FROM Employees
WHERE ManagerID = @ManagerID
AND EmployeeID <> 1
UNION ALL
SELECT e.EmployeeID FROM Employees e
INNER JOIN Reports r
ON r.EmployeeID = e.ManagerID
)
SELECT @count=count(*)
FROM Reports
RETURN @count
END
CTE Heirarchical Example
select
employeename,
Subordinates=dbo.SubordinateCount(employeeid)
from employees
employeename
---------------------------------------Andrew
Dave
Anne
Alex
Sandy
Subordinates
-----------4
0
2
0
0
.NET Extensions
• Code can be compiled to an assembly,
then referenced in an SQL query
• Applies to functions, stored procedures,
triggers, aggregate functions, user-defined
datatypes(classes)
MARS
• Multiple Active Result Sets
• Applies only to SQL Server 2005 and
ADO.NET 2.0(SQL Native Client)
Service Broker
• Allows for asynchronous execution of
queries
Try Catch syntax
BEGIN TRY
EXEC sp_ProcThatCallsRaiserror
END TRY
BEGIN CATCH
PRINT ‘Exception Occurred’
END CATCH
RowNumber()
SELECT
Result=row_number()
OVER(order by employeename asc),
EmployeeName,
Subordinates=dbo.reports(employeeid)
FROM employees
Result
-------------------1
2
3
4
5
employeename
-------------------------------------------------Alex
Andrew
Anne
Dave
Sandy
Subordinates
-----------0
4
2
0
0
OUTPUT clause
CREATE TABLE DeletedEmployeeIDs
(
employeeid int
)
DELETE Employees
OUTPUT deleted.EmployeeID
INTO DeletedEmployeeIDs
PIVOT Clause
CREATE TABLE SALES (
[Year] INT,
Quarter CHAR(2),
Amount FLOAT
)
INSERT INTO SALES VALUES (2001, 'Q2', 70)
INSERT INTO SALES VALUES (2001, 'Q3', 55)
INSERT INTO SALES VALUES (2001, 'Q3', 110)
SELECT * FROM SALES
PIVOT (SUM (Amount) — Aggregate the Amount column using SUM
FOR [Quarter] — Pivot the Quarter column into column headings
IN (Q1, Q2, Q3, Q4)) — use these quarters
AS P
Year
——2001
2002
Q1
———
100
200
Q2
———
190
250
Q3
-——
165
230
Q4
———
90
180
XML DataType