January 30, 2009 at 9:58 am
Hello,
I have three tables, and each table has three columns. If I wish to add all columns of each table into a new table with three new columns in a new table.
For example,
Table BO
BODDS BOCLEARED BOCLEAREDDATE
BO 2 1/1/2009
BO 3 1/2/2009
BO 4 1/5/2009
Table CT
CTDDS CTCLEARED CTCLEAREDDATE
CT 1 1/1/2009
CT 5 1/3/2009
CT 7 1/5/2009
Table NH
NHDDS NHCLEARED NHCLEAREDDATE
NH 2 1/5/2009
NH 4 1/7/2009
NH 8 1/8/2009
If I wish to add all three tables'data into new table and call it as "Total" table.
Table: Total
DDS CLEARED CLEAREDDATE
BO,CT 3 1/1/2009
BO 3 1/2/2009
CT 5 1/3/2009
BO,CT,NH 13 1/5/2009
NH 4 1/7/2009
NH 8 1/8/2009
Would anyone consider an expert to show me how to write this kind of script?
Thanks in advance
January 30, 2009 at 12:18 pm
Why do you need a table with that information. What you need looks a lot more like a report you are trying to produce, that information will be useless to keep since you have the 3 input tables, so no need to create a new one with the information.
Think about this, if you create this table for the report, how will you update it with new data, when the 3 tables actually change, it will be pretty hard to actually launch an update from those tables, unless you drop the table and recreate it.
If you're looking for a report, just group the selects by date, and add a sum on the BOCount.
As for the string concatenation, you will need to write a function to do this.
Hope it helps,
Cheers,
J-F
January 30, 2009 at 12:34 pm
Hi Jean,
I know you are an expert, but because I'm new to this SQL. Would you please show me how to do the way that I ask you? and for the future reference, would you please show me how to do your way? So I can see the way you design. Please help me
Thank you so much
Very respectful
January 30, 2009 at 12:38 pm
Hello Jean,
Those tables that I've posted which are the scripts that I wrote and save it in view. I have a general table where contains all data and three tables that I have posted which came from one table source.
Would you please show me how to do the way that I asked you, and your way. So I can become an expert like you in a very near future.
Thank You
January 30, 2009 at 12:46 pm
Here,
I'm no expert, and I think you should learn the basics of querying, grouping and all if you're looking to do some reporting.
Here is an untested solution, since you posted no sample data. There is a commented part in which you will need to find a way to concatenate the strings together if you want it the way you posted.
SELECT --DDS, You need to find a way to concatenate the found string into one here.
SUM(Cleared) AS Cleared,
ClearedDate
FROM (SELECT BODDS AS DDS,
BOCleared AS Cleared,
BoClearedDate AS ClearedDate
FROM BO
UNION ALL
SELECT CTDDS AS DDS,
CTCleared AS Cleared,
CTClearedDate AS ClearedDate
FROM CT
UNION ALL
SELECT NHDDS AS DDS,
NHCleared AS Cleared,
NHClearedDate AS ClearedDate
FROM NH) AS DDS
GROUP BY ClearedDate
Hope it helps,
Cheers,
J-F
January 30, 2009 at 1:33 pm
Hello Jean,
I think you misunderstood my question:
I have three tables (Table BO, CT, NH) as given and I wish to add those three tables together where I will have the table "Total".
In the table Total, I wish to have a new name for COALESCE(dbo.BOCLEARED.BODDS, dbo.CTCLEARED.CTDDS, dbo.NHCLEARED.NHDDS) AS DDS
BOCLEAREDDATE,NHCLEAREDDATE,CTCLEAREDDATE -> CLEAREDDATE
BOCLEARED, CTCLEARED, NHCLEARED --> TOTALCLEARED
I wish my new table display like below (The data provided below which are the draw data)
DDS TOTALCLEARED CLEAREDDATE
BO 5 1/1/2009
CT 2 1/2/2009
CT,NH 7 1/3/2009(3 cases of NH, 4 of CT)
NH 1 1/5/2009
Thank you so much for your helps Jean.
Very respectful
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply