cross tab query in sql 2000

Question

Post reply

cross tab query in sql 2000

atif saeed khan

SSC Veteran

Points: 278
More actions
March 7, 2007 at 4:36 am

#98177

hi!

i have one table like this.

id     res_date     tunit     tres     tin     tdep

7     03-03-2007    10        0       0        0

7     04-03-2007    10        0       0        0

7     05-03-2007    10        0       0        0

7     06-03-2007    10        0       0        0

7     07-03-2007    10        0       0        0

-----------------------------------------------------

the result im looking for shud be like this.

      03-03-2007     04-03-2007     05-03-2007     06-03-2007     07-03-2007

tunit     10                  10                  10                 10                  10

tres       0                   0                    0                   0                    0

tin         0                   0                    0                   0                    0

tdep       0                   0                    0                   0                    0

how it can b posible ?

Kindest Regards,

Atif Saeed Khan
March 12, 2007 at 8:00 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 1

There's gotta be a thousand posts on this web site for "crosstab" and "transpose" and "pivot". Do a search on it and I'm sure you'll find one that fits. Also, read about "Crosstab" in Books Online... good simple example...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Marvin D SSCarpal Tunnel Points: 4058 More actions · Answer 2

here is an example of a simple cross tab query. Hope it helps.

SELECT emp.LastName + ', ' + emp.FirstName AS 'Employee Name',

SUM(CASE month(orderdate) WHEN 1 THEN 1 ELSE 0 END) AS 'Jan',

SUM(CASE month(orderdate) WHEN 2 THEN 1 ELSE 0 END) AS 'Feb',

SUM(CASE month(orderdate) WHEN 3 THEN 1 ELSE 0 END) AS 'mar',

SUM(CASE month(orderdate) WHEN 4 THEN 1 ELSE 0 END) AS 'Apr',

SUM(CASE month(orderdate) WHEN 5 THEN 1 ELSE 0 END) AS 'May',

SUM(CASE month(orderdate) WHEN 6 THEN 1 ELSE 0 END) AS 'Jun',

SUM(CASE month(orderdate) WHEN 7 THEN 1 ELSE 0 END) AS 'Jul',

SUM(CASE month(orderdate) WHEN 8 THEN 1 ELSE 0 END) AS 'Aug',

SUM(CASE month(orderdate) WHEN 9 THEN 1 ELSE 0 END) AS 'Sep',

SUM(CASE month(orderdate) WHEN 10 THEN 1 ELSE 0 END) AS 'Oct',

SUM(CASE month(orderdate) WHEN 11 THEN 1 ELSE 0 END) AS 'Nov',

SUM(CASE month(orderdate) WHEN 12 THEN 1 ELSE 0 END) AS 'Dec'

FROM employees emp, orders ord

WHERE emp.employeeID INNER JOIN ord.employeeID

AND YEAR(OrderDate) = 1997

GROUP BY emp.LastName + ', ' + emp.FirstName

ORDER BY emp.LastName + ', ' + emp.FirstName

Marvin Dillard
Senior Consultant
Claraview Inc