Concatenating multiple rows from same column

Question

I have a line of SQL that results in 2 rows of 1 cell coming back:

SELECT ATTR_VAL FROM [NDC_ATTR] where item_id = 185836 and field_id IN (144,225) 

Results:

1 H400

2 TESTTEXT

I am trying to concatenate them together so they look like this 'TESTTEXT[H400]':

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?

Answer 1

select newColumn = stuff(
  (
  select '' +[ATTR_VAL]
    from [NDC_ATTR]
    where item_id = 185836
      and field_id in (144, 225)
    order by 1 desc
  for xml path (''), type).value('.','varchar(max)')
  ,1,0,'')

or for more items:

select 
    t.item_id
  , newColumn = stuff(
  (
  select '' +[ATTR_VAL]
    from [NDC_ATTR] as i
    where i.item_id = t.item_id
      and i.field_id in (144, 225)
    order by 1 desc
  for xml path (''), type).value('.','varchar(max)')
  ,1,0,'')
  from [NDC_ATTR] as t
  where t.item_id in (...)
  group by t.item_id

optionally: add a delimeter: (notice the 0 changed to the length of the delimiter ';' for the 3rd parameter of stuff)

select 
    t.item_id
  , newColumn = stuff(
  (
  select ';' +[ATTR_VAL]
    from [NDC_ATTR] as i
    where i.item_id = t.item_id
      and i.field_id in (144, 225)
    order by 1 desc
  for xml path (''), type).value('.','varchar(max)')
  ,1,1,'')
  from [NDC_ATTR] as t
  where t.item_id in (...)
  group by t.item_id