new line
-
select gender,amt,age,[address],class , Case when gender= '' then 'gender,'when gender is null then 'gender,' Else '' End + Case when amt = '' then 'amt,'when amt is null then 'amt,' Else '' End + Case when age = '' then 'age,'when age is null then 'age,' Else '' End + case when [address] = ''then 'address'when [address] is null then 'address,' else '' End + Case when class = '' then 'class,'when class is null then 'class,' Else '' End As [Error Remarks] from mytable The above query is used to check if any of this row is blank or null display the result in a column name "error remarks" . Now the result i m getting is : gender, amt, age,address,class all this result is displayed in a single line in a single cell. now i want the result to be displayed in different line: like this: gender, amt, age, [address], class all this is single cell with the above query. i had tried char(13) and 10 . and the value is coming in new line but in sql server textmode and not in grid mode . i want data to be in grid mode.
-
select gender,amt,age,[address],class , Case when gender= '' then 'gender,'when gender is null then 'gender,' Else '' End + Case when amt = '' then 'amt,'when amt is null then 'amt,' Else '' End + Case when age = '' then 'age,'when age is null then 'age,' Else '' End + case when [address] = ''then 'address'when [address] is null then 'address,' else '' End + Case when class = '' then 'class,'when class is null then 'class,' Else '' End As [Error Remarks] from mytable The above query is used to check if any of this row is blank or null display the result in a column name "error remarks" . Now the result i m getting is : gender, amt, age,address,class all this result is displayed in a single line in a single cell. now i want the result to be displayed in different line: like this: gender, amt, age, [address], class all this is single cell with the above query. i had tried char(13) and 10 . and the value is coming in new line but in sql server textmode and not in grid mode . i want data to be in grid mode.
scottichrosaviakosmos wrote:
i want the result to be displayed in different line
So do this in the UI where display operations belong! Why are doing such a silly thing as formatting your data for the query results grid, you should be the only person looking at SQL Server results.
Never underestimate the power of human stupidity RAH
-
select gender,amt,age,[address],class , Case when gender= '' then 'gender,'when gender is null then 'gender,' Else '' End + Case when amt = '' then 'amt,'when amt is null then 'amt,' Else '' End + Case when age = '' then 'age,'when age is null then 'age,' Else '' End + case when [address] = ''then 'address'when [address] is null then 'address,' else '' End + Case when class = '' then 'class,'when class is null then 'class,' Else '' End As [Error Remarks] from mytable The above query is used to check if any of this row is blank or null display the result in a column name "error remarks" . Now the result i m getting is : gender, amt, age,address,class all this result is displayed in a single line in a single cell. now i want the result to be displayed in different line: like this: gender, amt, age, [address], class all this is single cell with the above query. i had tried char(13) and 10 . and the value is coming in new line but in sql server textmode and not in grid mode . i want data to be in grid mode.
How did you use the CHAR(13) and CHAR(10)?
-
select gender,amt,age,[address],class , Case when gender= '' then 'gender,'when gender is null then 'gender,' Else '' End + Case when amt = '' then 'amt,'when amt is null then 'amt,' Else '' End + Case when age = '' then 'age,'when age is null then 'age,' Else '' End + case when [address] = ''then 'address'when [address] is null then 'address,' else '' End + Case when class = '' then 'class,'when class is null then 'class,' Else '' End As [Error Remarks] from mytable The above query is used to check if any of this row is blank or null display the result in a column name "error remarks" . Now the result i m getting is : gender, amt, age,address,class all this result is displayed in a single line in a single cell. now i want the result to be displayed in different line: like this: gender, amt, age, [address], class all this is single cell with the above query. i had tried char(13) and 10 . and the value is coming in new line but in sql server textmode and not in grid mode . i want data to be in grid mode.
With SQL Enterprise Manager Query Results in grid view you can't display new lines - I came across the same only this week. I think they get converted to spaces deliberately prior to being dumped into the grid so everything stays on the one line. Microsoft made the call - most developers, most of the time, want to see most of their data without stretching the row-height every time they run the query. But trust me, those 13-10s/CR-LFs are working. As someone else said - you are the only person who will see this grid. If you want to whack this query into a .NET winforms grid view, it is possible to get the multi-lines to show there by setting a couple of properties (I could look it up for you, but I won't). And of course, make that query a view, link to the view from Microsoft Access as a linked table and you will see multi-lines. I will now run for cover.