• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 79
  • Last Modified:

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

0
swaggrK
Asked:
swaggrK
1 Solution
 
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
 
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
 
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.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now