Jim writes "I have a problrm where I have 2 tables. The first table contains quantities and dates for a part number plus a grouping value called set_id. There are duplicate part numbers in this table. The second table contains One row for each part_num / set_id combination. The quantities and dates are inserted into different columns. The 2nd table has 10 sets of date / quantity columns (date1 / qty1 date2 / qty2...date10/qty10). These date/qty columns get populated in date order. Below is a sample of the two tables:part_num QTY Order_date set_id statusitem1 6 3/2/2007 set_a 0item1 2 3/16/2007 set_a 0item1 11 3/9/2007 set_a 0item1 17 3/2/2007 set_a 0part2 13 3/23/2007 grp_b 0part2 26 3/9/2007 grp_b 0part2 17 3/2/2007 grp_b 0wire3 7 3/16/2007 sec_1 0wire3 16 3/9/2007 sec_1 0wire3 28 3/2/2007 sec_1 0wire3 22 3/23/2007 sec_1 1wire3 18 3/30/2007 sec_1 1wire3 7 4/6/2007 sec_1 0
This table needs to be inserted into another permanent table as so:Part set date_1 qty_1 date_2 qty_2 date_3 qty_3 date_4 qty_4 date_5 qty_5 date_6 qty_ 6 date_7 qty_7item1 set_a 3/2/2007 6 3/9/2007 11 3/16/2007 2part2 grp_b 3/2/2007 17 3/9/2007 26 3/23/2007 13 wire3 sec_1 3/2/2007 28 3/9/2007 16 3/16/2007 7 3/23/2007 22 3/30/2007 18 4/6/2007 7
Alas, my question. How can I insert the first table above into the second table? These tables live in a SQL Server 2005 W2K3 server. My task is to move the data from the first table into the second. In the destination table, the date columns must be populated in ascending order. In addition to the insert, there are some conditions and updates on the first table. - the value of status must be 0 for a row to be inserted into the 2nd table- the order date must be a friday - something along the lines of datepart(dw) = 6 - if a record is validated on those 2 criteria, the status is set to -1 until the entire insert completes. Once the data is successfully inserted into the second table, the status is set to 1 in the first tablethe status is updated to 1 in the original table.I know there's alot of information here. I'm primarily stuck on how to insert the date / qty data from many rows into 1.Many thanks."
transposing many rows into one row with many columns 
Posted - 2007-03-20 : 09:07:34
