pivoting query on t-sql

[bher2]

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

[David Portas]

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,saledat e))

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.cfm?ArticleID=15608

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

[Steve Dassin]

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.

https://www.rac4sql.net