SQL MANAGEMENT STUDIO R2

Question

Am using SQL server 2008 R2 I have created One table which having Multiple Rows I would like to Concatenate into Single row Cap ABphyllin 100 1 1 0 1 DAYS Tab Acemiz 200 1 0 1 2 DAYS Tab Acetuff MR 1 1 1 6 DAYS Oint Acivir 1 1 1 7 DAYS Like above am getting 4 Rows if would like to Print the data it takes 4 lines to print but I would like to print it in single line Please help me..

Nitin Sontakke · Answer

I have no idea of the business benefit of the solution but here is the code: declare @sampleTable table ( [drug] [ varchar ](100) not null [morning] [ integer ] not null [afternoon] [ integer ] not null [night] [ integer ] not null [duration] [ varchar ](100) not null ) insert into @sampleTable values ( 'Cap ABphyllin 100'  1 1 0 '1 DAYS' ) ( 'Tab Acemiz 200'  1 0 1 '2 DAYS' ) ( 'Tab Acetuff MR'  1 1 1 '6 DAYS' ) ( 'Oint Acivir'  1 1 1 '7 DAYS' ) declare @singleRow varchar ( max ) = '' select @singleRow += coalesce ( nullif (@singleRow '' ) ';' ) + [drug] + ' ' + ltrim(str([morning])) + ' ' + ltrim(str([afternoon])) + ' ' + ltrim(str([night])) + [duration] from @sampleTable select @singleRow