Combining fields

Not concatenation, more... err.. I don't know what you'd call it.

SELECT
DISTINCT [C01241 Opened].[Col004] AS OpenerEmail,
[C01241 External Data].[DMCEMAIL] AS ExternalDataEmail,
[C01241 Internal Data].[Col15] AS InternalDataEmail
FROM [C01241 Opened]
LEFT JOIN [C01241 External Data] ON [C01241 External Data].[DMCEMAIL] = [C01241 Opened].[Col004]
LEFT JOIN [C01241 Internal Data] ON [C01241 Internal Data].[Col15] = [C01241 Opened].[Col004]

(Apologies for the table/col names, this is all very temporary)

So I've got a table, [C01241 Opened], which details all the people who registered. Those people might turn up in table [C01241 External Data], or they might turn up in [C01241 Internal Data]. Yes, they will always be in one or the other, and no, they won't appear in both.

At the moment, I just pull in the email address. But the client, of course, wants a whole bunch of fields that occur in the 'original data' tables: Firstname, Lastname, Company, Favourite color, etc.

What I want to know is if - and how - I can make the query output one column for each of the required fields, but populate it from either of the two 'original data' tables, depending on where their email address pops up in.

Does that make sense?look into union if I understand your question corretly this should work|||I might not be understanding UNION correctly, but if I do, then I should have added that the two 'original data' tables have absolutely no similarities in structure. Does this make a difference?

Edit:
(sorry, that was a stupid thing to say and I've just realised why :rolleyes: Thanks :) )|||Use the Coalesce function:
SELECT DISTINCT
[C01241 Opened].[Col004] AS OpenerEmail,
Coalesce([C01241 External Data].[DMCEMAIL], [C01241 Internal Data].[Col15]) AS DataEmail
FROM [C01241 Opened]
LEFT JOIN [C01241 External Data] ON [C01241 External Data].[DMCEMAIL] = [C01241 Opened].[Col004]
LEFT JOIN [C01241 Internal Data] ON [C01241 Internal Data].[Col15] = [C01241 Opened].[Col004]

Combining 3 columns into one (not concatenation)

Greetings,
I am trying to "Fix" a poorly normalized table, and I wanted some info on the best way to go about this. It is an orders table that has items associated with it, and also "add-ons" to those items in the same table, like so:

order# Part# Addon1 Addon2 Addon3

What I would like to do is break the addons into a new table. Is there a way using a query/view/SP to bring all the addon fields into one column to create a new table with? or would I have to create some form of append to add the additional columns one at a time. Here is an example of what I want:

Old Table: addon1 Addon2 Addon3

New Table:
Addon1
Addon2
Addon3

Of course I would also provide a link between the part and the applicable addons.

Thanksselect Order, Part, Addon1 as Addon from [YourTable] where Addon1 is not null
UNION
select Order, Part, Addon2 as Addon from [YourTable] where Addon2 is not null
UNION
select Order, Part, Addon3 as Addon from [YourTable] where Addon3 is not null

column name alias concatenation

I have a web application where I would like to return a dynamic column name using aliasing. below is an example:

select hours as 'Fri<BR>' + cast(Day(getDate()) as varchar(2)) from todayshours

I get an error trying to do concatenation as part of the alais. Any ideas?

Luke
lgraunke AT 4invie.comWhats <BR>

Is this being done in SQL Server?|||Ideally I would like the column name/header to show something like 'Fri<BR>20'. The '<BR>' is just some web formating that is automatically incorporated.|||This should float your boat...

USE Northwind
GO

DECLARE @.cmd varchar(8000)

SELECT @.cmd = 'SELECT Quantity AS ['
+ CASE DATEPART(WeekDay,GetDate())
WHEN 1 THEN 'SUNDAY'
WHEN 2 THEN 'MONDAY'
WHEN 3 THEN 'TUESDAY'
WHEN 4 THEN 'WEDNESDAY'
WHEN 5 THEN 'THURSDAY'
WHEN 6 THEN 'FRIDAY'
WHEN 7 THEN 'SATURDAY'
END
+ '<BR>'
+ cast(Day(getDate()) as varchar(2))
+ '] FROM [Order Details]'

SELECT @.cmd

EXEC(@.cmd)|||Thanks, that was exactly what I was looking for.

column concatenation

Hi

Assume a query :

select bankno,regionno,officeno from afo

The above query displays three columns (grids) in query analyzer. Is there in any way that
we can concatenate the above three columns and display as only one column in the grid with a hyphen as separator.

ThanxTry:

select
convert(varchar,bankno) +
convert(varchar,regionno)+
convert(varchar,officeno)
from afo|||Or Even

select
convert(varchar,bankno) + '-' +
convert(varchar,regionno) + '-' +
convert(varchar,officeno)
from afo

convert(varchar(50),'I''m only any good with the easy ones' + ' - ' + 'lol')

GW|||kir441, be aware that when using convert(varchar,<table attribute>) you are converting to a varchar(30) datatype. While this is good enough for Integers it could cause truncation for other datatypes.

Nothing like the obveous GWilliy, but why didn't you use CAST?|||Paul & Gwilliy

I used convert(varchar,bankno) + '-' + convert(varchar,acctno) ...etc
for my query. It worked, as you suggested we can use CAST also.
Thanks for your answers.|||U Mean

CAST('I''m only any good with the easy ones' + ' - ' + 'lol' AS VarChar(50))
(notice the not so obvious in both methods ?)

Dunno really Paul -

Guess I'm just used to using Convert and it feels a little more english to me - not forgetting the ability to use the style option.

Is there a good reason why I should start using CAST instead ?

GW|||My philosophy is use what works and is most maintainable in your shop.