none
Add Letters to Rows

    Question

  • I am creating a Report that uses SQL Statement to select Rows and I need to add alphabets like A-->Row one, B-->Row two etc. Below is my query

    SELECT     table.text1, table2.text2 
    FROM         table1 RIGHT OUTER JOIN
                          table2 ON table1.tableID = table2.tableID LEFT O 
    WHERE     (table1.mycolumn = 0) AND (table2.mycolumn2 = 0)  
     


    Ebenezer

    Thursday, October 17, 2013 1:44 AM

Answers

  • If you want a round trip of the alphabets, you may try the below:

    select CHAR((ROW_NUMBER() OVER (ORDER BY table1.text1 asc)%27) +64)+
    	'.' + table1.text1 + ' - ' + table1.text2 
    	FROM  table1 


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by denkyira Thursday, October 17, 2013 12:08 PM
    Thursday, October 17, 2013 5:24 AM

All replies

  • Can you provide sample data and output?

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, October 17, 2013 2:21 AM
  • Basically I want to concatenate the two columns and add alphabet so the results will look like this

    A  Column 1+ Column 2 

    A.372 - some text from column 2

    B  Column 1 + Column 2

    B.2185 - some text from column 2


    Ebenezer


    • Edited by denkyira Thursday, October 17, 2013 2:59 AM
    Thursday, October 17, 2013 2:58 AM
  • You can try this.assuming you have less than 26 rows.

    select CHAR(row_number() over(order by (select 0))+64)
    ,table1.text1
    ,table2.text2 
    FROM  table1 
    RIGHT OUTER JOIN table2 ON table1.tableID = table2.tableID 
    WHERE   (table1.mycolumn = 0) AND (table2.mycolumn2 = 0)  
    


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

    Thursday, October 17, 2013 3:09 AM
  • SELECT    CHAR((ROW_NUMBER() OVER (ORDER BY table.text1) +64)) + '.' + table.text1 + ' - ' + table2.text2 
    FROM         table1 RIGHT OUTER JOIN
                          table2 ON table1.tableID = table2.tableID LEFT O 
    WHERE     (table1.mycolumn = 0) AND (table2.mycolumn2 = 0)  

    Thursday, October 17, 2013 3:11 AM
  • Thanks. But where do I add the alphabets like A-->Row one, B-->Row two etc to achieve a results like this:

    A  Column 1+ Column 2 

    A.372 - some text from column 2


    Ebenezer

    Thursday, October 17, 2013 3:15 AM
  • Thanks. But where do I add the alphabets like A-->Row one, B-->Row two etc to achieve a results like this:

    A  Column 1+ Column 2 

    A.372 - some text from column 2


    Ebenezer

    Thursday, October 17, 2013 3:15 AM
  • Hi,

    the expression for alphabets is CHAR(row_number() over(order by (select 0))+64)

    add this in your select statement along with the other columns


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

    Thursday, October 17, 2013 3:33 AM
  • If you want a round trip of the alphabets, you may try the below:

    select CHAR((ROW_NUMBER() OVER (ORDER BY table1.text1 asc)%27) +64)+
    	'.' + table1.text1 + ' - ' + table1.text2 
    	FROM  table1 


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    • Marked as answer by denkyira Thursday, October 17, 2013 12:08 PM
    Thursday, October 17, 2013 5:24 AM