Grouping issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Grouping issue

Author

Topic

davidliv
Starting Member

45 Posts

Posted - 2004-05-16 : 22:28:46

Okay, here is my table format

table structure
item_id int
itemtype_id
item_qtr datetime
item_cost int

Data Example
ID..Type...Qtr.......Cost
1....1.....1/1/2003...55
2....1.....4/1/2003...45
3....1.....7/1/2003...50
4....1.....10/1/2003..40
5....1.....1/1/2004...50
6....1.....4/1/2004...25
7....1.....7/1/2004...10
8....1.....10/1/2004..30
9....2.....1/1/2003...55
10...2.....4/1/2003...45
11....2.....7/1/2003...50
12....2.....10/1/2003..40
13....2.....1/1/2004...50
14....2.....4/1/2004...25
15....2.....7/1/2004...10
16....2.....10/1/2004..30

Here is what I want my results to look like, but I can't figure out the grouping.
................2003............2004.....
Item Type...Q1..Q2..Q3..Q4..Q1..Q2..Q3..Q4
1...........55..45..50..40..50..25..10..30
2...........55..45..50..40..50..25..10..30

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-16 : 22:46:37

Search for pivot table on here. You will find all kinds of information that's exactly what you need. You should be able to find about 3 million scripts you can just cut, paste, and modify.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

davidliv
Starting Member

45 Posts

Posted - 2004-05-17 : 00:34:11

The crosstab stored procedure does not allow for rollup on the pivot field.

I can return:
........q1..q2..q3..q4
Item 1..22..23..32..10
Item 2..20..40..13..41
Item 3..50..30..12..55

or

.......2003.2004.2005.2006
Item 1..22...23...32...10
Item 2..20...40...13...41
Item 3..50...30...12...55

What I need is:

.............2003.........2004...............2005...........2006
........q1..q2..q3..q4.q1..q2..q3..q4.q1..q2..q3..q4.q1..q2..q3..q4
Item 1..22..23..32..10.22..23..32..10.22..23..32..10.22..23..32..10
Item 2..20..40..13..41.....
Item 3..50..30..12..55.....

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-05-17 : 02:09:13

But - how can you have multiple column names????

You'll need to have your crosstab column names Q1 2003, Q2 2003 etc, then manipulate in your display layer.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources