UNPIVOT - How to perform in 2000 1

[awaria]

MS SQL Server 2000

I have a table that I need to unpivot the columns, and UNPIVOT does not work in 2000.

In 2005 I would do the following;

select username, fieldname as dept, dept_value from TABLENAME
UNpivot
(dept_Value for fieldname in (10,20,30)) as uPVT
Where upvt.dept_value = 1

How can this be accomplished in 2000?

The table structure is;
username |dept 10 | dept 20 | dept 30
jsmith 1 0 1

Desired results to be;
Username | Dept | Dept_Value
jsmith 10 1
jsmith 30 1

A records for jsmith | dept 20 would be excluded because the dept_value = 0.

Thanks,

awaria

 

[gmmastros]

You would use a UNION ALL query, like this:

Select UserName, 10 As Dept, [dept 10] As Dept_Value
From   YourTableName
Where  [dept 10] > 0 

Union All

Select UserName, 20, [dept 20]
From   YourTableName
Where  [dept 20] > 0 

Union All

Select UserName, 30, [dept 30]
From   YourTableName
Where  [dept 30] > 0

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[awaria]

George,

Thanks for the post.

I did implement the UNION ALL query, and it works.

However, I have run into a limitation issue with sql 2000, which is, my UNION ALL query references over 256 tables, or 256 references of the same table. We have slightly over 256 Depts.

When I comment out several dept unions to get the count down to about 200, then everything works fine.

Any thoughts on gettng around this limitation?

Thanks again,

awaria

 

[gmmastros]

Oh wow. So you have a table with 256 columns. Yikes.

Anyway... you can make this work by creating a temp table and then inserting in to the temp table for each department.

Something like this (not tested).

Create Table #Output(UserName VarChar(100), Dept Int, Dept_Value Int)

Insert Into #Output(UserName, Dept, Dept_Value)
Select UserName, 10 As Dept, [dept 10] As Dept_Value
From   YourTableName
Where  [dept 10] > 0 

Insert Into #Output(UserName, Dept, Dept_Value)
Select UserName, 20, [dept 20]
From   YourTableName
Where  [dept 20] > 0 

Insert Into #Output(UserName, Dept, Dept_Value)
Select UserName, 30, [dept 30]
From   YourTableName
Where  [dept 30] > 0 

etc....

Select * From #Output

Notice the creation of the #Output table. I use varchar(100) for the username and ints for the dept and dept_value. You should adjust the data types accordingly.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[awaria]

George,

That works perfectly.

Many thanks once again for sharing your expertise.

awaria