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.
Author |
Topic |
DocHoliay66
Starting Member
2 Posts |
Posted - 2014-07-31 : 10:12:17
|
I have two tables one hold all Friday dates for given year (col1 ID, col2 date), second table have three column col1, col22, col3. What I need to do is insert the first Friday date into the second table for col1 and second Friday into col2, third Friday in col3, fourth Friday in col1 and the process repeat until all Friday dates are inserted. I would like to use While loop but stuck on how to iterate through table one to get the data. Any help would be greatly appreciated.Example:table1 data1 01/03/20142 01/10/20143 01/17/20144 01/24/20145 01/31/20146 02/07/20147 02/14/20148 02/21/2014table2 data should look after insert compelete.col1 col2 col301/03/2014 01/10/2014 01/17/201401/24/2014 01/31/2014 02/07/201402/14/2014 02/21/2014 |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-07-31 : 10:22:19
|
quote: Originally posted by DocHoliay66 I have two tables one hold all Friday dates for given year (col1 ID, col2 date), second table have three column col1, col22, col3. What I need to do is insert the first Friday date into the second table for col1 and second Friday into col2, third Friday in col3, fourth Friday in col1 and the process repeat until all Friday dates are inserted. I would like to use While loop but stuck on how to iterate through table one to get the data. Any help would be greatly appreciated.Example:table1 data1 01/03/20142 01/10/20143 01/17/20144 01/24/20145 01/31/20146 02/07/20147 02/14/20148 02/21/2014table2 data should look after insert compelete.col1 col2 col301/03/2014 01/10/2014 01/17/201401/24/2014 01/31/2014 02/07/201402/14/2014 02/21/2014
If your requirement is that you have to use a while loop you certainly can, but it is easier and more efficient to write the query using set based operators. Here is an example of how you could write a set based query to accomplish what you are looking for:SELECT * FROM( SELECT [DateCol], (Idcol-1)/3 AS ROW, (Idcol-1)%3 AS COL FROM Table1)s PIVOT( MAX([Date]) FOR COL IN ([0],[1],[2])) p |
|
|
DocHoliay66
Starting Member
2 Posts |
Posted - 2014-07-31 : 11:42:41
|
Hi James K, No, I don't have to use "While Loop", I tried your solution and it work just the way I wanted. Thank you very much for you help. DocHoliday |
|
|
|
|
|
|
|