Solved

SQL Select that concatenates Aliases

Posted on 2015-02-12
5
61 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 46

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

911 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

21 Experts available now in Live!

Get 1:1 Help Now