SQL select syntax with embedded logic

Posted on 2007-07-30
Medium Priority
Last Modified: 2010-03-19
i need to select from two tables. both tables can be joined on id (t1.id  = t2.id)

              if t2.columnA = 1 then (
                                                       1 as abc-column-name1
                                                        t2.columnX1 as abc-column1
               if t2.columnA = 2 then (
                                                         2 as abc-column-name2
                                                         t2.columnX2 as abc-column2
from table1 t1, table2 t2
where t1.id = t2.id
Question by:jgordin
LVL 93

Accepted Solution

Patrick Matthews earned 2000 total points
ID: 19593816
SELECT t2.ColumnA AS [abc-column-mame1],
    (CASE WHEN t2.ColumnA = 1 THEN t2.columnX1 ELSE t2.columnX2 END) AS  [abc-column-mame2],
    t1.ColumnA, t2.ColumnB
from table1 t1, table2 t2
where t1.id = t2.id
LVL 58

Expert Comment

ID: 19594010
In your example, you attempt to create columns with variable names. SQL isn't meant for that and only allows it in cross-tabs (this doesn't fit your scenario, however).

Instead, manipulate only your data, for example as Patrick showed above, into fixed column names. You can then rename or hide/show these columns further down in the interface. But not directly in the query.


Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question