multiple selects that differ in number of columns

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

multiple selects that differ in number of columns

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-02-23 : 13:35:39

Hi

I am using sql server 20005 and had a question on a workaround for the following which is the last part in the stored proc.

The output will be used by another program like excel vba or crystal for design/formatting.

I want to combine these into one temp table like a final select for the report designer to use. I tried UNION/UNION ALL but in this case the column numbers differ.

Any help or suggestion is appreciated.

--Final output for report

select
TradeMonthName,
TradeYear,
ExecuteToCoverDiff = avg(LevelDiff),
ResponseCount = avg(convert(float, ResponseCount)),
TradeMonth
from
#Temp

group by
TradeMonthName,
TradeYear,
TradeMonth
order by
TradeMonth

select
TradeYear,
avg(LevelDiff),
avg(convert(float, ResponseCount))
from
#Temp
where
TradeMonth >= 10
and TradeMonth <= 12
group by
TradeYear

select
Client,
TradeMonthName,
TradeYear,
avg(LevelDiff),
avg(convert(float, ResponseCount)),
TradeMonth

from
#Temp
where
TradeMonth >= 7
and Client like 'Albert%'
group by
Client,
TradeMonthName,
TradeYear,
TradeMonth
order by
Client,
TradeMonth

mfemenel
Professor Frink

1421 Posts

Posted - 2010-02-23 : 13:45:58

you can pad your results with nulls or blanks to make the results 'equal' for example:
Select 1 as myval, 2 as myval2
UNION
select NULL as myval,3 as myval2

Mike
"oh, that monkey is going to pay"

Kristen
Test

22859 Posts

Posted - 2010-02-23 : 13:48:02

Use UNION ALL but add additional, dummy, columns (select NULL), so all queries have the same number of columns.

The columns must also have the same datatype (in each SELECT in the UNIONs), so group things that are of similar logic data, and also identical type.


SELECT IntCol1, CharCol2, NULL
FROM ...
UNION ALL
SELECT IntCol3, NULL, DateCol4
FROM ...
UNION ALL
SELECT NULL, CharCol5, DateCol6
FROM ...

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-02-23 : 14:23:14

Thanks a lot for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-24 : 10:26:16

also make sure for NULL values used in first cast them to appropriate data types (data type of actual column whose placeholder you're using it for (DateCol4 in above case))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2010-02-24 : 11:30:37

quote:
Originally posted by visakh16
also make sure for NULL values used in first cast them to appropriate data types (data type of actual column whose placeholder you're using it for (DateCol4 in above case))

Used to have to be careful to do that, but I don't think it is needed any more (since SQL7 even maybe?)

But I haven't checked it recently ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-25 : 02:31:50

quote:
Originally posted by Kristen

quote:
Originally posted by visakh16
also make sure for NULL values used in first cast them to appropriate data types (data type of actual column whose placeholder you're using it for (DateCol4 in above case))

Used to have to be careful to do that, but I don't think it is needed any more (since SQL7 even maybe?)

But I haven't checked it recently ...

I beleive it would assume the datatype with highest precedence

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 02:45:43

It used to default to some assuption from the first SELECT, now it checks with datatypes in other SELECTs in the UNION.

SELECT NULL has no particular datatype (well, I suppose it must be assigned a datatype!)

However,


SELECT NULL AS Foo
UNION ALL
SELECT CONVERT(int, NULL)

will cause the first column to be datatype = INT

Seems to work with datetime, varbinary(10), etc.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-25 : 02:54:28

select null
union
select 'test' as test
union all
select getdate()

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 03:02:42

Yeah, well that's silly!

Refer my original:


SELECT IntCol1, CharCol2, NULL
FROM ...
UNION ALL
SELECT IntCol3, NULL, DateCol4
FROM ...
UNION ALL
SELECT NULL, CharCol5, DateCol6
FROM ...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-25 : 05:06:51

quote:
Originally posted by Kristen

Yeah, well that's silly!

Refer my original:

SELECT IntCol1, CharCol2, NULL
FROM ...
UNION ALL
SELECT IntCol3, NULL, DateCol4
FROM ...
UNION ALL
SELECT NULL, CharCol5, DateCol6
FROM ...

My point when no value is explitely casted, SQL Server will assume the datatype with higher precedence

--Datetime is considered

select null
union
select 'test' as test
union all
select getdate()

--Integer is considered
select 12
union
select 'test' as test

--Decimal is considered
select 0x020100010C000000
union
select 1.1 as test

--Datetime is considered
select 45.435
union all
select getdate()

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 05:39:06

Yup, I understand.

But in a real-query I would consider it an error / wrong to have Varchar and Date in the same column (unless there is an explicit cast)

But I don't consider it "wrong" to have


SELECT NULL
UNION ALL
SELECT IntColumn

i.e. I do not feel it wrong NOT to have an explicit cast on the first row - where I have a dummy column/value.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-25 : 06:18:58

<<
i.e. I do not feel it wrong NOT to have an explicit cast on the first row - where I have a dummy column/value.
>>

Yes it is

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-02-25 : 07:35:07

I think it creates errors.

Likihood is that I have, say, 20 columns from Union1, and 20 columns from Union2 *10* of which are the same.

So on first select I have 10 additional NULL values, and same on second select.

If I have to CAST these, explicitly, I will likely get some wrong, or change some columns in the future and forget to change the corresponding CAST.

Thus I think that a bare "NULL" for an unused column in a Union's select is "safer" - from a maintenance perspective.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-25 : 07:40:58

<<
Thus I think that a bare "NULL" for an unused column in a Union's select is "safer" - from a maintenance perspective.
>>

Yes as it would be omitted from guessing the datatype

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources