pivoting query on t-sql

gud day.

please help me. im working right now on a case study that will
retrieve/produce a simple report on sql. my problem is I dont know how
to pivot queries like in access. please help me. thanks

re: pivoting query on t-sql

Here's an example of a simple crosstab in SQL. Monthly Sales by region:

CREATE TABLE DailySales (region CHAR(10), saledate DATETIME, saleamount
DECIMAL(10,2) NOT NULL, PRIMARY KEY (region,saledate))

SELECT region,
SUM(CASE MONTH(saledate) WHEN 1 THEN saleamount END) AS jan,
SUM(CASE MONTH(saledate) WHEN 2 THEN saleamount END) AS feb,
SUM(CASE MONTH(saledate) WHEN 3 THEN saleamount END) AS mar,
SUM(CASE MONTH(saledate) WHEN 4 THEN saleamount END) AS apr,
SUM(CASE MONTH(saledate) WHEN 5 THEN saleamount END) AS may,
SUM(CASE MONTH(saledate) WHEN 6 THEN saleamount END) AS jun,
SUM(CASE MONTH(saledate) WHEN 7 THEN saleamount END) AS jul,
SUM(CASE MONTH(saledate) WHEN 8 THEN saleamount END) AS aug,
SUM(CASE MONTH(saledate) WHEN 9 THEN saleamount END) AS sep,
SUM(CASE MONTH(saledate) WHEN 10 THEN saleamount END) AS oct,
SUM(CASE MONTH(saledate) WHEN 11 THEN saleamount END) AS nov,
SUM(CASE MONTH(saledate) WHEN 12 THEN saleamount END) AS [dec]
FROM DailySales
GROUP BY region

These articles give examples of more complex, dynamic crosstabs:

http://www.sqlteam.com/item.asp?ItemID=2955
http://www.sqlmag.com/Articles/Index...rticleID=15608

--
David Portas
------------
Please reply only to the newsgroup
--

re: pivoting query on t-sql

Check out the RAC utility.It is similar to
Access crosstab and has many more features/options.
You will find it as easy to use as Access.

www.rac4sql.net