Odd Cross Tab - 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

SQL Server Development (2000)

Odd Cross Tab

Author

Topic

timgaunt
Posting Yak Master

115 Posts

Posted - 2005-09-25 : 10:24:27

Hi,

I should start out by saying that I'm not sure if this is even possible but here goes, basically I need to format a number of records from:


ID  |  Name  |  Type
1   |    A   |   T1
2   |    B   |   T1
3   |    C   |   T2
4   |    D   |   T1
5   |    E   |   T3

To:


T1  |  T2  |  T3
A   |   C  |  E
B   | NULL | NULL
D   | NULL | NULL

I've seen the crosstab code and read pretty much all the comments but the closest I can get is something like this:


T1  |  T2  |  T3
A   | NULL | NULL   
B   | NULL | NULL
NULL|  C   | NULL
D   | NULL | NULL
NULL| NULL |  E

Has anyone got any ideas how I can transform the data like this and "jumping" the nulls?

Thanks

Tim

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-26 : 00:43:43

Did you try this?

Select columns from (your CrossTab query) T where T1 is not null

Madhivanan

Failing to plan is Planning to fail

timgaunt
Posting Yak Master

115 Posts

Posted - 2005-09-26 : 04:22:37

Hi Madhivanan,

Thanks for your response, no I didn't try that because it wouldn't produce the required results, sorry I wasn't very clear with my question, it needs to move the Null values "up" the colums so to speak so if there's only NULLs in T2 before the first record then it'll move the next value to the top of the column in line with A. Doing what you suggest would loose the values in T2 and T3.

I'm thinking I may have to move this into a multidimensional array which is something I didn't really want to do but it may be the only solution :(

Any other ideas would be useful.

Tim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources