Dynamic Columns in SQL Server 2000

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)

Dynamic Columns in SQL Server 2000

Author

Topic

GMANAFIP
Starting Member

13 Posts

Posted - 2009-01-22 : 03:58:42

I need to create a query to create my columns dynamically as employee names and their case id numbers (multiple numbers per employee). Do I us sub queries? What is the best way to do that? The result should look something like this:

John Jane Fred
222 333 444
111 223 224
123 555

I tried searching on SQL Team but the examples uses SQL 2005 syntax of PIVOT which does not seem to work in 2000. Any help you could give would be great.

Thanks

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-22 : 04:04:44

use cross tab
select max(case when employeename = 'john' then value end ) as john,
max(case when employeename = 'jane' then value end ) as jane,
max(case when employeename = 'fred' then value end ) as fred
from urtable

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-22 : 04:07:01

can u post ur table Structure

GMANAFIP
Starting Member

13 Posts

Posted - 2009-01-22 : 05:03:09

quote:
Originally posted by Nageswar9

can u post ur table Structure

table 1
emloyeeid
caseid
date_updated
materialid

table 2
employeeid
lastname
firstname

basiclly the result I have is come out like this:
Name ID
John - 1
John - 2
John - 3

Fred - 3
Fred - 2

Jane - 2
Jane - 3

And I need:

John Jane
1 2
2 3
3
etc, etc. So I'm using an inner join in the select statement and 'in' in the where clause to list all the employee lastnames. I know I'm probably repeating myself but I just wanted to make sure I'm clear. Thanks for any help you could give.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-22 : 05:14:11

quote:
Originally posted by GMANAFIP

quote:
Originally posted by Nageswar9

can u post ur table Structure

table 1
emloyeeid
caseid
date_updated
materialid

table 2
employeeid
lastname
firstname

basiclly the result I have is come out like this:
Name ID
John - 1
John - 2
John - 3

Fred - 3
Fred - 2

Jane - 2
Jane - 3

And I need:

John Jane
1 2
2 3
3
etc, etc. So I'm using an inner join in the select statement and 'in' in the where clause to list all the employee lastnames. I know I'm probably repeating myself but I just wanted to make sure I'm clear. Thanks for any help you could give.

did u try the cross tab which i had given the query

GMANAFIP
Starting Member

13 Posts

Posted - 2009-01-22 : 05:24:43

quote:
Originally posted by bklr

quote:
Originally posted by GMANAFIP

quote:
Originally posted by Nageswar9

can u post ur table Structure

table 1
emloyeeid
caseid
date_updated
materialid

table 2
employeeid
lastname
firstname

basiclly the result I have is come out like this:
Name ID
John - 1
John - 2
John - 3

Fred - 3
Fred - 2

Jane - 2
Jane - 3

And I need:

John Jane
1 2
2 3
3
etc, etc. So I'm using an inner join in the select statement and 'in' in the where clause to list all the employee lastnames. I know I'm probably repeating myself but I just wanted to make sure I'm clear. Thanks for any help you could give.

did u try the cross tab which i had given the query

yes, I did. However, it only returns the first record (postalcode) per employee:

(using the employee table in the northwind database of SQL Server 2000)

select max(case when employeeid = 1 then postalcode end ) as fuller,
max(case when employeeid = 2 then postalcode end ) as Leverling,
max(case when employeeid = 3 then postalcode end ) as Peacock
FROM Employees

I need mutiple codes per employee. How do I loop through and display all codes for each employee?

GMANAFIP
Starting Member

13 Posts

Posted - 2009-01-25 : 07:42:12

Thanks for the response. I tried this but I keep getting null's in my result. I even tried using "isnull(max(case when employeeid = 1 then postalcode end ) , 0) as jane" in the select statement but I either get 0's or null instead of just the postalcode excluding the non-postalcodes. How do I eliminate the null/0's and just return the postalcode? I use a group by postalcode. Should I use something else?

quote:
Originally posted by bklr

use cross tab
select max(case when employeename = 'john' then value end ) as john,
max(case when employeename = 'jane' then value end ) as jane,
max(case when employeename = 'fred' then value end ) as fred
from urtable

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-26 : 19:00:51

Search for Dynamic crosstab in here .Post real expected output.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-27 : 07:19:49

hi gmanafip,
try this

declare @tab table(Name varchar(32),ID int)
insert into @tab select
'John ', 1 union all select
'John ', 2 union all select
'John ', 3 union all select
'Fred ', 3 union all select
'Fred ', 2 union all select
'Jane ', 2 union all select
'Jane ', 3

select max(case when name = 'john' then id end) as john,
max(case when name = 'fred' then id end) as fred,
max(case when name = 'jane' then id end) as jane
from (select *,(select count(1) from @tab where name = t.name and id <=t.id )as row from @tab t)a
group by row

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources