combining two tables with a full-text search

I had a table that was terribly in need of normalisation that I have now
split into two tables. The table contains three "similar" fields that I
were previously indexed using a full-text query. I now need to do the query
twice as subqueries, UNION the results, then order by the calculated rank.
Problem is - the rank appears to be independent between the two tables, to
the results are coming up with one query always being higher than the other.
Is there any way to pre-select or cap the rank value, or another way to
search these two tables so they're more "combined"?
Thanks in advance,
Duncan
Probably not as the rank is generated on a per table basis.
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
"Dunc" <dunc@.ntpcl.f9.co.uk> wrote in message
news:Ouh0hapaFHA.2496@.TK2MSFTNGP14.phx.gbl...
> I had a table that was terribly in need of normalisation that I have now
> split into two tables. The table contains three "similar" fields that I
> were previously indexed using a full-text query. I now need to do the
query
> twice as subqueries, UNION the results, then order by the calculated rank.
> Problem is - the rank appears to be independent between the two tables, to
> the results are coming up with one query always being higher than the
other.
> Is there any way to pre-select or cap the rank value, or another way to
> search these two tables so they're more "combined"?
> Thanks in advance,
> Duncan
>
|||One solution, that wouldn't be the best but would work, would be to
create a third table containing all the fields you wish to index with
the primary key associated with it, then index that table instead.

Combining two tables to make a third

Need help combining two tables into a third with corresponding fields
of both tables
table 1
time 12 mike work
time 13 john sleep
times 24 George jump
table 2
23 sam run
There is a table3 which has all the columns of the two tables but i
cannot seem to come around to combine them
time 12 mike work 23 sam run
time 13 john sleep 23 sam run
times 24 George jump 23 sam run
Your help is appreciated have been on thisI guess a cross join will work since I don't see any keys
select * into table3 from table1 cross joins table2
select * from table3
Denis the SQL Menace
http://sqlservercode.blogspot.com/
mngong@.gmail.com wrote:
> Need help combining two tables into a third with corresponding fields
> of both tables
> table 1
> time 12 mike work
> time 13 john sleep
> times 24 George jump
> table 2
> 23 sam run
> There is a table3 which has all the columns of the two tables but i
> cannot seem to come around to combine them
> time 12 mike work 23 sam run
> time 13 john sleep 23 sam run
> times 24 George jump 23 sam run
> Your help is appreciated have been on this|||I really don't know what you are asking for, but I suppose I can
guess.
SELECT Table1.*, Table2.*
FROM Table1 CROSS JOIN Table2
Roy Harvey
Beacon Falls, CT
On 27 Jul 2006 06:24:48 -0700, mngong@.gmail.com wrote:
>Need help combining two tables into a third with corresponding fields
>of both tables
>table 1
>time 12 mike work
>time 13 john sleep
>times 24 George jump
>table 2
>23 sam run
>There is a table3 which has all the columns of the two tables but i
>cannot seem to come around to combine them
>time 12 mike work 23 sam run
>time 13 john sleep 23 sam run
>times 24 George jump 23 sam run
>Your help is appreciated have been on this|||This is a multi-part message in MIME format.
--=_NextPart_000_0C6D_01C6B148.4585B4F0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Based on this limited information:
INSERT INTO Table3
SELECT *
FROM Table1
CROSS JOIN Table2
If you must control the field order, you may need to list the columns =from each table instead of using [*],
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
<mngong@.gmail.com> wrote in message =news:1154006687.920219.20920@.75g2000cwc.googlegroups.com...
> Need help combining two tables into a third with corresponding fields
> of both tables
> table 1
> time 12 mike work
> time 13 john sleep
> times 24 George jump
> table 2
> 23 sam run
> There is a table3 which has all the columns of the two tables but i
> cannot seem to come around to combine them
> time 12 mike work 23 sam run
> time 13 john sleep 23 sam run
> times 24 George jump 23 sam run
> > Your help is appreciated have been on this
>
--=_NextPart_000_0C6D_01C6B148.4585B4F0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Based on this limited =information:
INSERT INTO Table3
SELECT =*
FROM =Table1
=CROSS JOIN Table2
If you must control the field order, =you may need to list the columns from each table instead of using [*],
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
wrote in message news:1154006687.920219.20920@.75g2000cwc.googlegroups.com...> =Need help combining two tables into a third with corresponding fields> of =both tables> table 1> time 12 mike work> =time 13 john sleep> times 24 George jump> table 2> 23 sam run> There is a table3 which has all the columns of the two =tables but i> cannot seem to come around to combine them> time =12 mike work 23 sam run> time 13 john sleep 23 =sam run> times 24 George jump 23 sam run> > Your help =is appreciated have been on this>

---

--=_NextPart_000_0C6D_01C6B148.4585B4F0--

Combining two fields into one

I have a small problem.
In my query I am calling a field of status and a field of statusyearcount. I
need to get these into one cell in a table, called statusyearcount.
ie. Status = C
StatusYearCount = 10
Need to show C10 in the table.
I have tried =Fields!Status.Value + Fields!StatusYearCount.Value but I keep
getting an "incorrect format string".
Im gathering it because one is an int and the other varchar. Can somebody
please point me in the right direction here.
Thanks muchly.You can cast the Field.
=Fields!Status.Value + Int(Fields!StatusYearCount.Value)
--
| Thread-Topic: Combining two fields into one
| thread-index: AcWo+8ArbHZHrGD+TI2fppOvcbIgrg==| X-WBNR-Posting-Host: 202.175.143.143
| From: "=?Utf-8?B?TmF0IEpvaG5zb24=?="
<NatJohnson@.discussions.microsoft.com>
| Subject: Combining two fields into one
| Date: Wed, 24 Aug 2005 15:32:46 -0700
| Lines: 18
| Message-ID: <3113308E-337F-41C0-BD15-F3A43DA03A0C@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.reportingsvcs:51089
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| I have a small problem.
|
| In my query I am calling a field of status and a field of
statusyearcount. I
| need to get these into one cell in a table, called statusyearcount.
|
| ie. Status = C
| StatusYearCount = 10
|
| Need to show C10 in the table.
|
| I have tried =Fields!Status.Value + Fields!StatusYearCount.Value but I
keep
| getting an "incorrect format string".
|
| Im gathering it because one is an int and the other varchar. Can
somebody
| please point me in the right direction here.
|
| Thanks muchly.
|
||||This will do it:
=Fields!Status.Value & CStr(Fields!StatusYearCount.Value)
CStr converts integer to string, allowing succesful concatenation.
GeoSynch
"Nat Johnson" <NatJohnson@.discussions.microsoft.com> wrote in message
news:3113308E-337F-41C0-BD15-F3A43DA03A0C@.microsoft.com...
>I have a small problem.
> In my query I am calling a field of status and a field of statusyearcount. I
> need to get these into one cell in a table, called statusyearcount.
> ie. Status = C
> StatusYearCount = 10
> Need to show C10 in the table.
> I have tried =Fields!Status.Value + Fields!StatusYearCount.Value but I keep
> getting an "incorrect format string".
> Im gathering it because one is an int and the other varchar. Can somebody
> please point me in the right direction here.
> Thanks muchly.
>

Combining two fields

Hey everyone, I am very new to SQL Reporting, I usually work with Crystal. So I think this question is very simple. I have a field and in the expression box it says

=Sum(Fields!EstRev.Value)

Now I have another field that I want to combine in the above box, no fancy calcs or anything I just want it to appear in the same area.

=Sum(Fields!OrderCount.Value)

Basically I do not want to add the two fields together arthritically I just want them numbers to appear on the bottom of each other.

I have tried an AND Operator and that didnt work. Please help, very simple questions. It has to be very simple to just combine these two in to one field on the report?

You can do something like this:

=Sum(Fields!EstRev.Value)&" "&Sum(Fields!OrderCount.Value) <- this will show something like "23 432"

or

=Sum(Fields!EstRev.Value)&Chr(13)&Chr(10)&Sum(Fields!OrderCount.Value) <- this will show something like

23

432

Maciej

|||

Maciej

I normally do this Cstr(sum(fields!EstRev.value)) - converting number to string values

Hammer

Combining or Concating seperate fields for date?

I have two tables in a SQL db.
Each has 3 separate fields used to store a date info:

lastservicemonth tinyint1
lastserviceday tinyint1
lastserviceyear smallint2
(and these fields can be nulls)

I want to compare the date info in Table A vs. Table B and find the latest date between the two.

I know I somehow need to combine the 3 separate fields in each table to form one date field. Then I can compare the dates.
But ths far I have been unsuccessful.

Any help would be greatly appreciated!To get the latest row, use:SELECT TOP 1 *
FROM [Table A]
ORDER BY lastserviceyear, lastservicemonth, lastserviceday-PatP|||Thanks Pat - I can can see how my question was unclear. Hope this clarifies.

Say Table A and Table B both contain the same person records for when they last came in the hospital. But the tables contain different dates.

Example:
Table A - ID #123, John Doe , 11-1-2007
Table B - ID#123, John Doe, 12-3-2007

I want to update the date fields in Table A with the data in Table B, but only if the date in Table B is more recent than the date in Table A.

So, I need to compare the dates for each person in Table A to the same person in Table and determine which visit date is more recent.

Hope this makes more sense.|||Why are two tables storing such similar information?

If you give us the real problem, it might also be easier to decipher than "table A and table B..."|||While I understand the desire to "simplify" a problem for posting purposes, the process usually infuriates me... All too often critical pieces of information get "simplified" out of the example that gets posted!

Can you post at least the DDL for the tables (in other words the CREATE TABLE statements needed to recreate them), and whatever attempt you've made so far to do what you want? This would help us a lot in determining what you need.

-PatP|||I appreciate your feedback. I can see I need to clarify.
Table A and B are in different dbs connected to different apps.
The apps communicate with each other imperfectly, so the dates get out of synch.

I imported the id# and the day, month and year column from System B, Table B to system A.
Now I want to update Table A with that data.

Here's a select statement where I attempt to identify discrepancies in the two data sets. If I can correct this , I can do an update statement. The statement below adds the 3 date fields and arrives a a number rather than a date. Do the fields need to be converted from smallint and tinyint ?

SELECT
(p.lastserviceYEAR +'-'+ p.lastserviceMONTH +'-'+ p.lastserviceday) as pdate,
(e.lastserviceYEAR +'-'+ e.lastserviceMONTH +'-'+ e.lastserviceday) as edate
From patient p Inner Join empi e On (p.ID = e.ID)
where edate > pdate|||I'd use something like:Cast(1000 * lastserviceyear + 100 * lastservicemonth + lastserviceday AS INT)to get integer values that you can safely compare and sort... They aren't pretty to print, but they work well for comparisons and sorting.

-PatP|||I was going to suggest the use of DateAdd(), but I think that will be far more efficient.|||last time it was me dropping a zero, pat, this time it's you ;)

Cast(10000 * ...|||Excellent. That will do the trick.
Thanks to all for your help and patience!

Combining numeric fields

Hello,
I use the following line in my sql view to bring 2 numeric fields together,
as one field (I'm using this to populate a listbox in vb.net).
This all works great, but I need to have the value 0 show up as 0.0. Right
now, it shows up as 0.
CAST(dbo.TDT_ROAD_SECTION.NUM_START AS varchar(10)) + ' ' +
CAST(dbo.TDT_ROAD_SECTION.NUM_END AS varchar(10))
Any suggestions?
TIA!
amberOn Tue, 20 Sep 2005 15:30:04 -0700, amber wrote:

>Hello,
>I use the following line in my sql view to bring 2 numeric fields together,
>as one field (I'm using this to populate a listbox in vb.net).
>This all works great, but I need to have the value 0 show up as 0.0. Right
>now, it shows up as 0.
>CAST(dbo.TDT_ROAD_SECTION.NUM_START AS varchar(10)) + ' ' +
>CAST(dbo.TDT_ROAD_SECTION.NUM_END AS varchar(10))
>Any suggestions?
Hi amber,
Use the STR function instead of CAST. See the documentation in Books
Online for details on usage.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||If the original data type of those two columns is integer (int, bigint,
smallint or tinyint) then cast it as decimal before casting it to varchar.
Look into data types in Books Online for more details, or provide DDL and
some sample data and please explain what exactly your goal is.
ML|||Thank you!
That fixed it!
Amber

Combining Fields to string

I have a function that takes a field with values separated by commas within the field and splits them to multiple rows.

Example:
Field - Interior
Value - abc,def,efg,ghi

Output:
ID Item
1 abc
2 def
3 efg
etc

This is working great thanks to help that I received on here.

Now I am combining multiple fields to a string.
Example:
Field1: abc, def
Field2: ghi, jkl

using

SELECT (Field1 + ',' + Field2) From ....

This is working great unless there is a field that has a NULL value. Then I get a NULL result.

Is there an easy way to only put the fields with value into my string and leave out the NULL fields? Some have one NULL field, some have multiple. I just need to get the string to work and get only the fields that have values.

Any suggestions are always appreciated.It has been resolved on another post.

THANKS!!|||

Quote:

Originally Posted by rpeacock

It has been resolved on another post.

THANKS!!

I am having the same problem - can you tell me what other post solved the issue?

Thanks in advance

RIP

Combining Fields in a stored procedure

my question is this...I have to fields I want to retreive..One is a nvarchar and the other is an integer...I want to return them in this format
(interger field + '/' + nvarcharfield) as combinedfield
problem is i get errors when I try to get this value
I just need the info I know you cant add theses two together...

Example of output needed...

31/OfficeVisit

my sp

ALTER procedure EncounterCodes_NET
(
@.ClinicID int
)
as
select
CombinedField=(EnCodeID + EnCodeDesc)
from
Clinic_Encodes
Where
ClinicID=@.ClinicID
Order by Sortorderreplace the EncodeID with

Cast(EnCodeID as varchar)

combining fields (sort of)

Hi,
Hope this is neither too obvious or laughably impossible, else I'll
just have to be embarrased.
Is there any way to do this:

SELECT field1 if it is not NULL, else SELECT field2..??

The way this dbase is set up, I'm just about suicidal.
THANKS for any help!Use the COALESCE function (or NVL if using Oracle):

COALESCE(field1,field2)|||Originally posted by andrewst
Use the COALESCE function (or NVL if using Oracle):

COALESCE(field1,field2)

NVL did it! THANKS SO MUCH! I'd looked at that earlier but
the sql book I have only shows literals as being valid 'convert-to'
values. Shoulda just tried it. Thanks again!!!!!sqlsql

Combining fields

Not concatenation, more... err.. I don't know what you'd call it.

SELECT
DISTINCT [C01241 Opened].[Col004] AS OpenerEmail,
[C01241 External Data].[DMCEMAIL] AS ExternalDataEmail,
[C01241 Internal Data].[Col15] AS InternalDataEmail
FROM [C01241 Opened]
LEFT JOIN [C01241 External Data] ON [C01241 External Data].[DMCEMAIL] = [C01241 Opened].[Col004]
LEFT JOIN [C01241 Internal Data] ON [C01241 Internal Data].[Col15] = [C01241 Opened].[Col004]

(Apologies for the table/col names, this is all very temporary)

So I've got a table, [C01241 Opened], which details all the people who registered. Those people might turn up in table [C01241 External Data], or they might turn up in [C01241 Internal Data]. Yes, they will always be in one or the other, and no, they won't appear in both.

At the moment, I just pull in the email address. But the client, of course, wants a whole bunch of fields that occur in the 'original data' tables: Firstname, Lastname, Company, Favourite color, etc.

What I want to know is if - and how - I can make the query output one column for each of the required fields, but populate it from either of the two 'original data' tables, depending on where their email address pops up in.

Does that make sense?look into union if I understand your question corretly this should work|||I might not be understanding UNION correctly, but if I do, then I should have added that the two 'original data' tables have absolutely no similarities in structure. Does this make a difference?

Edit:
(sorry, that was a stupid thing to say and I've just realised why :rolleyes: Thanks :) )|||Use the Coalesce function:
SELECT DISTINCT
[C01241 Opened].[Col004] AS OpenerEmail,
Coalesce([C01241 External Data].[DMCEMAIL], [C01241 Internal Data].[Col15]) AS DataEmail
FROM [C01241 Opened]
LEFT JOIN [C01241 External Data] ON [C01241 External Data].[DMCEMAIL] = [C01241 Opened].[Col004]
LEFT JOIN [C01241 Internal Data] ON [C01241 Internal Data].[Col15] = [C01241 Opened].[Col004]

Combining fields

Hello,
Within a view I've created, I have combined 2 fields to make 1.
dbo.TABLE1.STR_STRATUM + N' ' + dbo.TABLE2.STR_LAYER AS StratumLayer
This is for display (to populate a listbox in .NET).
The problem is, if there is nothing in the STR_LAYER field, the whole field
is blank.
Is it possible to display Stratum always, and Layer when it's available?
Thanks!
AmberUse functions ISNULL or COALESCE.
Example:
coalesce(dbo.TABLE1.STR_STRATUM + N' ', N'') +
coalesce(dbo.TABLE2.STR_LAYER, '') AS StratumLayer
AMB
"amber" wrote:

> Hello,
> Within a view I've created, I have combined 2 fields to make 1.
> dbo.TABLE1.STR_STRATUM + N' ' + dbo.TABLE2.STR_LAYER AS StratumLayer
> This is for display (to populate a listbox in .NET).
> The problem is, if there is nothing in the STR_LAYER field, the whole fiel
d
> is blank.
> Is it possible to display Stratum always, and Layer when it's available?
> Thanks!
> Amber
>|||SELECT dbo.TABLE1.STR_STRATUM + ISNULL( N' ' + dbo.TABLE2.STR_LAYER AS
StratumLayer, '')
Jacco Schalkwijk
SQL Server MVP
"amber" <amber@.discussions.microsoft.com> wrote in message
news:AF278105-D1AF-44DA-AD22-13E762A0690A@.microsoft.com...
> Hello,
> Within a view I've created, I have combined 2 fields to make 1.
> dbo.TABLE1.STR_STRATUM + N' ' + dbo.TABLE2.STR_LAYER AS StratumLayer
> This is for display (to populate a listbox in .NET).
> The problem is, if there is nothing in the STR_LAYER field, the whole
> field
> is blank.
> Is it possible to display Stratum always, and Layer when it's available?
> Thanks!
> Amber
>|||If you concatenate a string with a null value, it will return null.
Use ISNULL function:
dbo.TABLE1.STR_STRATUM + N' ' + ISNULL(dbo.TABLE2.STR_LAYER ISNULL(), '')
Francesco Anti
"amber" <amber@.discussions.microsoft.com> wrote in message
news:AF278105-D1AF-44DA-AD22-13E762A0690A@.microsoft.com...
> Hello,
> Within a view I've created, I have combined 2 fields to make 1.
> dbo.TABLE1.STR_STRATUM + N' ' + dbo.TABLE2.STR_LAYER AS StratumLayer
> This is for display (to populate a listbox in .NET).
> The problem is, if there is nothing in the STR_LAYER field, the whole
> field
> is blank.
> Is it possible to display Stratum always, and Layer when it's available?
> Thanks!
> Amber
>|||This worked.
Thanks!
Amber

Combining date and time

I have 2 fields, one containing just the date and another containing the time. I want to combine them into a proper date/time format. No matter what I try, I lose 2 days in the process. Combining

2005-12-21 00:00:00.000
and
1899-12-30 14:30:00.000

will get me the 19th at 14:30. What am I missing here?

TIAThis was addressed recently in the following thread: link (http://www.dbforums.com/t1203973.html)

Regards,

hmscott|||Thank you. I searched and somehow missed that thread. Bad choice of search words I suppose. I guess my problem was not using varchar. I had tried using convert but used datetime as the type, using a style of 101 for the date portion and 108 for the time. This seems to be working correctly:

Convert(datetime,Convert(varchar(11), DateField, 101) + ' ' + Convert(varchar(8), ReqTime, 108))

Combinig fields

Hi all,
In Access I can do a simply query and put something like:
SELECT FirstName & ", " & LastName AS FullName FROM People
doing this woud combine the two fields into one... can this be done in a SQL
stored procedure?
Thanks
GavUse the + operator to combine columns...just make sure that you don't =
add numbers if you really want to combine them as strings. =20
use pubs
go
select au_lname, au_fname, 'FullName' =3D au_lname + ', ' + au_fname =
From authors
--=20
Keith
"Gav" <spam@.spam.com> wrote in message =
news:e3jEGHECEHA.2348@.TK2MSFTNGP09.phx.gbl...
> Hi all,
>=20
> In Access I can do a simply query and put something like:
>=20
> SELECT FirstName & ", " & LastName AS FullName FROM People
>=20
> doing this woud combine the two fields into one... can this be done in =
a SQL
> stored procedure?
>=20
> Thanks
> Gav
>=20
>|||I have tried this but it simply returns null all the time.
Regards
Gav
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:u2yfqNECEHA.2308@.tk2msftngp13.phx.gbl...
Use the + operator to combine columns...just make sure that you don't add
numbers if you really want to combine them as strings.
use pubs
go
select au_lname, au_fname, 'FullName' = au_lname + ', ' + au_fname From
authors
Keith
"Gav" <spam@.spam.com> wrote in message
news:e3jEGHECEHA.2348@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> In Access I can do a simply query and put something like:
> SELECT FirstName & ", " & LastName AS FullName FROM People
> doing this woud combine the two fields into one... can this be done in a
SQL
> stored procedure?
> Thanks
> Gav
>|||I can see whats happening, if one of the fields is null it only returns
null... can I get it to ignore the field if it is null?
Regards
Gav
"Gav" <spam@.spam.com> wrote in message
news:uT9giUECEHA.464@.TK2MSFTNGP11.phx.gbl...
> I have tried this but it simply returns null all the time.
> Regards
> Gav
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:u2yfqNECEHA.2308@.tk2msftngp13.phx.gbl...
> Use the + operator to combine columns...just make sure that you don't add
> numbers if you really want to combine them as strings.
> use pubs
> go
> select au_lname, au_fname, 'FullName' = au_lname + ', ' + au_fname From
> authors
> --
> Keith
>
> "Gav" <spam@.spam.com> wrote in message
> news:e3jEGHECEHA.2348@.TK2MSFTNGP09.phx.gbl...
> SQL
>|||There are a few options that you can use...
Here are a few that come to mind:
CREATE TABLE #foo (col1 char(5), col2 char(5))
INSERT INTO #foo (col1, col2) VALUES ('test', null)
INSERT INTO #foo (col1, col2) VALUES ('test1', 'test1')
GO
SELECT col1 + ' ' + col2 FROM #foo=20
SELECT col1 + ' ' + ISNULL(col2, '') FROM #foo
SELECT col1 + ' ' + COALESCE(col2, '') FROM #foo
SELECT col1 + ' ' + CASE WHEN col2 IS NULL THEN '' ELSE col2 END FROM =
#foo
--=20
Keith
"Gav" <spam@.spam.com> wrote in message =
news:%23h7ZKXECEHA.3344@.tk2msftngp13.phx.gbl...
> I can see whats happening, if one of the fields is null it only =
returns
> null... can I get it to ignore the field if it is null?
>=20
> Regards
> Gav
>=20
> "Gav" <spam@.spam.com> wrote in message
> news:uT9giUECEHA.464@.TK2MSFTNGP11.phx.gbl...
don't add
From
done in a
>=20
>|||You can replace that column value with an empty string:
SELECT
au_fname + COALESCE(initial, '') + au_lname AS full_name
FROM tblname
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Gav" <spam@.spam.com> wrote in message
news:%23h7ZKXECEHA.3344@.tk2msftngp13.phx.gbl...
> I can see whats happening, if one of the fields is null it only returns
> null... can I get it to ignore the field if it is null?
> Regards
> Gav
> "Gav" <spam@.spam.com> wrote in message
> news:uT9giUECEHA.464@.TK2MSFTNGP11.phx.gbl...
add
a
>|||Thanks for the help Keith and Tibor thats works great.
Cheers
Gav
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eU0HchECEHA.3400@.tk2msftngp13.phx.gbl...
> You can replace that column value with an empty string:
> SELECT
> au_fname + COALESCE(initial, '') + au_lname AS full_name
> FROM tblname
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Gav" <spam@.spam.com> wrote in message
> news:%23h7ZKXECEHA.3344@.tk2msftngp13.phx.gbl...
> add
From
in
> a
>

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 separate date & time fields into one datetime field?

Good morning.

I am importing an XLS file into one of my tables. The fields are:

Date Id Time IO

12/22/2006

2

12:48:45 PM

9

12/22/2006

16

5:40:55 AM

1

12/22/2006

16

12:03:59 PM

2

When I do the import, I get the following:

Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2

Here are my doubts:

1. Is it be better to combine the Date & Time fields into one column? Advantages/Disadvantages?
2. If I don't combine them, should I use varchar or datetime data type?
2. What issues or problems might I have when I program SQL reports, if I leave the fields as they are?

Any comments or suggestions will be very much welcomed.

Cheers mates.I was suggested to try this out:

UPDATE tbl
SET Date = Date + convert(char(8), Time, 108)

I'll run it after I use DTwizard to export the data into my table. I should also mention I have no PKs defined, just a FK that references Id from a table called Employees. I'm thinking it's best to define ID and Date and Time as PKs.|||

As far as SQL Server is concerned, it will be far easier over time to work with and deal with date/time data if it is stored as one column. I recommend combining the two.

One primary reason is that the combined column will allow easier datetime comparisions and searches.

combine separate date & time fields into one datetime field?

Good morning.

I am importing an XLS file into one of my tables. The fields are:

Date Id Time IO
12/22/2006 2 12:48:45 PM 9
12/22/2006 16 5:40:55 AM 1
12/22/2006 16 12:03:59 PM 2

When I do the import, I get the following:

Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2

Here are my doubts:

1. Would it be better to combine the Date & Time fields into one
column? If so, how?
2. What issues or problems might I have when I program SQL reports, if
I leave the fields as they are?

Any comments or suggestions will be very much welcomed.

Cheers mates.drurjen (jfontecha@.gmail.com) writes:

Quote:

Originally Posted by

Good morning.
>
I am importing an XLS file into one of my tables. The fields are:
>
Date Id Time IO
12/22/2006 2 12:48:45 PM 9
12/22/2006 16 5:40:55 AM 1
12/22/2006 16 12:03:59 PM 2
>
When I do the import, I get the following:
>
Date Id Time IO
12/22/2006 12:00:00AM 2 12/30/1899 12:48:45 PM 2
12/22/2006 12:00:00AM 16 12/30/1899 5:40:55 AM 1
12/22/2006 12:00:00AM 16 12/30/1899 12:03:59 PM 2
>
Here are my doubts:
>
1. Would it be better to combine the Date & Time fields into one
column? If so, how?

Most probably. (In the end it depends on business needs, which I don't
anything about.)

A way to merge the columns would be:

UPDATE tbl
SET Date = Date + convert(char(8), Time, 108)

Quote:

Originally Posted by

2. What issues or problems might I have when I program SQL reports, if
I leave the fields as they are?

That you get 1899-12-30 printed all over the place, which you probably
don't want to. So you will need a lot of code to filter the date away.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland,

Thank you for replying. Basically the DB is for employee time
attendance records. I start out with a flat txt file and run that
through an Excel macro that:

a) eliminates repeat entries in a time lapse of 5min
b) erases null entries.

I then take the XLS file and use DTWizard to export it into a table
with the same fields as before: Date, Id, Time, IO. I have no primary
keys defined in this table, just a FK (Id). I believe the primary keys
should be ID, Date & Time.

I'll try your suggestion. Thx again, and sorry for the repeat post.

Combine results into one field

If I do a basic query
select * from grouping where code='12345'
I get the results
(fields are code,result)
12345 aaaaa
12345 assas
12345 f5fgh
I NEED to get it as aaaaa,assas,f5fgh. Since code is all the same, I
just want one result. Possible?
*** Sent via Developersdex http://www.examnotes.net ***http://www.aspfaq.com/2529
"Joey Martin" <joey@.kytechs.com> wrote in message
news:eUn4NjrTGHA.4308@.TK2MSFTNGP10.phx.gbl...
> If I do a basic query
> select * from grouping where code='12345'
> I get the results
> (fields are code,result)
> 12345 aaaaa
> 12345 assas
> 12345 f5fgh
>
> I NEED to get it as aaaaa,assas,f5fgh. Since code is all the same, I
> just want one result. Possible?
> *** Sent via Developersdex http://www.examnotes.net ***

Combine Names

Hello.
A quick layout of what I am trying to do.
I have 3 fields in a table. There names are..
fname, lname and fullname.
What I would like to know is, how do I combine the first 2 fields to show
the results as the full name in the fullname field?
Is this possible. If so, can someone please explain.
Thank you so much.You can use a computed column, basically:
ALTER TABLE your_table DROP COLUMN fullname
ALTER TABLE your_table ADD fullname
AS fname + ' ' + lname
Jacco Schalkwijk
SQL Server MVP
"noixa1234" <noixa1234@.discussions.microsoft.com> wrote in message
news:300E0247-E903-44CD-A6AB-68A64D7489F5@.microsoft.com...
> Hello.
> A quick layout of what I am trying to do.
> I have 3 fields in a table. There names are..
> fname, lname and fullname.
> What I would like to know is, how do I combine the first 2 fields to show
> the results as the full name in the fullname field?
> Is this possible. If so, can someone please explain.
> Thank you so much.|||If the Fname and/or the Lname might be null, then you need to use the follow
ing
IsNull(FName+ ' ', '') + IsNull(LName, '')
If only the first name might be null then you can use
IsNull(FName+ ' ', '') + LName
Charly
"noixa1234" wrote:

> Hello.
> A quick layout of what I am trying to do.
> I have 3 fields in a table. There names are..
> fname, lname and fullname.
> What I would like to know is, how do I combine the first 2 fields to show
> the results as the full name in the fullname field?
> Is this possible. If so, can someone please explain.
> Thank you so much.|||Select FName, LName , FName + ' ' + LName as Fullname from <source table>
I assume FName and Lname are either char or varchar fields; you can simply
concatenate to create the full name.
regards,
Sarav...
"noixa1234" <noixa1234@.discussions.microsoft.com> wrote in message
news:300E0247-E903-44CD-A6AB-68A64D7489F5@.microsoft.com...
> Hello.
> A quick layout of what I am trying to do.
> I have 3 fields in a table. There names are..
> fname, lname and fullname.
> What I would like to know is, how do I combine the first 2 fields to show
> the results as the full name in the fullname field?
> Is this possible. If so, can someone please explain.
> Thank you so much.|||Here is an example:
use northwind
go
select employeeid, lastname, firstname
into dbo.t1
from dbo.employees
go
-- you do not need a computed column.
-- you can do the concatenation in a select statement
alter table dbo.t1
add fullname as lastname + ', ' + firstname
go
select * from dbo.t1
go
drop table dbo.t1
go
AMB
"noixa1234" wrote:

> Hello.
> A quick layout of what I am trying to do.
> I have 3 fields in a table. There names are..
> fname, lname and fullname.
> What I would like to know is, how do I combine the first 2 fields to show
> the results as the full name in the fullname field?
> Is this possible. If so, can someone please explain.
> Thank you so much.

combine fields and text in select statement

Is it possible to combine fields and text in a select statement?

In a dropDownList I want to show a combination of two different fields, and have the value of the selected item come from a third field. So, I thought I could maybe do something like this:

SELECT DISTINCT GRPAS GroupName, "Year: " +YEAR + "Grade: " + GRDAS ShowMeFROM GE_DataWHERE (DIST = @.DIST)

I hoped that would take the values in YEAR and GRD and concatenate them with the other text. Then my dropDownList could show the ShowMe value and have the GroupName as the value it passes on. However, when I test this in the VS Query Builder, it says that Year and Grade are unknown column names and changes the double-quotes to square brackets.

If this is possible, or there's a better way to do it, I'd love some more info.

Thanks!

-Mathminded

You could do it in the SELECT statement. This kind of formatting is generally done at the application/GUI layer. You need to use single quotes for strings. Also Year is a keyword so you use square brackets.

SELECT DISTINCT GRPAS GroupName, 'Year: ' + [YEAR] + 'Grade: ' + [GRD]AS ShowMeFROM GE_DataWHERE (DIST = @.DIST)

|||

I got it to work! On a whim I decided to try single quotes and that got me farther. The error it produced then led me to this page:

http://weblogs.foxite.com/andykramek/archive/2005/09/18/921.aspx

Then I realized I needed to change the type for one of the columns. Thus, I ended up with this SQL statement which works:

SELECT DISTINCT GRPAS GroupName,'Year: ' +YEAR +' and Grade: ' +CAST(GRDAS CHAR(2))AS ShowMeFROM GE_DataWHERE (DIST = @.DIST)

|||

ndinakar:

You could do it in the SELECT statement. This kind of formatting is generally done at the application/GUI layer. You need to use single quotes for strings. Also Year is a keyword so you use square brackets.

SELECT DISTINCT GRPAS GroupName, 'Year: ' + [YEAR] + 'Grade: ' + [GRD]AS ShowMeFROM GE_DataWHERE (DIST = @.DIST)

Thanks, Dinakar! I thought I had figured it out quickly but you had it even faster! :-) Thanks for pointing out the keyword issue, also.

|||

The output from my working statement is:

GroupName ShowMe

AYear: 0203 and Grade: 3AYear: 0304 and Grade: 4AYear: 0405 and Grade: 5BYear: 0203 and Grade: 4BYear: 0304 and Grade: 5BYear: 0405 and Grade: 6CYear: 0203 and Grade: 5CYear: 0304 and Grade: 6CYear: 0405 and Grade: 7DYear: 0203 and Grade: 6DYear: 0304 and Grade: 7DYear: 0405 and Grade: 8EYear: 0203 and Grade: 7EYear: 0304 and Grade: 8EYear: 0405 and Grade: 9FYear: 0203 and Grade: 8FYear: 0304 and Grade: 9FYear: 0405 and Grade: 10GYear: 0203 and Grade: 9GYear: 0304 and Grade: 10GYear: 0405 and Grade: 11

With the way my dropDownList is working, the user could select any of the first 3 choices, for instance, and end up with the same group. It would really be great if I could get this to output a single GroupName and combine the other information. For instance:

GroupName ShowMe

A Years: 0203,0304,0405 and Grades: 3,4,5

B Years: 0203,0304,0405 and Grades 4,5,6

etc...

Would that be really difficult to do? I may play around with it and see if I can get it using embeded select statements. I've never tried those before. In the off chance that I'm successful, I'll post my results. :-)

|||Again, this is the task that has to be done in the application layer. You have more string functions available in .NET to manipulate the strings than in SQL Server.|||

I thought it may make things easier if I changed how the information was displayed. Rather than try to fit all that info into the dropDownList using some complicated SQL query, I'd like to create a table with columns Group, Years, and Grades so the users can refer to that when choosing just the group letter from the dropDownList. Here's a sample of the table I'd like to display to the users:

GROUP

YEARS

GRADES

A

0203, 0304, 0405

3, 4, 5

B

0203, 0304, 0405

4, 5, 6

C

0203, 0304, 0405

5, 6, 7

D

0203, 0304, 0405, 0506

6, 7, 8, 9

The database table has the data stored like this:

GRP

YEAR

GRD

A

0203

3

A

0304

4

A

0405

5

B

0203

4

B

0304

5

B

0405

6

C

0203

5

C

0304

6

C

0405

7

D

0203

6

D

0304

7

D

0405

8

D

0506

9

I've tried a bunch of different things with the GridView in Visual Studio but I'm not meeting with success. Any advice would be appreciated.

Thanks!

-Mathminded

|||

You can create a stored proc and a local table variable in it, get the values in the format you want into the table and do a select from the table at the end.

Check if this post helps in the concatenation:http://forums.asp.net/thread/1514443.aspx

Combine fields

I have two fields that I would like to combine into 1 field is this possible.
Example: Document # 555, Doc Description ABCD in the 3 field I would like 555-ABCD.
Is that possible in SQL Server thankscould you provide us some info as outlined in the hint sticky that's on top of the board?|||Select DocNoc, DocDesc from DocTable (take these to fields and insert it into one field in the same table.
Insert into DocNumDesc

How would I go about creating this syntax. Thanks|||SELECT DocNoc+DocDesc

??|||Okay is the correct syntax:

INSERT INTO DocNumDesc
SELECT DocNoc +DocDesc
FROM DocTable


Thanks