0

When writing a self-join in tSQL I can avoid duplicate column names thus:

SELECT FirstEvent.Title AS FirstTitle, SecondEvent.Title AS FirstTitle
FROM ContiguatedEvents AS FirstEvent
LEFT OUTER JOIN ContiguatedEvents AS SecondEvent
ON  FirstEvent.logID = SecondEvent.logID

Suppose I want to select all the columns from the self-join, for example into a view. How do I then differentiate the column names without writing each one out in the join statement. I.e. is there anything I can write like this (ish)

SELECT FirstEvent.* AS ???, SecondEvent.* AS ???
FROM ContiguatedEvents AS FirstEvent
LEFT OUTER JOIN ContiguatedEvents AS SecondEvent
ON  FirstEvent.logID = SecondEvent.logID
dumbledad
  • 16,305
  • 23
  • 120
  • 273
  • 1
    "for example into a view" - is, like many other places, somewhere you should be listing each column explicitly. `SELECT *` does *odd* things in a view definition if the table is subsequently altered. – Damien_The_Unbeliever Apr 26 '13 at 06:53
  • 1
    Never saw s.t. like this. Putting the names one by one is also better because your select does not get bigger when you add columns to the tables later. I personally never use * anywhere. I allways print out the column names with a little stored proc i wrote and copy and paste the ones i need into the sql. Then you don't need to type it by hand. – Hasan Tuncay Apr 26 '13 at 06:55
  • You're both correct - I've fallen foul of that recently when it took some time for me to realise why the column I'd added to the table was not in the view. That said, this is for some 'quick and dirty' analysis of a table with many columns that I'd rather use `*` for. – dumbledad Apr 26 '13 at 06:56
  • Either way, no, there's no way of automatically introducing unique column aliases. – Damien_The_Unbeliever Apr 26 '13 at 06:57
  • @HasanTuncay - if you're using management studio, you can drag the `Columns` folder from object explorer into a query window and it gives you all of the column names. – Damien_The_Unbeliever Apr 26 '13 at 06:58
  • Thanks Damien. Can you write the "no way" and the dragging column names hint in as an answer and I'll mark it as such? – dumbledad Apr 26 '13 at 06:59
  • @Damien: Yes I know, i'm a bit old fashioned :-) – Hasan Tuncay Apr 26 '13 at 11:24

1 Answers1

1

There's no way to automatically introduce aliases for multiple columns, you just have to do it by hand.

One handy hint for quickly getting all of the column names into your query (in management studio) is to drag the Columns folder from the Object Explorer into a query window. It gives you all of the column names.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448