Solved

SQL Select that concatenates Aliases

Posted on 2015-02-12
5
70 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 34

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 34

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 50

Expert Comment

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

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

738 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