Combining multiple columns into one column.

Dear all,
I am having a problem on how to merge 3 columns into one column. I have a
columns named [LastName], [FirstName] and [MiddleName], I want those columns
to be as one and name it as [Name]. How will I do that? I already tried the
trick like what I did in Access but it does'nt work in SQL. Please help me
with this. Any suggestions will be much appreciated.
Thanks in advance,
Jir
Try:
alter table dbo.MyTable
add
MyComputedCOlumn as LastName + ', ' + FirstName + ' ' + MiddleName
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Jir" <Jir@.discussions.microsoft.com> wrote in message
news:F64E0FD3-3E0E-4B9A-AB49-0CFFE29B5D8F@.microsoft.com...
Dear all,
I am having a problem on how to merge 3 columns into one column. I have a
columns named [LastName], [FirstName] and [MiddleName], I want those columns
to be as one and name it as [Name]. How will I do that? I already tried the
trick like what I did in Access but it does'nt work in SQL. Please help me
with this. Any suggestions will be much appreciated.
Thanks in advance,
Jir

Combining databases

Dear Developres,

Actually I'm on the half way of making a portal and I get some problem I need your kindly helps.

at first I use the membership feature of ASP.net 2.0 to have login and all so by default it has generate an ASPNETDB.MDF file which its is (Microsoft SQL Server Database File (SqlClient)) and also I have two more databases one for file managemnet and one for Calander and Contacts but I need all to be one so whenevr one user can login it can show his own file in his page but now everyone can see all,Can anybody guide me should it all be in one database and if yes how can I connect all since one is generated by default by Visual studio2005.Should I use a Microsoft SQL Server (SqlClient)?

Thanks in advance.

Hi,

I think the problem is something related to membership expanding. Actually you can add your datatables into ASPNETDB.mdf and in each table , create a primary key (such as UserId or something like that ) which is the foreign key of the aspnet_users. And then, create a view so when the user login, he can get all his files.

Thanks.

|||

Hello Michael,

Thanks for you reply ,actually I have done the same thing but in one file that has stored procedure got problem ad it says:

Cannot insert the value NULL into column 'DateCreated', table ASPNETDB.MDF.dbo.Files'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Can you please tell me whats the problem?

|||

Hi,

The DateCreated field in your database doesn't allow nulls so when you insert the record, don't forget to insert the datacreated value, you can use Date.Now to get the current time.

Thanks.

|||

Actually the error is for something else I dont know why because by the time that it was outside the ASPNET.MDF it works well but as I move inside the new databse it has this error.

Thanks

Bye

|||

Hi,

Try to check if allow-null is checked and generally that is caused by the restraint in your database.

Thanks.

Combining Cross-Tab and charts

Dear all,
I'm a noob to SQL Reporting Services but have plenty of experience with
other MS applications (including excel and access). I was wondering whether
it's possible to combine a cross-tab and a chart within a single report. If
so, how do you do this? I believe the power of a report (often) exists out of
numbers combined with a graphical display.
thanks in advance,
mischaOn Apr 26, 10:22 am, mischa <mis...@.discussions.microsoft.com> wrote:
> Dear all,
> I'm a noob to SQL Reporting Services but have plenty of experience with
> other MS applications (including excel and access). I was wondering whether
> it's possible to combine a cross-tab and a chart within a single report. If
> so, how do you do this? I believe the power of a report (often) exists out of
> numbers combined with a graphical display.
> thanks in advance,
> mischa
If I understand you correctly, you should be able to use a matrix
control and a chart control.
Regards,
Enrique Martinez
Sr. Software Consultant

combine table data fields from two records into a record

Dear helper,

I have the question of T-SQL.

I have a table original is:

and want to use Sql to make it becomes:

Concrete_Grade Mix_Code RM_ID RM_Name RM_Value UnitType_ID RMTypeType_Name RMType_Name
10P/20 10P kfdn_100 KFDN-100 2.24 kg Set Retarding Admixture
10P/20 10P kfdn_100 KFDN-100 2.24 kg Water-reducing Admixture
10P/20 10P kfdn_100 KFDN-100 1.95 lit Set Retarding Admixture
10P/20 10P kfdn_100 KFDN-100 1.95 lit Water-reducing Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.93 kg Set Retarding Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.93 kg Water-reducing Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.76 lit Set Retarding Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.76 lit Water-reducing Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.43 kg Set Retarding Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.43 kg Water-reducing Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.21 lit Set Retarding Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.21 lit Water-reducing Admixture
10P/20 10PC kfdn_100 KFDN-100 2.33 kg Set Retarding Admixture
10P/20 10PC kfdn_100 KFDN-100 2.33 kg Water-reducing Admixture
10P/20 10PC kfdn_100 KFDN-100 2.03 lit Set Retarding Admixture
10P/20 10PC kfdn_100 KFDN-100 2.03 lit Water-reducing Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.59 kg Set Retarding Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.59 kg Water-reducing Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.25 lit Set Retarding Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.25 lit Water-reducing Admixture

It is better to make it becomes a view for table joining.

Concrete_Grade Mix_Code RM_ID RM_Name RM_Value UnitType_ID RMTypeType_Name RMType_Name
10P/20 10P kfdn_100 KFDN-100 2.24 kg Set Retarding, Water-reducing Admixture
10P/20 10P kfdn_100 KFDN-100 1.95 lit Set Retarding, Water-reducing Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.93 kg Set Retarding, Water-reducing Admixture
10P/20 10PAA daratard_17d Daratard 17D 1.76 lit Set Retarding, Water-reducing Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.43 kg Set Retarding, Water-reducing Admixture
10P/20 10PAB daratard_17d Daratard 17D 2.21 lit Set Retarding, Water-reducing Admixture
10P/20 10PC kfdn_100 KFDN-100 2.33 kg Set Retarding, Water-reducing Admixture
10P/20 10PC kfdn_100 KFDN-100 2.03 lit Set Retarding, Water-reducing Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.59 kg Set Retarding, Water-reducing Admixture
10S/20 10Sa kfdn_100 KFDN-100 2.25 lit Set Retarding, Water-reducing Admixture

Regards,

Man Pak Hong, Dave

try this..

SELECT a.Concrete_Grade
, a.Mix_Code
, a.RM_ID
, a.RM_Name
, a.RM_Value
, a.UnitType_ID
, a.RMTypeType_Name
, b.RMType_Name
FROM YourTable a INNER JOIN
YourTable b ON a.Concrete_Grade = b.Concrete_Grade
AND a.Mix_Code = b.Mix_Code
AND a.RM_ID = b.RM_ID
AND a.RM_Name = b.RM_Name
AND a.RM_Value = b.RM_Value
AND a.UnitType_ID = b.UnitType_ID|||

So your point is creating view which holds the values [RMTypeType_Name]='Water-reducing', lets say vw_MyData_WaterReducing. Later you want to join this view with outher tables on your query.

One way it is good if you use INDEXED VIEW. You have to create a index on this new view. It will increase the performance well.

But if you try to use with out index (only the filtered query), it may decrease the performance. You may unknowingly use Self join on your query...

To know better abotu indexed view visit here ... http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

Combine many rows to one row?

Dear friends,

I have a problem that need some help from expert.Is there any way I could combine many rows into a row in Access using Visual Basic. I want to change the below table from TABLE A to TABLE B

TABLE A SampleCode Test Name Result ID Name Sex 9300105Peripheral Blood Film....
a few poikilocytes are present.S7585512EDHANDAPANI MAHESHM9300105Peripheral Blood Film....
No blast cells seen.S7585512EDHANDAPANI MAHESHM9300105Peripheral Blood Film....
microcytes, elongated cells andS7585512EDHANDAPANI MAHESHM9300105Peripheral Blood Film....
hypochromic but normocytic: . SomeS7585512EDHANDAPANI MAHESHM9300105Peripheral Blood Film....
Majority of rbcs appear slightlyS7585512EDHANDAPANI MAHESHM

Output:

TABLE B SampleCode Test Name Result ID Name Sex 9300105Peripheral Blood Film....
a few poikilocytes are present, No blast cells seen.microcytes, elongated cells and hypochromic but normocytic. Some Majority of rbcs appear slightlyS7585512EDHANDAPANI MAHESHM

Your help would be greatly appreciated

Thanks a lot,

Chicky

---

Chicky

You might want to give this thread from yesterday a look:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1335992&SiteID=1

Columns Order

Dear All
Can I Alter tables to change the columns orders, Or Add Column in a
specified order? How?
AhmedAhmed
I don't think that it make sense. It does not matter what is a column's
order though you can SELECT col1,col2 Or SELECT col,col1
If you persist, you can add the column by EM in order that you want.
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
news:%232KTvJ6sFHA.1568@.TK2MSFTNGP10.phx.gbl...
> Dear All
> Can I Alter tables to change the columns orders, Or Add Column in a
> specified order? How?
> Ahmed
>|||No and no. Only way is to re-create the table (which is what Enterprise Mana
ger does when you do it
graphically).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
news:%232KTvJ6sFHA.1568@.TK2MSFTNGP10.phx.gbl...
> Dear All
> Can I Alter tables to change the columns orders, Or Add Column in a specif
ied order? How?
> Ahmed
>|||> Can I Alter tables to change the columns orders, Or Add Column in a
> specified order? How?
More importantly, WHY?
http://www.aspfaq.com/2528|||The only reason I can think of for changing the order of columns is that you
want to use SELECT *. Using SELECT * is a common bad practice usually
committed by the lazy or the incompetent. I only use it in query analyzer
for debugging. SELECT * is one of the things I look for in profile traces
when I'm analyzing a system. If I find it in a trace, then I know that I
will have to spend a lot more time determining whether or not any change I
make will break existing code, and consequently, I will have to charge the
customer a lot more money at every stage of the project.
"Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
news:#2KTvJ6sFHA.1568@.TK2MSFTNGP10.phx.gbl...
> Dear All
> Can I Alter tables to change the columns orders, Or Add Column in a
> specified order? How?
> Ahmed
>|||Dear All
Thanks for replay
I know it's not a live or death feature, I need to arrange the columns
according to some rules, like if it is a primary key or doesn't allow null,
to make the table structure in client database same as the development
database, or anything else. I'm just asking how to do it.
Anyway, if we can do it through the enterprise manager I think it is not
difficult to make it through the T_SQL.
Thanks
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:Om6jiC7sFHA.524@.TK2MSFTNGP12.phx.gbl...
> The only reason I can think of for changing the order of columns is that
> you
> want to use SELECT *. Using SELECT * is a common bad practice usually
> committed by the lazy or the incompetent. I only use it in query analyzer
> for debugging. SELECT * is one of the things I look for in profile traces
> when I'm analyzing a system. If I find it in a trace, then I know that I
> will have to spend a lot more time determining whether or not any change I
> make will break existing code, and consequently, I will have to charge the
> customer a lot more money at every stage of the project.
> "Ahmed Hashish" <a_hashish@.hotmail.com> wrote in message
> news:#2KTvJ6sFHA.1568@.TK2MSFTNGP10.phx.gbl...
>|||Ahmed Hashish (a_hashish@.hotmail.com) writes:
> Thanks for replay
> I know it's not a live or death feature, I need to arrange the columns
> according to some rules, like if it is a primary key or doesn't allow
> null, to make the table structure in client database same as the
> development database, or anything else. I'm just asking how to do it.
> Anyway, if we can do it through the enterprise manager I think it is not
> difficult to make it through the T_SQL.
You are completely right, and Brian is wrong. There are several good reasons
why one want to have columns in a certain order, and SELECT * is not one
of them. But have PK columns first in the table, and in the correct order
is one. And wanting have logically related columns close to each other is
another.
Anyway, there is no syntax for this, not even in SQL 2005. What Enterprise
Manager does is to create a new table, and then move over data to that
table, and recreate foreign keys etc. In a script, I should add, that has
several serious flaws. While these are fairly easy to address, an ALTER
TABLE command would be easier to use.
On MSDN Product Feedback Centre you can submit bugs and suggestions for
SQL 2005, and then other people can vote on these submissions. One
suggestion that was submitted earlier this year, was precisely about this
matter. By now, it has assembled 21 votes, which I think makes it the
most voted-on suggestion for SQL 2005. So you are not the first one to
ask for this.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> You are completely right, and Brian is wrong. There are several good
reasons
> why one want to have columns in a certain order, and SELECT * is not one
> of them. But have PK columns first in the table, and in the correct order
> is one. And wanting have logically related columns close to each other is
> another.
The location of columns in a table is immaterial either from a performance
standpoint, or for any other reason. A primary key constraint always
creates an index, which means that the key values are copied into the a
B-tree structure, so there is no need for the columns to be adjacent.
Variable length columns are always separated from fixed length columns at
the physical layer, and depending on the option settings when the table is
created, nullable fixed-length character columns are treated in the same way
as variable-length character columns. In addition, SQL Server reads an
extent at a time, so there is no performance penalty for having intervening
columns within a row since the entire row is in memory anyway. Therefore,
it is pointless to try to force the columns to be in a specific order,
unless you're going to use SELECT *.
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96CE72C7ED62Yazorman@.127.0.0.1...
> Ahmed Hashish (a_hashish@.hotmail.com) writes:
columns
> You are completely right, and Brian is wrong. There are several good
reasons
> why one want to have columns in a certain order, and SELECT * is not one
> of them. But have PK columns first in the table, and in the correct order
> is one. And wanting have logically related columns close to each other is
> another.
> Anyway, there is no syntax for this, not even in SQL 2005. What Enterprise
> Manager does is to create a new table, and then move over data to that
> table, and recreate foreign keys etc. In a script, I should add, that has
> several serious flaws. While these are fairly easy to address, an ALTER
> TABLE command would be easier to use.
> On MSDN Product Feedback Centre you can submit bugs and suggestions for
> SQL 2005, and then other people can vote on these submissions. One
> suggestion that was submitted earlier this year, was precisely about this
> matter. By now, it has assembled 21 votes, which I think makes it the
> most voted-on suggestion for SQL 2005. So you are not the first one to
> ask for this.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp
>|||> I also like to start the table definition with the columns of the
> Primary Key, preferably in the order of the Primary Key definition.
Sure, when you're first designing the table, I think we all tend to create
the column structure in a logical order.
Whether it is a logical thing to do after the table has been created, I'm
not so sure about that. Why would we be adding the primary key to the table
as an afterthought?
More often than not, this request seems to stem from issues like "I want all
my numeric columns together" or "I want such and such column at the 'end' of
the table."|||Brian Selzer (brian@.selzer-software.com) writes:
> The location of columns in a table is immaterial either from a
> performance standpoint, or for any other reason. A primary key
> constraint always creates an index, which means that the key values are
> copied into the a B-tree structure, so there is no need for the columns
> to be adjacent. Variable length columns are always separated from fixed
> length columns at the physical layer, and depending on the option
> settings when the table is created, nullable fixed-length character
> columns are treated in the same way as variable-length character
> columns. In addition, SQL Server reads an extent at a time, so there is
> no performance penalty for having intervening columns within a row since
> the entire row is in memory anyway. Therefore, it is pointless to try
> to force the columns to be in a specific order, unless you're going to
> use SELECT *.
If only computers were reading the table, you would be right. Almost, more
a little later.
But the table is also used by people. If you are going to develop something
in a database, it may be perfectly OK to you if you look at the table
definition and the columns appear in the order they were added to the table.
Personally, I prefer to see column in a logical order, for instance PK
column first, auditing columns at the end, and related column adjancent
to each other.
This also matters when you do a SELECT * from Query Analyzer for debugging
reasons. Which I do a lot. (SELECT * in code is another matter.)
There is also a technical reason. Say that your column order is accidental,
and now you are to bulk load out from a table one server to the same table
on another server. If you know about it, you write a format file (jolly
good fun for a 100-column table!). If you don't know about it, you may
be informed of errors. Then again you may not, because the colunms that
were in different order were of the same data types.
Column order is not about performance. It's about usability.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Column in contains clause?

Dear all,
Another question: I would like to get an overview of how often a number
of words, stored in a table KO, occur in a full text indexed column on
another table. I can do this seperately:
select * from names
id | name
--+--
1 | bush
2 | kerry
select count(*) from texts where contains(text, 'bush')
2,123
select count(*) from texts where contains(text, 'dean')
1,326
[numbers entiry fictional]
But since I have a large number of such names, I would like to just
join the count per name to the names table like so:
select name, count(*) from
names n, texts t
where contains(text, n.name)
group by name
but this returns an 'incorrect syntax near n'.
Why doesn't the above work? Is what I am trying to do possible using
the contains function? Is there another way to achieve this goal?
Thanks!
Wouter
It doesn't work because the Contains operator is expecting a single value
instead of a column - which is what you are passing.
I think what you need to do is something like this you might want to write a
cursor or perhaps a function that will return a table variable to accomplish
this.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"wouter" <wouter@.2at.nl> wrote in message
news:1108297074.753022.57670@.l41g2000cwc.googlegro ups.com...
> Dear all,
> Another question: I would like to get an overview of how often a number
> of words, stored in a table KO, occur in a full text indexed column on
> another table. I can do this seperately:
> select * from names
> id | name
> --+--
> 1 | bush
> 2 | kerry
> select count(*) from texts where contains(text, 'bush')
> 2,123
> select count(*) from texts where contains(text, 'dean')
> 1,326
> [numbers entiry fictional]
> But since I have a large number of such names, I would like to just
> join the count per name to the names table like so:
> select name, count(*) from
> names n, texts t
> where contains(text, n.name)
> group by name
> but this returns an 'incorrect syntax near n'.
> Why doesn't the above work? Is what I am trying to do possible using
> the contains function? Is there another way to achieve this goal?
> Thanks!
> Wouter
>

collecting values into a variable in a loop

Dear All,

Im trying to collect values from a query into a single variable within a loop, like so:

WHILE condition is true
BEGIN

SET @.intLoop = @.intLoop + 1

@.myString = @.myString + , + (SELECT companyName FROM @.tblTheseComp WHERE id = @.intLoop

END

For some reason though the @.myString does not collect up the values, but will equal NULL at the end of the loop.

If however I simple do

WHILE condition is true
BEGIN

SET @.intLoop = @.intLoop + 1

@.myString = (SELECT companyName FROM @.tblTheseComp WHERE id = @.intLoop
END

Then I get the last value from the query as expected.

Can anyone explain why this might be?

Thanks in advance!Hi

Your variable is null initially -
NULL +'Something' = NULL

HTH|||Also - you don't need a loop:

DECLARE @.myString AS VarChar(1000)

SELECT @.myString = ''

SELECT @.myString = @.myString + ', ' + companyName
FROM @.tblTheseComp

SELECT @.myString = SUBSTRING(@.myString, 3, LEN(@.myString)-2)

SELECT @.myString|||This code will handle if any of the companyName is null.

DECLARE @.myString AS VarChar(1000)
SELECT @.myString = ''

SELECT @.myString = @.myString + coalesce(companyName+',','')
FROM @.tblTheseComp

SELECT @.myString = SUBSTRING(@.myString, 1, LEN(@.myString)-1)

SELECT @.myString|||Nice one thanks a lot! A final obsticle and I'm there!

As I need to send a list of action items to my users, I need to add a carriage return at the end of each line, any idea how I would do that, the mail is sent as a text file so I assume I'll need the CTRL + M combination or in ASCII:

Char: CR, Oct:15, Dec:13, Hex:d

Any ideas anyone?|||Hmm wasnt that tricky I needed CHAR(13) + CHAR(10) cool, thanks for all the help again!|||add char(13) with ur string.
eg:

PRINT 'First line.' + CHAR(13) + CHAR(10) + 'Second line.'|||Thanks yes I noticed I had to include a string to add CHAR(13) + CHAR(10) to. Cheers!

collecting data from different mdbs

Dear netters,

I'm trying to set up a db architecture for a community school environment with the following restrictions:
- each school uses a simple application, storing data in an an .mdb file
- school data must asynchronously update a central server (SQL server) that stores data from all the school files and publishes them on the www
- no permanent interner connection is guaranteed (schools work offline and update the server periodically)

I'm checking MS solutions for the above requirements. Is "replication" the right place to start? Should I look into some other capability of Ms products (wharehousing?)?

I would appreciate some guidelines on how to organise my search for solutions.

Thanx in advance,

Vas.Mail the req's and I'd be glad to consult...cheap rates!

No replication is not the place to start..

A entity relationship model is...

Actually a process model diagram is...|||come on, we're talking about school system here! they can't afford to pay teachers the rate that they deserve! ...and what are your rates? :)

but you're right, - process model combined with the erd will give us an idea here on how to tackle this.

Collation+Greek

Dear all,
I am using an application which loads data from an SQL Database 2000 (no
service pack installed).
Some fields of the application use drop-down lists in order to enter a
value. The problem is that when I try to create the drop-down list and fill
it with values (in my case, words written in Greek and words written with
latin characters). The latin written words are sorted alphabetically but the
words written in Greek are not sorted.
Is this a problem of collation? I have installed SQL server with the
default settings (typical installation)
Thank you
It sounds like a collation problem. I've had the same problem but that
was few years ago on 6.5, and it was caused because of the use of wrong
page code. I didn't see this behavior in later versions, so I might be
wrong here, but my bet would be that the cause of the problem is using
wrong collation.
Adi

Collation+Greek

Dear all,
I am using an application which loads data from an SQL Database 2000 (no
service pack installed).
Some fields of the application use drop-down lists in order to enter a
value. The problem is that when I try to create the drop-down list and fill
it with values (in my case, words written in Greek and words written with
latin characters). The latin written words are sorted alphabetically but the
words written in Greek are not sorted.
Is this a problem of collation? I have installed SQL server with the
default settings (typical installation)
Thank youIt sounds like a collation problem. I've had the same problem but that
was few years ago on 6.5, and it was caused because of the use of wrong
page code. I didn't see this behavior in later versions, so I might be
wrong here, but my bet would be that the cause of the problem is using
wrong collation.
Adi