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
Post a Comment