1

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?

Improve this question
3
  • Your table doesn't have those columns, so I'm just not following the relationship between your query and what you want to do. Commented Feb 28, 2017 at 21:25
  • 2
    are you trying to concat tables or... where is your from? Commented Feb 28, 2017 at 21:25
  • look at: stackoverflow.com/a/545672/7187145 Commented Feb 28, 2017 at 21:26

1 Answer 1

Reset to default
3 
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
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks!, how would I modify the order? I can't get H400 to be second. How about TESTTEXT[H400]?
you can add order by 1 desc before for xml path

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.