SQL Server 2008 :: Concatenating Multiple Text Fields Into One Field In Another Table

Oct 7, 2015

SQL code for the following? (SQL Server 2008 R2 - SQL Server 2012).

I have Table1 Containing two fields with the below entries

VehicleType Name

Two Wheels Bicycle
Two Wheels Scooter
Two Wheels Motorcycle
Four Wheels Sedan
Four Wheels SUV
Four Wheels Pickup
Four Wheels Minivan

The result I'm looking for would be

Table2

Vehicle Type
Two Wheels Bicycle, Scooter, Motorcycle
Four Wheels Sedan, SUV, Pickup, Minivan

View 1 Replies


ADVERTISEMENT

SQL Server 2008 :: Concatenating Multiple Rows (Certain Columns)

Jan 29, 2015

Currently I have a table that looks like the one below and I need to concatenate the description column and keep the rest of the row the same.

current:
IDSeq Desc DateOpen DateClose
1 AA description 1 1/1/2015 12/31/2015
1 AB description 2 1/1/2015 12/31/2015

Desired outcome:
ID Desc DateOpen DateClose
1 description 1,description 2 1/1/2015 12/31/2015

View 4 Replies View Related

SQL Server 2012 :: Concatenating Multiple Records Into One Field

Oct 5, 2015

I have a requirement where I have the following separate tables:

Table A:
FldA FldB
34
35
43
53
54
55
64
74
75

Table B:
FldC FldD
1Break Begin
2Break End
3Out
4In
5Dept

Desired Result:
FldA FldD
3 In;Dept
4 Out
5 Out;In;Dept
6 In
7 In;Dept

I have played around with the newly discovered 'for xml path' but I can't quite seem to get the sql syntax right.

View 2 Replies View Related

Concatenating Text Fields?

Dec 18, 2007


Hi All,

I am trying to get at some text fields from an AS400(JBA) system. the problem i'm having is that for each new line of text on AS400 it creates a new row with a line number associated in the SQL table. When trying to export to excel this causes problems because i need the text in one cell not spread over 15/20 lines.

I need to create a query/function that will concatenate the text lines together. I'm fairly new to T-SQL so could do with some help.


Example:-
ID TxtLn Text








R262965
1
Text 1

R262965
2
Text 2

R262965
3
Text 3


What i'm aiming for is:-
ID ConcatText
R262965 Text1 Text2 Text3



Can anyone please help me or guide me towards a starting point.

Thanks

View 2 Replies View Related

Concatenating Three Longint Fields Into A Date Field

Apr 20, 2001

Good afternoon one and all,

I have the following problem that I can use some help with :

I have a table in a linked server that has the date stored in three fields (i.e. day,month and year (I have no idea why)). I would like to concatenate these three fields together into a datetime format in a SQL statement

Something like

SELECT ([stc_dd] & '/' & [stc_mm] & '/' & [stc_yy]) AS stkdate

(the above line does not work)

Hope that is clear, thanx in advance for any and all help

Gurmi

View 1 Replies View Related

Auto Scripting SPs, Perhaps Concatenating Text Fields...

Jun 9, 2006

Hi,

I'm trying to get the text of all my SPs saved into text (*.sql) files. My first thought was to use sp_helptext and bcp the table to a text file but this isn't working (see my other post) so thought I'd try another method.

I can get the code from syscomment.text and concatenate the varchar(8000) field together in a text field. Unfortunately this isn't as easy as just text = text + newtext, how is this done?

Or am I doing it all comletely the wrong way? BTW, I have over 150 SPs so I can't save them individually.

Thanks!

Nick

View 3 Replies View Related

Concatenating Fields Over Multiple Rows

Jul 13, 2007

I have a query that produces seperate rows for people, but I want to combine them into one place. I tried doing this in SQL but apparently it's not very easy in SQL Server. You need to loop through a table using cursors. I'm not quite that advanced with SQL Server and was wondering if there might be an easier way just using SSRS.

In other words I have a table as such:

1 John Smith
2 Jane Doe
3 Matthew Jones

And I'd like to create one textbox that contains the following:

"John Smith, Jane Doe, Matthew Jones"

I've been drawing a blank. Anyone have any ideas?

Levi

View 4 Replies View Related

Concatenating Multiple Returns On One Field Only

Nov 18, 2004

I have a SQL query that returns several fields from several tables, eg. Title, Subtitle, Author, Binding and Imprint. When these are returned everything seems rosy until there are two authors linked to one title. When this happens Title, Subtitle, Binding and Imprint are repeated which is not required. Is there a way to concatenate the authors from multple records to return a single title with the concatenated authors, instead of repeating titles due to multiple authors?.

Example:
A query may currently return:
Title1 - Subtitle1 - Author 1a - etc
Title1 - Subtitle1 - Author 1b - etc
Title1 - Subtitle1 - Author 1c - etc
Title2 - Subtitle2 - Author 2a - etc
Title3 - Subtitle3 - Author 3a - etc

When I would like
Title1 - Subtitle1 - Author 1a, Author 1b, Author 1c - etc
Title2 - Subtitle2 - Author 2a - etc
Title3 - Subtitle3 - Author 3a - etc

My actual SQL code, if you are interested, is:

SELECT dbo.edition.ISBN, dbo.edition.title, dbo.party.first_name+' '+dbo.party.surname as name, dbo.edition.reviews, dbo.edition.long_blurb, dbo.series.series_id, dbo.series.series_number,
dbo.series.series_title, dbo.edition.sub_title, dbo.edition.about_author, dbo.edition.short_blurb,
dbo.series.editors_affiliations, dbo.title.contents, dbo.title.affiliations, dbo.series.series_editors
FROM dbo.edition INNER JOIN
dbo.series ON dbo.edition.series_id = dbo.series.series_id INNER JOIN
dbo.title ON dbo.edition.title_id = dbo.title.title_id INNER JOIN
dbo.agreement ON dbo.edition.edition_id = dbo.agreement.edition_id INNER JOIN
dbo.role ON dbo.agreement.role_id = dbo.role.role_id INNER JOIN
dbo.party ON dbo.role.party_id = dbo.party.party_id

View 1 Replies View Related

Concatenating Field For Multiple Records

May 30, 2008

Quick question. What I'm trying to do is concatenate a field for multiple records (hope that is worded in an understandable manner). Here's an example:

ID.....Code
5......33
5......23

ID.....Code...Result
5......33.....33 23
5......23.....33 23

I need the code to get the Result field. I know the code if you were to find the sum, min, max, etc...

(SELECT SUM(CODE)
FROM Table
WHERE ID = Table.ID) AS Result

But I don't know how to write it so it will combine strings.

View 5 Replies View Related

Executing Sql Code From Text Field Or From Multiple Varchar(8000) Rows In A Table

Jul 20, 2005

Does anyone know of a way to execute sql code from a dynamically builttext field?Before beginning, let me state that I know this db architecture isbuilt solely for frustration and I hope to make it better soon.Unfortunately, there is never a non-crucial time in which we can do anupgrade, so we are stuck for now.Point 1:There are multiple tables: students, courses, cross-referencestudent/courses, teachers, cross-reference teacher/courses, andothers.Point 2:Yearly archiving is done by appending a 2 digit year suffix to thetable and rebuilding the table structure for the current year. Thatis, each of the tables named above has an archive table for 1999,2000, 2001, etc. This leads to many tables and a big mess whenunioning them.Point 3:A history report is run by building a big query that unions each unionof tables (students, courses, etc) by year. This query has grown toobig for a varchar(8000) field. Actually, it's too big for 2 of them.Point 4:I don't want to write code to maintain any more varchar(8000) fieldsfor this query. It should be much more easily handled with atemporary table holding each bit of yearly archive data. I have builtthis and it works fine. I have also figured out how to pull the rowsfrom that table, concatenate them, and insert the resulting lump intoa text field in another table.Point 5:I haven't figured out how to grab the 5 or so records from that tableand execute them on their own. I figured I could grab them, put theminto a text field that's big enough to hold the whole query and thenselect and execute that text field. I've had no luck with that and Ihaven't had any luck finding any references that might help me withthis problem. I keep thinking of nesting execute() calls, but thatdoesn't work.I'm open to questions, potential solutions, hints about different wayto approach the problem, anything.Many thanks in advance,Rick Caborn

View 7 Replies View Related

Adding A Full Text Search Across Multiple Tables (with Text Fields)

Sep 7, 2007

Hi, i'm trying to do a full text search on my site to add a weighting score to my results.  I have the following database structure:
Documents: - DocumentID (int, PK) - Title (varchar) - Content (text) - CategoryID (int, FK)
Categories: - CategoryID (int, PK) - CategoryName (varchar)
I need to create a full text index which searches the Title, Content and CategoryName fields.  I figured since i needed to search the CategoryName field i would create an indexed view.  I tried to execute the following query:
CREATE VIEW vw_DocumentsWITH SCHEMABINDING ASSELECT dbo.Documents.DocumentID, dbo.Documents.Title, dbo.Documents.[Content], dbo.Documents.CategoryID, dbo.Categories.CategoryNameFROM dbo.Categories INNER JOIN dbo.Documents ON dbo.Categories.CategoryID = dbo.Documents.CategoryID
GOCREATE UNIQUE CLUSTERED INDEX vw_DocumentsIndexON vw_Documents(DocumentID)
But this gave me the error:
Cannot create index on view 'dbname.dbo.vw_Documents'. It contains text, ntext, image or xml columns.
I tried converting the Content to a varchar(max) within my view but it still didn't like.
Appreciate if someone can tell me how this can be done as surely what i'm trying to do is not ground breaking.

View 2 Replies View Related

SQL Server 2008 :: Concatenating Many Rows Into One

Jul 14, 2015

I have a table called Signatures

It contains the fields

Parentobject and [View As]

there can be more than one Parentobject so I want to concatenate them so I have them on one line

for example

901 Joe Dow
901 Jane Dow

I want one line - | 901 | Joe Dow, Jane Dow

I found something similar as below but I'm getting dups like

901 |Joe Dow , Joe Dow
901 | Jane Dow, Jane Dow

DECLARE @Delimiter VARCHAR(10) = ' '; -- this is the delimiter we will use when we concatenate the values
SELECT DISTINCT
ParentObject,
(SELECT STUFF(
(SELECT @Delimiter + s1.[View As]
FROM Signatures s2

[Code] .....

View 3 Replies View Related

SQL Server 2008 :: Long Text Field - Failed To Locate Ending Boundary Of A Sentence

Apr 23, 2015

I have an very long ntext field, made up of many sentences that I append a full stop to every one, I also strip out any line breaks within the text. However I get this error, when I look it up it comes up with "Failed to locate the ending boundary of a sentence."

View 0 Replies View Related

SQL Server 2008 :: Percentage Of Fields Used In A Table - Query?

Oct 28, 2015

I have tableX with columns colA, colB, colC, colD and there are 2256 rows in the tableX.

I would like to find out the percentages of colA, colB, colC, colD that hold data (where it is not an empty string or NULL value).

So out of 2256 rows in the table, the user has stored data in colA 1987 times, colB 2250 times, colC 2256 times and colD 17 times.

So the report would say:

colA: 88.07%
colB: 99.73%
colC: 100%
colD: 0.01%

We have an application that has a bunch of fields that we believe are not being used and would like to remove them, but we need to prove this by looking at the data.

I know I could run a query, one at a time and change the column name, but this would take a long time as there are a lot of columns in this table. I am hoping there is some way to do this in one query.

View 2 Replies View Related

How To Concate 2 Ore More Text Fields Into One Field?

Oct 12, 2006

Hello,

I hope someone has already done this, but I have a table with a text column- example ColA, now i want to run a query to select the ColA in this table and combine the results of ColA into a ColB in another table.

Something like - Note: the codes below doesn't work!!

DECLARE @ResultID as int
DECLARE @AccID int
DECLARE _rows CURSOR
FOR SELECT AccID FROM tableA

FETCH NEXT FROM _rows INTO @AccID

WHILE (@@fetch_status <> -1)
BEGIN

UPDATE TableB SET Report = Report + (SELECT txtField FROM tableA WHERE AccID = @AccID)

WHERE AccID = @AccID


FETCH NEXT FROM _rows INTO @AccID

END

Thanks in advance



View 8 Replies View Related

T-SQL (SS2K8) :: Parsing Text Field Into 4 Fields

Sep 5, 2014

I have a text field that I need to parse out into 4 fields.

30.125x23.625-18.875x25.375
6.1875X19.375-2.4375x15.625
0X0-0x0
26.875X11.375-11.125x22.875
0X0-0x0
0X0-0x0
6.1875X26.875-2.4375x23.125
6.1875X26.875-2.4375x23.125
6.1875X26.875-2.4375x23.125
26.625X14.875-11.125x22.875
26.625X14.875-11.125x22.875

I need to 26.625X14.875-11.125x22.875 should be 26.625 then 14.875 then 11.125 then 22.875

View 5 Replies View Related

Database Triggers - SQL Server - Fields Only Allowed If Listed In Another Field In Another Table

Nov 2, 2006

I would like to ensure data integrity in a column (actually multiple columns will need a trigger) in my table(s) by setting up a trigger which allows an update of my database field only if the value which is being written to the field in the database exists in another column (in another "check" table).eg. I only want values "Yes", "No" or "" in many of my fields, which I store in a column named "YesNoBlank" in another table.Does anyone know the easy way to do this? / Syntax for the trigger?

View 3 Replies View Related

Trying To CONCAT A TEXT Field With 2 VARCHAR Fields - Brick Wall.

Dec 7, 2006

Hi all. I have been going round and round for the past 2 days on this and would appreciate any help. In a view select statement, I need to concat 2 varchar fields with a text field. If I CONVERT the TEXT field to VARCHAR, only the first 30 characters of the field appear in the result set. If I convert the VARCHAR fields to TEXT, I get an error that I cannot CONCAT TEXT fields. I'm not sure what to do. Can someone please offer some assistance? Thanks in advance! Steve

View 1 Replies View Related

SQL Server 2008 :: Copying ID Number To Another Field In Table

Jun 26, 2015

I need to have a table that has a primary key

CREATE TABLE [dbo].[testing](
[tid] [int] IDENTITY(1,1) NOT NULL,
[sometext] [varchar](150) NOT NULL,
[idcopied] [varchar](50) NULL,
CONSTRAINT [testing_PrimaryKey] PRIMARY KEY CLUSTERED

..and eveytime I add 'sometext' as another row, the tid # needs to be duplicated to idcopied field

insert into [testing] (sometext,idcopied) values ('some junk',@@identity)

???

View 4 Replies View Related

SQL Server 2014 :: Find Multiple Entries In Two Fields One Table?

Sep 12, 2014

I am trying to find books which have the same title and publisher name as at least two other books and need to also show the book ref (ISBN number). I have the below script so far:

SELECT isbn, title, publishername
FROM book
WHERE title in (SELECT title
FROM book
GROUP BY title
HAVING count(title)>2 or count(publishername)>2)
order by title;

This is a snap shot of the output:

ISBN Title Publishername
0-1311804-3-6 C Prentice Hall
* 0-0788132-1-2 C OSBORNE MCGRAW-HILL
* 0-0788153-8-X C OSBORNE MCGRAW-HILL
* 0-9435183-3-4 C Database Development MIS
* 1-5582806-2-6 C Database Development MIS

[Code] ....

What I should be seeing is only the ones I have put an * next to. What am I missing from the scrip?

View 3 Replies View Related

SQL Server 2008 :: Bulk Import And Create New Table Based On Header Fields Of Imported File (XLXS)

Sep 11, 2015

I have a record in an Excel format (Excel 2010) and I would like to bulk import that into SQL Server 2008 and also while importing, SQL Server will automatically create a new table based on the header fields or row of the source file.

I am not sure if SQL Server 2008 has this capabilities.

View 0 Replies View Related

SQL Server 2008 :: Table And Column Name Where Given Text Is Matching

Jul 31, 2015

I need a script which will tell me Table name and column name where a given text is matching.

Example: search string = "I want to search something"

Output: Table T1 , Column C3.

DB: Microsoft SQL Server 2005 / 2008

View 1 Replies View Related

Concatenating Fields

Aug 9, 2007

Hi,there's a method to concatenate fields in a WHERE clause?I've a parameter which represents a name and surname of a person; in the table I've two fields representing one the name and the other the surname. I'd like to do a "LIKE" comparison concatenating Name and Surname field and confronting with my parameter... Is it possible?

View 3 Replies View Related

Concatenating Fields

Aug 9, 2007

Hi,I've a table with two fields representing one Name and the other teh Surname of a persona. I've a to create a Stored Procedure with one input parameter that is a string containing Name and Surname (I don't know in waht order...)What I'd like to do is to concatenate teh fields Name and Surname and confronting with "LIKE" in the "WHERE" clause... something like this:Select Name, Surname FROM XXX where (Name + ' ' + Surname LIKE @parameter OR Surname + ' '+ Name LIKE @parameter).The problem is that I don't know neither if it is possible neither the correct syntax... 

View 1 Replies View Related

Concatenating Fields In SQL

Apr 11, 2008

I am using SQL server 2000 and trying to do a concatenation of firstname,middle-initial and lastname.
SELECT first_name+('mid_ini'= casewhen middle_init is null then ''when middle_init ='' then ''else middle_initend)+nsl_last_nameFROM tbl_names
How is it possible to use the CASE condition along with concatenating the fields. Is this possible?
I would like my result to be "Bob J Sam".
and if there is no middle initial it should be blank or when its null it should be blank.
I know you can separate and get the results with the CASE statement,but is this feasible when you want to concatenate?
Thank you

View 9 Replies View Related

Concatenating Fields

Nov 9, 2007

I'm looking for ideas / options for concatenating several fields into one based on a sequence number. The sequence could go as high as 90 but I'm only interested in concatenating the first 3 sequences. I'd like to compare when seq is the same as the id, it's concatenated and placed in a virtual column.



Example:

table x:
column:attritbute
id:123
fname:xyz
lname:zmith
seq:1
address: 123 something
id:123
fname:abc
lname:zmith
address: 123 something
seq:2

Query:
select *
from x
where id = 123

would return 2 records:

ID | Concat | address | fname | lname
123 | abc / xyz | 123 something | xyz | zmith
...

I would like it to return:
ID Concat address
123 | abc / xyz | 123 something ....

Thanks!

View 1 Replies View Related

SQL Server 2014 :: Dynamically Concatenating Multiple Columns In A Sequence?

Oct 16, 2014

I have a requirement where in I have to concatenate the fields based on their sequence given in another table along with respect to their lengths. eg..

Input 1:

Table A: (below are the fields and their respective values, not all fields will have values)
-----------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR-->1234567890
LIFNR
VKORG-->a234
PRCTR
KUNRE-->4355325363
LIFRE-->88390234
PRODH

Table BIt contains the same fields as in table A and will have sequence number in which the concatenation should happen. The length field(LEN) will have corresponding field lengths(pipe delimited) should be considered in concatenation)

KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR--> 1
LIFNR
VKORG-->3
PRCTR
KUNRE-->2
LIFRE -->4
PRODH
LEN10|10|4|10

Expected Result:
---------------------
KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR
KUNNR1234567890
LIFNR
VKORGa234
PRCTR
KUNRE4355325363
LIFRE0088390234
PRODH
Concat_String12345678904355325363a2340088390234

Note: If the field length given in Table B doesn't match with actual size of the fields then, the field should be filled with 2 left spaces while concatenation.. Eg. In above example say LIFNR value = 88390234(len =icon_cool.gif then after concat the value should be like below:

12345678904355325363a234 88390234

Note:The fields are not constant..I have around 40 fields like that in which any combination of fields can be possible...eg..

KSCHL - ZIC0 (KEY)
KOTABNR - 521 (KEY)
MATNR -->2
KUNNR--> 4
LIFNR
VKORG-->1
PRCTR
KUNRE
LIFRE --> 3
PRODH

I am not sure which field has the value 1, 2 etc.. and how many fields are forming the combination..It can be sometimes 3/40 fields or it can be 10/40 fields...I have to dynamically get those values and concat...

I can have any number of fields for concatenation..above example is just for 4...it should be dynamic enough to handle any number of fields..

View 2 Replies View Related

Concatenating Numeric Fields

Mar 28, 2006

Friends,

I am attempting to concatenate two numeric type fields together with character data and the query is adding them together. I am assuming I need to convert the ints to a string type but would appreciate some info on the best way to do this...I am sure it's something simple but am not finding much on the web about it.

SELECT vehFacID + '-' + vehID AS vehNew FROM Vehicles

Returns the sum of vehFacID & vehID. Doh!

J.H.

View 7 Replies View Related

Problem With Updatetext In SQL 2000 Trying To Concatenate Multiple Text Fields

Jan 2, 2007

Hi all, I am creating a search table where the keywords field is madeup of several text fields and this is causing me some problems. I canconcatentate the text ok but i can't seem to concatenate matchingrecords here is the cursor loop. I'm not a fan of cursors but alsodidn't see another way of achieving this.declare @ptr1 varbinary(16)declare @Ptr2 varbinary(16)declare @profileid intdeclare @x intset @profileid = 0while @profileid is not nullbeginselect@profileid = min([id]),@ptr1 = MIN(textptr(text1))from #holdingwhere [id] @profileiddeclare c2 cursor fast_forward forselect textptr(searchterms), datalength(searchterms)from searchwhere search.[id] = @profileidopen c2fetch c2 into @ptr2, @xwhile @@fetch_status = 0beginupdatetext search.searchterms @ptr2 null 0 #holding.text1 @ptr1fetch c2 into @ptr2, @xendclose c2deallocate c2endThe #holding table contains the fields that i want to concatenate andthe search table is the resulting table. This example would loopthrough search and find id 1 in search and then append another fieldmatching id 1 in holding then move onto the next field in turn goingthrough the whole table.i.e.search holding result after each loopid text id text1 abc 1 def abcdef2 ghi 2 jkl ghijklWhen I run this, some of the records concatenate properly but most dontwith the same text being appended to the end of searchterms. i.e loadsof results will end up with jkl tagged onto the end. I can't figure outwhen my loop is falliing over!!! Can anyone help?Dan

View 4 Replies View Related

SQL Server 2008 :: Upload 1000 Text Files Into One Table - Skip Last Row?

Jul 19, 2015

Im trying to upload 1000 txt files into one table in SQL. I'm using the following query, to upload one txt file at a time:

bulk insert [dbo].AAA_2013_2015
from 'dataserverSQL Data FilesSQL_EMELIZFC x Bloque Detallada201308 Detalle FacturasFACT_BLOQ_AGO13 (4).txt'
with (firstrow = 2,
lastrow = ???,
fieldterminator = ';',
rowterminator = '0x0A')

I'm trying that the query skip the last row because gives me the following error:

Msg 4866, Level 16, State 1, Line 1
The bulk load failed. The column is too long in the data file for row 1, column 17. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

know a command to skip the last row, something like lastrow= all-1...or something like that.

I also executed using MAXERRORS command...like this:

bulk insert [dbo].AAA_2013_2015
from 'dataserverSQL Data FilesSQL_EMELIZFC x Bloque Detallada201308 Detalle FacturasFACT_BLOQ_AGO13 (15).txt'
with (firstrow = 2,
fieldterminator = ';',
MAXERRORS = max_errors,
rowterminator = '0x0A')

does not recognize MAXERRORS command, also tried to put a number of error instead of max_errors.

View 0 Replies View Related

Concatenating Fields With NULL Values

Jul 11, 2006

Hey everyone,

This is probably a very simple question, but I am just stumped. I am storing different name parts in different fields, but I need to create a view that will pull all of those fields together for reports, dropdowns, etc.

Here is my current SELECT statement:

SELECT m.FName + SPACE(1) + m.MName + SPACE(1) + m.LName + ', ' + m.Credentials AS Name,
m.JobTitle,
m.Company,
m.Department,
m.Address,
m.City + ', ' + m.State + ' ' + m.Zipcode AS CSZ,
m.WorkPhone,
m.FAX,
m.Email,
c.Chapter,
m.Active,
s.Sector,
i.Industry
FROM tblMembers m
LEFT OUTER JOIN tblChapters c
ON m.ChapterID = c.ChapterID
LEFT OUTER JOIN tblSectors s
ON m.SectorID = s.SectorID
LEFT OUTER JOIN tblIndustries i
ON m.IndustryID = i.IndustryID
WHERE m.DRGInclude = 1

My problem is that I don't know how to test for NULL values in a field. When you concatenate fields that contain NULL values, the entire contactenated field returns NULL. I am not aware of an IF statement that is available within the SELECT statement.

The first thing I would like to accomplish is to test to see if MName contains NULL. If it does I do not want to include + SPACE(1) + m.MName in the clause. Then, if Credentials contains NULL I do not want to include + ', ' + m.Credentials in the clause.

Can someone tell me what I am missing? Is there a function that I can use for this?

Thanks,

View 8 Replies View Related

Joining / Concatenating Fields - Unique Reference?

Jun 13, 2014

I have a simple query which displays items from inventory with their latest annual test date. I want to create another unique reference in my results to use as a certificate number. The number should be a combination of the item+month+year from the test date. What is the easiest way to accomplish this?

My query and my desired results are below:

select item, test_date
from inventory
where cat = 'TELE' and itemised_status > 15

item test_datecert_no
-------------------- ----------------------------------------
05MC0002 2014-06-10 00:00:0005MC0002-06-2014
06MT0001 2014-05-13 09:02:0006MT0001-05-2014
06MT0002 2014-05-13 09:03:0006MT0002-05-2014
06MT0003 2014-05-13 09:03:0006MT0003-05-2014
06MT0004 2014-05-09 14:12:0006MT0004-05-2014

View 2 Replies View Related

SQL Server 2008 :: Table Valued Function Where Parameter Has Multiple Values

Jan 29, 2015

Is it possible to pass multiple values to a TVF, such as using an IN clause?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved