Author |
Topic |
chapm4
Yak Posting Veteran
58 Posts |
Posted - 2011-09-14 : 23:00:12
|
I am creating a new table from two different tables that will be a stored procedure. I am using Insert Into to create the table (I will eventually delete data and just pick the most recent 50 records each time the procedure is triggered).SELECT a.resultsID, a.result.Time, b.compoundName, b.concentrationINTO resultsCombinedFROM aINNER JOIN bON a.resultID=b.resultIDMy issue is that for each resultID there is 3 compoundName s and 3 concentrations and I want both of those in one record based on the resultID. The result.Time is the same for each resultID.1 09142011 09:53:00 Ca .04271 09142011 09:53:00 Cl .03791 09142011 09:53:00 Al .07972 09142011 09:57:03 Ca .04322 09142011 09:57:03 Cl .02342 09142011 09:57:03 Al .0765AND I WANT: (With the compound name as the column name)1 09142011 09:53:00 .0427 .0379 .0797 2 09142011 09:57:03 .0432 .0234 .0765Please help me get there |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-15 : 00:17:51
|
something likeSELECT resultsID,Time,[Ca],[Cl],[Al]FROM tablePIVOT (SUM(concentration) FOR compoundName IN ([Ca],[Cl],[Al]))p ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
chapm4
Yak Posting Veteran
58 Posts |
Posted - 2011-09-15 : 17:30:17
|
This may work, but does anyone else have any suggestions? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
chapm4
Yak Posting Veteran
58 Posts |
Posted - 2011-09-15 : 17:48:37
|
Nothing is wrong. Haven't had a chance to try yet. I just like to get a few different ideas on how to approach the same problem. Gives me and others reading the solution some alternatives. |
|
|
chapm4
Yak Posting Veteran
58 Posts |
Posted - 2011-09-15 : 23:38:04
|
Thanks for the reply visakh16, I really wasn't aware of the pivot and was thinking of the pivot table in Excel. Good read on it [url]http://msdn.microsoft.com/en-us/library/ms177410.aspx[/url] if anyone else is interested.I do have another question on this though...So I have my NewTable and it is on a sql 2008 box in a database named Lab. I have a link set up on a sql 2005 box on the same network called sqllink\sqlinstance. Object is to make this a stored procedure that I guess runs on the server 2005 box???? And I need it to only return new records (that it hasn't already put in the NewTable).Sorry to be requesting so much help, but a little over my head at this point. Hopefully I can help others someday. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-16 : 11:57:03
|
why do you want to create remote procedure? Any specific reasons why you cant do in 2008 box itself?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
X002548
Not Just a Number
15586 Posts |
|
X002548
Not Just a Number
15586 Posts |
|
chapm4
Yak Posting Veteran
58 Posts |
Posted - 2011-09-16 : 16:20:14
|
quote: Originally posted by visakh16 why do you want to create remote procedure? Any specific reasons why you cant do in 2008 box itself?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/
The database resides on a sql 2008 box which is on one network. There is another sql 2005 box which is on that network and also on a PLC network (two nics). The company wants to push this lab data to a PLC for reporting. I set up a linkedserver from the 2008 box to the 2005 box. I need a stored procedure on the 2005 box that the PLC program will trigger to pick up the last....let's say 20 records to push to the PLC. The reason I asked about combining the two tables into one is the Lab data is pushed to the sql 2008 box into two tables (that's the way the xray machine in the lab pushes it out to a Access App and then it pushes it to SQL. I am certainly open to a "better" way of doing things, but the ultimate goal is to link to that Lab database and have a stored procedure on the other server to be triggered by the the PLC. |
|
|
X002548
Not Just a Number
15586 Posts |
|
X002548
Not Just a Number
15586 Posts |
|
chapm4
Yak Posting Veteran
58 Posts |
Posted - 2011-09-20 : 14:30:35
|
Sorry X002548, don't have a clue what you are talking about. Is the remote link not the way to go? |
|
|
|