Solved

SQL Select that concatenates Aliases

Posted on 2015-02-12
5
64 Views
Last Modified: 2015-02-18
I need to re-combine a field that has been broken out into aliases.
My table name is "Salary" and the field name is "a_id"
The following query grabs the highest value, breaks it apart into 2 aliases.

 The values in the field "a_id" look something like this:
 143939-0100000373-1
 143939-0100000373-2
 53866-02009-1
 53866-02009-2
 53866-02009-3


This query breaks apart "a_id" into two aliases
SELECT LEFT(a_id, LEN(a_id) - CHARINDEX('-',REVERSE(a_id))), MAX(RIGHT(a_id, CHARINDEX('-',REVERSE(a_id))-1))
FROM Salary
GROUP BY LEFT(a_id, LEN(a_id) - CHARINDEX('-',REVERSE(a_id)))

Open in new window



The resultset looks like this:
(No column name)	               (No column name)
143939-0100000373               2
 53866-02009	                3

Open in new window



I need it to look like this:
(No column name)	               (No column name)           (No column name) 
143939-0100000373               2                                         143939-0100000373-2
 53866-02009	                3                                          53866-02009-3

Open in new window

0
Comment
Question by:swaggrK
5 Comments
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40606125
SELECT LEFT(a_id, LEN(a_id) - CHARINDEX('-',REVERSE(a_id))) As Col1, MAX(RIGHT(a_id, CHARINDEX('-',REVERSE(a_id))-1)) As Col2, a_id As Col3
FROM #T1
GROUP BY a_id, LEFT(a_id, LEN(a_id) - CHARINDEX('-',REVERSE(a_id)))

Open in new window



Col1                  Col2              Col3  
143939-0100000373	1	143939-0100000373-1
53866-02009	        1	53866-02009-1
53866-02009	        2	53866-02009-2
53866-02009          	3	53866-02009-3

Open in new window

0
 
LVL 18

Expert Comment

by:Simon
ID: 40606130
select L ,M ,L+'-'+M from (
SELECT LEFT(a_id, LEN(a_id) - CHARINDEX('-',REVERSE(a_id))) as L, MAX(RIGHT(a_id, CHARINDEX('-',REVERSE(a_id))-1)) as M
FROM @Salary
GROUP BY LEFT(a_id, LEN(a_id) - CHARINDEX('-',REVERSE(a_id))) 
) dtbl

Open in new window

Returns two rows of results:
L      M      (No column name)
143939-0100000373      2      143939-0100000373-2
53866-02009      3      53866-02009-3
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40606134
try:
SELECT  LEFT(a_id, LEN(a_id) - CHARINDEX('-',REVERSE(a_id))) As Col1, MAX(RIGHT(a_id, CHARINDEX('-',REVERSE(a_id))-1)) As Col2, LEFT(a_id, LEN(a_id) - CHARINDEX('-',REVERSE(a_id))) + '-' + MAX(RIGHT(a_id, CHARINDEX('-',REVERSE(a_id))-1))
FROM #T1
GROUP BY LEFT(a_id, LEN(a_id) - CHARINDEX('-',REVERSE(a_id)))

Open in new window


Col1                              Col2                     Col3
143939-0100000373      1      143939-0100000373-1
53866-02009                    3      53866-02009-3
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40606991
CROSS APPLY is very useful for providing aliases that can be reused in a query.

Here I have aliases the expensive REVERSE() calculation into an alias "pos" to simplify the overall syntax. After the grouping using aliases via a derived table allows for the concatenation.
SELECT L , R , (L + '-' + R) AS ID
FROM (
            SELECT
                  LEFT(a_id, LEN(a_id) - CA.pos) AS L
                , MAX(RIGHT(a_id, CA.pos - 1))   AS R
            FROM Salary
                  CROSS APPLY (
                               SELECT CHARINDEX('-', REVERSE(a_id))
                  ) AS CA (POS)
            GROUP BY
                  LEFT(a_id, LEN(a_id) - CA.pos)
      ) AS DERIVED
;

Open in new window


|                 L | R |                  ID |
|-------------------|---|---------------------|
| 143939-0100000373 | 2 | 143939-0100000373-2 |
|       53866-02009 | 3 |       53866-02009-3 |

Open in new window


see it, touch it: http://sqlfiddle.com/#!3/df6e6/1
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40613703
swaggrK, do you still need help with this question?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

813 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now