December 18, 2008 at 1:26 pm
I know, it sounds easy, right. Well, the trouble I have is that I have one
ID column, int type, always has 6 digits, so that is easy. But
the other two columns that I wanted to included are varchar type, and
they are title and company name; therefore, the values in these fields are varied. So, when I combined them, each field does not seperate with a straight line because of this. I want to pad empty string but it does not work. Please see my query below.
table
ID title company
120000 Manager ABCDEF
120001 Accounting Supervisor GHIJKLMNOPQRSTVW
result wanted:
company Title ID
ABCDEF | Manager | 120000
GHIJKLMNOPQRSTVW | Accounting Supervisor | 120001
my query that I am stuck at:
I know, it sounds easy, right. Well, what I have is one
ID column, int type, always has 6 digits, so that is easy. But
the other two columns that I wanted to included are varchar type, and
they are title and company name; therefore, the values in these fields are varied.
So, when I combined them, each field does not seperate with a straight line
because of this. I want to pad empty string but it does not work.
Please see my query below. Is there anyway to do this that I am not aware of. any help would be greatly appreciated.
table
ID title company
120000 Manager ABCDEF
120001 Accounting Supervisor GHIJKLMNOPQRSTVW
result wanted -- the column line up straight
ABCDEF | Manager | 120000
GHIJKLMNOPQRSTVW | Accounting Supervisor | 120001
my query that I am stuck at:
create table dummycompany
(
id int,
title varchar(100),
companyname varchar(200)
)
insert into dummycompany
values(120000, 'Manager', 'ABCDEF')
insert into dummycompany
values(120011, 'Accounting Supervisor', 'GHIJKLMNOPQRSTVW')
select * from dummycompany
declare @maxTitle as int
declare @maxName as int
set @maxTitle = (select max(len(title)) from dummycompany with(nolock))
set @maxName = (select max(len(companyname)) from dummycompany with(nolock))
select(RIGHT(REPLICATE('', @maxTitle) + LTRIM(RTRIM(Title)), @maxTitle)) + ' | ' +
(RIGHT(REPLICATE('', @maxName) + LTRIM(RTRIM(companyname)), @maxname)) + ' | ' +
cast(id as varchar(15))
fromdummycompany
December 18, 2008 at 2:09 pm
what exactly are you trying to accomplish?
Usually this kind of formatting is done on the presentation layer. IE Report or Webpage.
Are you exporting this data for someone?
Why are you dynamically fixing the length of your query.
Why don't you fix it to the length of the field.
select convert(char(200), companyname) + '|' +
convert(char(100), Title) + '|' + ltrim(str(ID))
from dummycompany
December 18, 2008 at 2:25 pm
I'm trying to display them in the list box in asp.net
as one search that would show multiple columns.
I tried to do it at the presentation layer, but html
ignore blank space so it does not seem to work.
I'm still researching differnt way to do it in the front end,
but I was hoping to do it from sql. Thanks for responding.
December 18, 2008 at 6:04 pm
You might want to use
SELECT Title + SPACE(@maxtitle - LEN(Title)) + ' | '
FROM dummycompany
changing to use the SPACE function for Title and company name.
Try above using SSMS and have the query results display as text. It will show the alinement you want. To copy and paste here, well this is HTML and it raises cain with blanks not being displayed, ditto in SSMS when displaying in grid mode, select Query - Results to - Results to text or use a control T. Selecting output to a file results in a Crystal report format which also clombers blanks ... Now what or how you can handle it in asp.net -- beyond me.
December 18, 2008 at 9:23 pm
Hi bitbucket, thanks for replying.
I see what you meant after I ran your query
in text display in ssms. 'll keep play around with this
to see if I can use it.
i did not want to have to do this to be honest with you.
in asp.net, there is a tool for display multiple columns,
that is not the problem. The problem here I have is the
users wants a kind of smart searching that they would
type only a first few words then the list would match
those words for all the columns related to those records
the users are looking for. Currently, in the version I am using and
with ajax tool, it can be done for listbox and dropdownbox,
but only display for one column. that is why I am trying this in sql.
anyway, I am still trying different kinds of ways to do this, so will see.
thanks again.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply