?
Solved

join results as one column

Posted on 2012-08-28
7
Medium Priority
?
359 Views
Last Modified: 2012-08-28
how do i return a result set back as one column?

select Distinct((LEFT(RIGHT(GLTran.Sub, 20), 3))) AS SubAcct , Account.Descr
from GLTran
INNER JOIN Account ON GLTran.Acct = Account.Acct
where GLTran.Acct >= '40000' AND GLTran.Acct < '50000' order by SubAcct asc

SubAccount     Descr
000                    (Gain)/Loss Extin of Debt


want 1 col instead of 2
000 - Gain/Loss Extin of Debt
0
Comment
Question by:fwstealer
  • 4
  • 3
7 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38342206
CAST the account number (assuming it's numeric) as a varchar, then you can concatenate the varchar number and description into one column

select CAST(Distinct((LEFT(RIGHT(GLTran.Sub, 20), 3))) AS varchar(max)) + ' - ' + Account.Descr as account
from GLTran
INNER JOIN Account ON GLTran.Acct = Account.Acct
where GLTran.Acct >= '40000' AND GLTran.Acct < '50000' order by SubAcct asc
0
 

Author Comment

by:fwstealer
ID: 38342395
got an error:

Incorrect syntax near the keyword 'as'.

CAST(Distinct((LEFT(RIGHT(GLTran.Sub, 20), 3))) as varchar(max))
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38342415
If this value is always three characters than replace varchar(max) with char(3).

This may also work..

select Distinct(CAST(LEFT(RIGHT(GLTran.Sub, 20), 3) AS varchar(max))) + ' - ' + Account.Descr as account
from GLTran
INNER JOIN Account ON GLTran.Acct = Account.Acct
where GLTran.Acct >= '40000' AND GLTran.Acct < '50000' order by SubAcct asc

Since I don't have access to your data source there's no way I can validate if Distinct((LEFT(RIGHT(GLTran.Sub, 20), 3))) is correct, so you're on your own on that one.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

Author Comment

by:fwstealer
ID: 38342436
minor change:

select Distinct(CAST(LEFT(RIGHT(GLTran.Sub, 20), 3) AS varchar(max))) + ' - ' + Account.Descr as account
from GLTran
INNER JOIN Account ON GLTran.Acct = Account.Acct
where GLTran.Acct >= '40000' AND GLTran.Acct < '50000' order by GLTran.Sub asc

error:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 2000 total points
ID: 38342455
>ORDER BY items must appear in the select list if SELECT DISTINCT is specified

change the ORDER BY to ORDER BY 1 and see if that works.

If not, you'll have to replicate the whole SELECT formula like this (maybe this, or remove the DISTINCT, not sure on that one)

select Distinct(CAST(LEFT(RIGHT(GLTran.Sub, 20), 3) AS varchar(max))) + ' - ' + Account.Descr as account
from GLTran
INNER JOIN Account ON GLTran.Acct = Account.Acct
where GLTran.Acct >= '40000' AND GLTran.Acct < '50000'
order by Distinct(CAST(LEFT(RIGHT(GLTran.Sub, 20), 3) AS varchar(max))) + ' - ' + Account.Descr
0
 

Author Comment

by:fwstealer
ID: 38342471
order by 1 worked!
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38342485
Thanks for the grade.  Good luck with your project.  -Jim

>order by 1 worked!
If you're SELECTing a lot of calculations, you can ORDER BY the position of the column (1, 2, 3, ...) instead of having to re-calculate.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
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 video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?

864 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