SQL Select that concatenates Aliases

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

swaggrKAsked:
Who is Participating?
 
PortletPaulfreelancerCommented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
SimonCommented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
swaggrK, do you still need help with this question?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.