sql server - How to use stuff on entire columns of the table: -


i executed below query , executed :-

select table2id, stuff((select char(13) + table1name  table1 table1id=table2.table2id xml path (''), type ).value('.', 'varchar(max)') , 1, 1, '')  table2 table2id=117 group id; 

but when using count(*) , in below query :-

select table2id, stuff((select char(13) + count(*)  table1 table1id=table2.table2id xml path (''), type ).value('.', 'varchar(max)') , 1, 1, '')  table2 table2id=117 group id; 

i getting below error:

 msg 245, level 16, state 1, line 19 conversion failed when converting varchar value ' ' data type int. 

now how can stuff columns in table1 ? !

i want result below:-

table2id  |  table1name  |  table1id  | table1color ------------------------------------------------------   117     |  jon, jack   |   117,117  | blue,red 

( adding sample data table1 , table2) :-

table1:

table1id | table1name | table1color | table1city | table1animal |...(i     have 25 columns !) --------------------------------------------------------------    117      | jon        | blue        | city1      | animal1         117      | jack       | red         | city2      | animal2        

table2:

table2id | table2uniqueid ------------------------- 117      | asbn6383hhh3j3837 118      | kf9s8sujfu6df5d7j  

this has nothing stuff.
reason error this:
count(*) returns int. char(13) char. whenever try int + char sql server try implicitly convert char int. naturally, char(13) cannot converted int.
need to explicitly convert count(*) varchar:

select table2id, stuff(      (      select char(13) + cast(count(*) varchar(10))       table1       table1id=table2.table2id       xml path (''), type).value('.', 'varchar(max)'), 1, 1, '') table2  table2id=117  group id; 

Comments

Popular posts from this blog

networking - Vagrant-provisioned VirtualBox VM is not reachable from Ubuntu host -

c# - ASP.NET Core - There is already an object named 'AspNetRoles' in the database -

ruby on rails - ArgumentError: Missing host to link to! Please provide the :host parameter, set default_url_options[:host], or set :only_path to true -