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)
 Query Help Field Names as Columns

Author  Topic 

kim1
Starting Member

1 Post

Posted - 2007-08-10 : 14:56:44
Basically I have two tables ALC and WKS. The table ALC Contains the data I need and look like this

ITNBR AVL01 AVL02 AVL03 AVL04 AVL05 AVL06 AVL07 AVL08 AVL09
___________________________________________________________
22333 00235 00235 00235 00235 00235 00235 00235 00235 00235
23423 00001 00001 00001 00001 00001 00001 00001 00001 00001
86788 00934 00934 00934 00934 00934 00934 00934 00934 00934

ITNBR is obviously an item number and AVL01 is the beggining of each week and AVL02 is the begginning of next week and so on. The values 235 ,1 , 934 are inventory values. These are how much the company has that week.

The second table is WKS or Weeks. This table contains these values

DAT0001 DAT0002 DAT0003 DAT0004 DAT0005 DAT0006 DAT0007 DAT0008 DAT0009
_______________________________________________________________________________
1070620 1070630 1070710 1070720 1070730 1070810 1070820 1070830 1070910

These dates correspond to the inventory values. For example DAT01 is 1070620 (which is 06/20/2007). On this date the inventory value is 235 under AVL01.

Basically what I want to do is use the WKS table as Column Headings and use the ACL as data like this

ITNBR 1070620 1070630 1070710 1070720 1070730 1070810 1070820 1070830 1070910
_____________________________________________________________________________
22333 0000235 0000235 0000235 0000235 0000235 0000235 0000235 0000235 0000235
23423 0000001 0000001 0000001 0000001 0000001 0000001 0000001 0000001 0000001
86788 0000934 0000934 0000934 0000934 0000934 0000934 0000934 0000934 0000934

I was thinking you could

SELECT ALC.ITNBR, ALC.AVL01 AS (SELECT DAT0001 FROM PYFLIB.WPSALC, PYFLIB.WPSWKS WKS), PYFLIB.WPSALC.AVL02.....

But that didn't work




ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-10 : 15:15:40
instead of posting data, post SQL Scripts to create temp tables and enter data in those table so that its easier for others to test and help u in better way

Ashley Rhodes
Go to Top of Page
   

- Advertisement -