SQL Combing the top 2 field values into 1 value

Question

I have a very simple query that returns the Notes field. Since there can be multiple notes, I only want the top 2. No problem. However, I'm going to be using the sql within another query. I really don't want 2 lines in my results. I would like to combine the results into 1 field value so I only have 1 result line in the results. Is this possible?

For example, I currently get the following:

12345     1001     500.00   "Note 1"
12345     1001     500.00   "Note 2"

What I would like to see is this:

12345     1001     500.00   "Note 1 AND Note 2"

Following is the sql:

select top 2 rcai.field_value
from rnt_agrs ra 
inner join rnt_agr_inv_notes rain on ra.rnt_agr_nbr=rain.rea_rnt_agr_nbr
inner join RNT_CUST_ADDNL_INFO rcai on rain.rea_rnt_agr_nbr=rcai.rea_rnt_agr_nbr and rain.bac_acc_id=rcai.bac_acct_id
where ra.rnt_agr_nbr=128260511

Thanks for your help. I appreciate this forum for help with these issues.....

Your SQL has one column returned and yet your sample data seems to have four columns. — Gordon Linoff, May 22 '15 at 16:36
Check this post out I believe it's the answer to your question. [1]: http://stackoverflow.com/questions/22919259/multiple-rows-into-a-single-row-and-combine-column-sql — CoderKen, May 22 '15 at 16:41
@Andrew......I don't understand your reply. I'm relatively new to sql and Teradata. Thanks...... — Shaves, May 26 '15 at 15:29

score 1 · Accepted Answer · answered May 22 '15 at 17:39

1

Get the next row's value and filter all but the first row:

select ..., rcai.field_value || ' AND ' 
   min(rcai.field_value)  -- next row's value (same as LEAD in Standard SQL)
   over (partition by ra.rnt_agr_nbr
         order by rcai.field_value
         rows between 1 following and 1 following) as next_field_value   
from rnt_agrs ra 
inner join rnt_agr_inv_notes rain on ra.rnt_agr_nbr=rain.rea_rnt_agr_nbr
inner join RNT_CUST_ADDNL_INFO rcai on rain.rea_rnt_agr_nbr=rcai.rea_rnt_agr_nbr and rain.bac_acc_id=rcai.bac_acct_id
where ra.rnt_agr_nbr=128260511
qualify
   row_number()   -- only the first row 
   over (partition by ra.rnt_agr_nbr
         order by rcai.field_value) = 1

If there might be only a single row you need to add a COALESCE(min...,'') to get rid of the NULL.

Both OLAP functions specify the same PARTITION and ORDER, so this is a single working step.

answered May 22 '15 at 17:39

dnoeth

59,503
4
39
56

@dnoeth........Thanks.....that does exactly what I was looking for. When I run that code separately, I get one field with the 2 notes. What I need to do is to insert that code in the following code so it just adds a column to each row with the 2 notes. Can you help with that? Here is the code that I needed it added to : – Shaves May 26 '15 at 19:53
@dnoeth......I've tried to copy the code for you to see but it is too long. When I add the rcai.field_value.............I'm getting 2 lines for each record because rcai.field value has 2 different values. I think I'm close but not close enough yet.... – Shaves May 26 '15 at 20:20
@dnoeth...........thank you very much. I worked with it some more and I now have 1 line for each record with the 2 notes combined. thanks again for all of your help – Shaves May 26 '15 at 20:30
@dnoeth............Thanks again. I knew about that and just forgot to do it yesterday. I just accepted your answer. Thanks for the help...... – Shaves May 27 '15 at 16:14

score 0 · Answer 2 · answered May 22 '15 at 17:50

0

select *,(SELECT top 2 rcai.field_value + ' AND ' AS [text()]
                            FROM          RNT_CUST_ADDNL_INFO rcai 
                            WHERE      rcai.rea_rnt_agr_nbr = rain.rea_rnt_agr_nbr
                                       AND rcai.bac_acct_id=rain.bac_acc_id
                                      FOR XML PATH('')) AS Notes
from 
rnt_agrs ra inner join rnt_agr_inv_notes rain 
on ra.rnt_agr_nbr=rain.rea_rnt_agr_nbr

I had something like this, where there was a 1 to many, and I wanted a semicolon delimited set of values in a single column with the main record.

answered May 22 '15 at 17:50

NINtender

109
6

@NINtender...........I've tried your suggestion but I'm getting a syntax error message. It's looking for something like a name or Unicode delimiter between AS and [. thanks for your help – Shaves May 26 '15 at 15:28
Wow, I never looked back, Sorry Shaves. Maybe instead of ' AND' try N' AND '. The query is finicky, and is difficult to solve in my head and not in a query analyzer. :) – NINtender Sep 04 '15 at 18:40

Michael Petito · Answer 3 · 2015-05-22T18:04:59.577

You could use PIVOT to transform the two note rows into two note columns based on row number, then concatenate them. Here's an example:

SELECT pvt.[1] + ' and ' + pvt.[2]
FROM
(   --the selection of your table data, including a row-number column
    SELECT Msg, ROW_NUMBER() OVER(ORDER BY Id)
    --sample data shown here, but this would be your real table
    FROM (VALUES(1, 'Note 1'), (2, 'Note 2'), (3, 'Note 3')) Note(Id, Msg)
) Data (Msg, Row)
PIVOT (MAX(Msg) FOR Row IN ([1], [2])) pvt

Note that MAX is used for the aggregate in the PIVOT since an aggregate is required, but since ROW_NUMBER is unique, you're only aggregating a single value.

This could also be easily extended to the first N rows - just include the row numbers you want in the pivot and combine them as desired in the select statement.

SQL Combing the top 2 field values into 1 value

3 Answers3