CODEWITHSUNDEEP
sqlsql-server
David Tunnell
David Tunnell

Reputation: 7542

Combining repeating row cells

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:

enter image description here

Upvotes: 1

Views: 43

Answers (2)

SqlZim
SqlZim

Reputation: 38043

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

Upvotes: 1

Serg
Serg

Reputation: 22811

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;

Upvotes: 0

Related Questions