Combining repeating row cells

Question

I have a line of SQL that results in 2 repeating rows that could be any count:

SELECT ATTR_VAL 
FROM [NDC_ATTR] 
WHERE field_id IN (144,225)

Results:

1 H400
2 TESTTEXT
3 A200
4 TxtTst
....

I am trying to concatenate them together so they look like this:

1 TESTTEXT[H400]
2 TxTTst[A200]
...

I here is my current attempt which was mostly just trying to get them in the same field.

select 
    concat ([NDC_ATTR], ' ', [NDC_ATTR]) as newColumn
where 
    item_id = 185836
    and field_id in (144, 225);

However, I am getting a bunch of errors saying that the column names are wrong.

I think this is because both cells come from the same column.

What am I doing wrong and how do I fix it?

Edit: returning data:

You are missing a `FROM` clause. And you will need a `JOIN` to identify which two records you are concatenating. — DVT, Mar 01 '17 at 17:10
Can you expand your example to show what logic you are trying to use? -- also, you still can't `concat()` tables, and your last query needs a `from` clause. — SqlZim, Mar 01 '17 at 17:10
I have fixed the query. Also, I show a pic of how the data comes in. I want each 2 rows repeating combined into 1. — David Tunnell, Mar 01 '17 at 17:15
I think [this is what you are after](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) though you'll need another Key Column, like field_id — S3S, Mar 01 '17 at 17:24

score 1 · Accepted Answer · answered Mar 01 '17 at 17:36

Without any table schema posted, I am just guessing here:

select 
    a.item_id
  , attr_vals = concat(a.attr_val,' ',quotename(b.attr_val))
from ndc_attr as a
  inner join ndc_attr as b
    on a.item_id = b.item_id
      and a.field_id = 144
      and b.field_id = 225
where a.item_id = 185836

Serg · Answer 2 · 2017-03-01T17:34:22.537

0

Group pivoted values by item_id

select concat (
        max(case field_id when 144 then [ATTR_VAL] end),
        ' ',
        max(case field_id when 255 then [ATTR_VAL] end)
        ) as newColumn
from NDC_ATTR
where field_id in (144, 225)
    and item_id = 185836
group by item_id;

edited Mar 01 '17 at 17:34

answered Mar 01 '17 at 17:16

Serg

22,285
5
21
48

[NDC_ATTR] is not a column name, its the table name. – David Tunnell Mar 01 '17 at 17:20
Corrected according to your first SELECT – Serg Mar 01 '17 at 17:35

Combining repeating row cells

2 Answers2