February 15, 2011 at 10:03 am
Hi,
I have run into a little bit of a stumper. I have 3 tables that I am trying to return records from, but I need the records returned as separate rows if possible.
Each table will have a different number of columns, and datatypes, selected from them. I have tried using UNION ALL, but have found that each SELECT statement needs to have the same number of columns (which can be accomplished by using some extra NULLS), but since the types are different, it is failing on the conversion of some of the data.
Is there any other way to accomplish this?
ex:
SELECT id, date, value, time, null from table1
union all
SELECT id, value, flag, value2, location, time from table2
union all
SELECT id, value,date, null, null from table3
Expected Results:
TABLE1 Record 1
TABLE1 Record 2
TABLE1 Record 3
TABLE2 Record 1
TABLE3 Record 1
...etc...
This will cause me an error because in my actual queries, some datatypes cannot be converted to each other.
February 15, 2011 at 10:23 am
If you really need to do this, then line up the columns that match, in your select statement, and use Null in the ones that don't match.
Select IDColumn, NumericColumn, VarcharColumn, Null, Null
from MyTable1
union all
select IDColumn, NumericColumn, Null, DateTimeColumn, Null
from MyTable2
union all
select IDColumn, NumericColumn, Null, Null, BinaryColumn
from MyTable3;
It'll work, but I'm having trouble understanding why you would want to do this. Normally, a column, in a table or in a select statement, has a meaning. Mixing columns from multiple tables, where they aren't even the same data type, kind of defeats the purpose of "columns" in datasets.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 15, 2011 at 10:54 am
What I am basically trying to do is send a CSV file with records from 3 different tables. I know I can just do it by creating 3 different procs, but I was looking to dump all of the records into 1 file instead of having multiple files.
I have 1 table for patient lab results, 1 table for medications, and 1 with other data. So, needless to say, the column number and types are going to be different.
I was then thinking on my Final Select statement where I was going to use the UNION ALL, I would add a column to the front of each record to indicate which table it came from. Then, the people on the other end would be able to parse it and store it appropriately.
SELECT 'MED', a, b, c from MED
UNION ALL
SELECT 'LAB', a, c, d, e, f from LABS
UNION ALL
SELECT 'INFO', d, e from INFO
Results in CSV form:
MED, a, b, c
MED, a, b, c
MED, a, b, c
LAB, a,c,d,e,f
LAB, a,c,d,e,f
INFO, d,e
..etc
February 15, 2011 at 7:42 pm
How are you planning to get the mash-up of rows into a single CSV file? First thing I think of is run multiple dataflow tasks serially in an SSIS package, each putting its delimited flat-file output to the same file. That way, since it's just a flat file, the different number of columns and differences in source data types won't matter. As you've already said, you'll want a row-type in each record, presumably so the recipient can load the data into whatever application they're using.
February 16, 2011 at 7:10 am
I'd do it by exporting to three separate files. That'll make the import on the other end easier.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply