Concatenating A Field From A View Into One Field

Feb 4, 2008

Hi i need to concatenate a value into one field, i currently have a "view" in my sql which is retrieving values from a couple of tables including a junction table "tblUser_Constructor", someof these values are repeating at the moment, the only field changing is the constructor field, which i am trying to concatenate, for instance the following is happening;

 

User_ID       UserName      Constructor_ID    Constructor

1                 Jim                       2                     Keeley

1                 Jim                       5                     Truro

 

 

 below is my view;

 

CREATE VIEW dbo.vwUser
AS
SELECT         dbo.tblUser.User_ID, dbo.tblUser.Username, dbo.tblUser.User_Password, dbo.tblUser.User_Title, dbo.tblUser.User_Forename,
                      dbo.tblUser.User_Surname, dbo.tblUser.User_Phone, dbo.tblUser.User_Mobile, dbo.tblUser.User_Email, dbo.tblUser.Access_Right_ID,
                      dbo.tblUser.Tstamp, dbo.tblUser.Professional_Qualifications, dbo.tblUser.Organisation_Name, dbo.tblUser.Organisation_Address,
                      dbo.tblUser.Is_MainContact, dbo.tblUser.LastLogin_DateTime, dbo.tblUser_Constructor.Constructor_ID, dbo.tblAccess_Right.AccessLevel AS [Group],
                      dbo.tblCompany.Company_Name AS Constructor
FROM            dbo.tblCompany RIGHT OUTER JOIN
                      dbo.tblUser_Constructor ON dbo.tblCompany.Company_ID = dbo.tblUser_Constructor.Constructor_ID RIGHT OUTER JOIN
                      dbo.tblUser LEFT OUTER JOIN
                      dbo.tblAccess_Right ON dbo.tblUser.Access_Right_ID = dbo.tblAccess_Right.Access_Right_ID ON
                      dbo.tblUser_Constructor.User_ID = dbo.tblUser.User_ID

View 13 Replies


ADVERTISEMENT

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

Concatenating A Field While Grouping Records

Jul 20, 2005

All,Given multiple records with identical values in all fields except asingle varchar field, is there an efficient query that will group therecords into a single record and concatenate the aforementionedvarchar field into a single field with each of the source records'values separated by commas?Example:Record 1 'Doug' , '1'Record 2 'Doug' , '2'Output record 'Doug' , '1,2'Thanks in advance,Doug

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

Concatenating Problem Two Field In Case Expression

Jun 19, 2008

Dear all,
I am using sql server 2005.
I have two field firstname and lastname. Actual requirment is if category is doctor then display name as Dr. name else name but when I use following sql stament
Whenever one of the name is null it show name as null that is whenever firstname or lastname is null I get name as null

select @DOCNAME = CASE
WHEN DR_CUST_CATEGORY = 1 THEN 'Dr.' + ' ' + dr_cust_name + dr_cust_lastname
ELSE dr_cust_name + dr_cust_lastname
END FROM DOCTOR_MASTER WHERE DR_CUST_DOCTORCODE =@DOCCODE

Please suggest some ideas or any other idea please suggest
Thank you.

View 1 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

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 View Related

Transact SQL :: Return Field When A Field Contains Text From Another Field

Aug 25, 2015

I'm new to SQL and I'm trying to write a statement to satisfy the following:

If [Field1] contains text from [Field2] then return [Field3] as [Field4].

I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.

View 8 Replies View Related

How To Add Date Field And Time Field (not Datetime Field )

May 4, 2006

Good morning...

I begin with SQL, I would like to add a field that will be date like 21/01/2000.

Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.

How to do for having date and time in two different field.

Sorry for my english....

Cordially

A newbie

View 3 Replies View Related

View Field Name

May 3, 2007

does anyone know of a system stored procedure or table that can help me identify the original field name in an SQL table when given the alias of that field in the corresponding SQL view ?

e.g. something like :

Code:


select tableName, tableFieldName from ... where viewname = 'xxx' and viewFieldName = 'yyy'

View 7 Replies View Related

Adding A New Field To A View!!!

Aug 30, 2000

I am trying to add a field on a view which should be query-able.
Please what is the best way to go about it. HELP PLEASE!!!

View 1 Replies View Related

SQL View With Text Field

Apr 1, 2008

When creating a View, I am able to link the tables togethor and pull the data that I am looking for without a problem. However, when I add a field that is a 'Text' field, I get the following error:

....Column 'dbo.commpay.fdescript' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I add it to the GROUP BY clause, then I get the following error:

....The text, ntext and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

This particular field is contains the description of the parts in our inventory table.


VIEW Statement:

SELECT dbo.armast.fcinvoice AS Invoice, dbo.commpay.fnCalcComm AS Commission, dbo.commpay.fnrate AS CommRate, dbo.armast.fbcompany AS Company, dbo.commpay.fdarpost AS InvDate, dbo.commpay.fpartno AS PartNum, dbo.commpay.fnamount AS Price, dbo.commpay.fsalespn AS RepID, dbo.commpay.fmdescript AS Description
FROM dbo.armast INNER JOIN dbo.commpay ON dbo.armast.fcinvoice = dbo.commpay.fcarinv INNER JOIN dbo.slspns ON dbo.commpay.fsalespn = dbo.slspns.fsalespn
GROUP BY dbo.armast.fcinvoice, dbo.commpay.fnCalcComm, dbo.commpay.fnrate, dbo.armast.fbcompany, dbo.commpay.fdarpost, dbo.commpay.fpartno, dbo.commpay.fnamount, dbo.commpay.fsalespn, dbo.commpay.fmdescript

Any assistance or guidance would be appreciated!

View 4 Replies View Related

View With Auto_increment Field

Dec 27, 2005

I am trying to create a view in MSSQL that has a column which is anauto increment (or unique id) field but it doesn't work the way I wantit. Does anybody have an idea how to do this? The example below givesme only 0 in the id field.CREATE VIEW dbo.tree_x_categoriesASSELECT @@ROWCOUNTas id, *FROM <dbname>.dbo.tree_x_categoriesGOWhat I want is the fields from the table tree_x_categories plus anadditional field that has a unique identifier.Many thnx,Chris

View 3 Replies View Related

View A JPG From SQL2000 Image Field

Jan 16, 2004

I would like to retrieve and view .jpg pictures that have been stored in a Data Type "image", Length "16", SQL2000 field.
Any ideas on "How to......."
Thanks alot and enjoy
HandyMac

View 3 Replies View Related

Create View With Field Combination

May 30, 2004

Hi there, my situation is
I have a table x with 3 filed
a nvarchar(100), b smalldatetime, c text(16)
. I want to create a view like this:
select a + ' ' + b + ' ' + c as all_field from x where all_field like %my_str%

So, I always get a message error said wrong datatype, how can i do, please help me.

View 2 Replies View Related

Re-using A Calculated Sub-select Field In A View?

Jul 23, 2005

I've been running into more and more complexity with an application, becauseas time goes on - we need more and more high-level, rolled-up information.And so I've created views, and views that use other views.. and the queriesare getting slower and slower.This morning, I'm working on something like this:select<some columns>,"calculatedcolumn" = (select top 1 crap from stuff wherethingy='whatchamacallit')fromsomeviewnow, I realized that I need to really return "calculatedcolumn" in a coupleother places in the select like this - well, this is what I WANT to do:select<some columns>,calculatedcolumn = (select top 1 crap from stuff wherethingy='whatchamacallit'),otherfield = case SomeBitwhen 1 then calculatedcolumnelse count(somefield)end,otherfield1 = case SomeotherBitwhen 1 then calculatedcolumnelse sum(somefield)end,otherfield2 = case SomeBit2when 1 then calculatedcolumnelse avg(somefield)end,otherfield3 = case SomeBit3when 1 then calculatedcolumnelse count(somefield)end,fromsomeviewPoint is, I CAN'T do that, so I have to re-run that sub-select for EACH ofthese cases, and that is KILLING this stored procedure. It seems to me, thatif the database when and already got that field, for that row - I should beable to re-use, rather than going back out additional times.Is there a way to so this? Put simpler:selectx = (select top 1 user_id from users),bestUser=x,smartestUser=xfromUserscan I re-use "x" in that example. Thanks!

View 6 Replies View Related

Can Someone Help Me Create A Derived Field In A View

May 22, 2008



I am a C# developer who was forced to use a DataSet with Infragistics grid instead of the class object I was using as a data source, so that I can implement an AddNew functionality directly in the grid.

In code I would simply add a read only property to do this, and it was much simpler, but if someone could point in the right direction as to can a view do what I want.

The field I want to create is called "Info" short for HasInfo.
Info has 3 values (from an enumeration) NoInfo = 0; HasResume = 1; HasHomeInfo = 2

The business logic for this is if we have a resume, I show a resume icon in the grid, if we do not have a resume, if we have home phone # or mobil # then an icon of phones are shown in the grid.

To find out if a contact has a resume the SQL is going to be something like if exists (select ContactResumeID from ContactResume Where ContactResume.ContactID = Contact.ContactID) then 1; and if that is not true; check if Contact.HomePhone is Not Null Or Empty or Contact.WorkPhone is not null or empty then 2 else 0

I can do this in C# blindfolded, but in SQL I am fish out of water for compound if statements.

The SQL for this view is shown below (creating using SQL Server Gui Selector which I think is real cool).


SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER VIEW [dbo].[Employees]

AS

SELECT dbo.Company.CompanyID,

dbo.Company.Name AS CompanyName,

dbo.Contact.FirstName,

dbo.Contact.LastName,

dbo.Position.Title,

dbo.Department.DepartmentName,

dbo.ContactPosition.WorkPhone,

dbo.ContactPosition.Extension,

dbo.Contact.ContactID,

dbo.ContactPosition.ContactPositionID,

dbo.Department.DepartmentID,

dbo.Position.PositionID

FROM dbo.ContactPosition INNER JOIN dbo.Contact

ON dbo.ContactPosition.ContactID = dbo.Contact.ContactID INNER JOIN dbo.Position

ON dbo.ContactPosition.PositionID = dbo.Position.PositionID INNER JOIN dbo.Department

ON dbo.Position.DepartmentID = dbo.Department.DepartmentID INNER JOIN dbo.Company

ON dbo.Position.CompanyID = dbo.Company.CompanyID

Thanks,

Flying Elephant Software

View 4 Replies View Related

How To Create Unique Field Or Sequence In View

Jun 5, 2012

I have created a view based on joining 3 tables, however, it is not possible to have a unique field in the view which I must need it and I must create index on some other fields. Is there any way to create sequence number or uniqie field in mssql view.

View 13 Replies View Related

Iterate Field In Table And Create A View

Jul 14, 2015

I have a table that houses fully qualified table names, roughly 15. How can I iterate that table and create a view ? For example, something like this

Code:
Create Table tocreateviewfrom (dbname varchar(100))
Insert Into tocreateviewfrom Values (foxfire.dbo.abcd), (foxfire.dbo.abcde), (foxfire.dbo.abcdf), (foxfire.dbo.abcdg), (foxfire.dbo.abcde), (foxfire.dbo.abcdl)

--Then somehow iterate each value in the field dbname to create a view something like
Create View viewcreatedfromtable As
Select * from foxfire.dbo.abcd
Union All
Select * from foxfire.dbo.abcde

[Code] ....

I tried this, but it is only printing the last result returned not the entire result set

Code:
Declare @database varchar(max), @sql varchar(max)
Declare c1 Cursor For
Select Program
from tocreateviewfrom
Open c1

[Code] .....

View 11 Replies View Related

How To Separate The DateTime Field Into Two Fields In View

Jun 21, 2006

Hi ,

I've a DateTime field in a table and I want to separate it into two fields in an SQL Server 2005 view one for Date and the other for Time so What is the function I can use to do this process?

Best Regards,

View 4 Replies View Related

Prompt For User Input In Criteria Field Of View

Apr 12, 2006

In Access, I use [Enter Date] in the Criteria field of the Query. I tried the same thing in SQL Server in the Criteria field of the View and it does not recognize this. Is there a comparable command in SQL to get user input into the Criteria field of a view?

View 4 Replies View Related

Multi Value Values In Varchar Field, To Show Each Value Separately In A View

Jul 20, 2005

Hi thereGot a interesting problem (depends on a point of view.....).Background :-Agent (within Domino) is run daily basis which extracts data fromDomino Notes application to SQL Server 2000 database. Agent firstremoves ALL contents and then appends ALL data.Reporting Tool is SQL Reporting Services (very cool !!).Problem :-Within Domino Notes, it can have a field which is mult-value fieldi.e. contain multi valuese.g.(from Helpdesk application)!HowTo!!Access Email;!HowTo!!Access the Web;etc..Need to create a view, then use sql to create stored proc, to be usedas the dataset for report within SQL Reporting services.Format. (using above as data as example)!HowTo!!Access Email; (1 row) WWL/SDR/04023/010 (DocID)!HowTo!!Access the Web; (2 row) WWL/SDR/04023/010 (DocID)I have a unique KEY within table called DocID. The report will have 8separate datasets (i.e. using subreports), all linked back to UniqueKey. That's easy.The dataset causing me hassle is the 1 above. How do u split outvalues as separate rows ?Name of field called --> "ImpFunctionsImpacted".Also, notes expert, who's working on the agent, tell's me the data canbe split either as a comma or semi-colon.Any suggestions most welcome.

View 2 Replies View Related

Cannot Copy Field Names In Column Headings From View Results

Jan 8, 2007

When I am using a SQL Query I have an ability to control whether or not I am able to Include the Column Headers when copying or saving results.

The control exists in the Options > Query Results > Results to Grid > Include column headings etc.

My question is how to get this same ability when attempting to copy the results of a VIEW vs. a Query.
The idea is that when I setup a view it€™s a drag/drop type of query building (query building for dummies if you will). Once I have a view and click the Execute icon it will return all the records selected by the View. However, when I click the upper left/top box to select all rows and column and then try to copy/paste the records into Excel all the data copies just fine but the field name/column headers are not there.
How can I get the header fieldname date to copy/paste from View result set that I'm able to copy from a Query result set?
Thank you,
Mike

View 1 Replies View Related

How To View Field List On The Left Side Of Report Layout

Jan 4, 2007

I want to see all the fields list on the left side of report layout, you can drag a field and put it on the report.

Thank you very much.

View 5 Replies View Related

Problem Importing Data From An Access Memo Field Into A SQL Server Ntext Field.

Jul 11, 2005

I'm using DTS to import data from an Access memo field into a SQL Server ntext field.  DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!

View 4 Replies View Related

Pass In Null/blank Value In The Date Field Or Declare The Field As String And Convert

Dec 30, 2003

I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.

I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable

mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))

option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.

With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End

Please help

View 6 Replies View Related

How To Convert Datetime Field To A Date Field So Excel Recognize It As Data Type

May 17, 2015

I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE" .

How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?

I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)

**all of the above returned text objectes in excel and not date objects.

View 3 Replies View Related

Informix Date Type Field To SQL Server Datetime Field Error

Oct 17, 2007



I am trying to drag data from Informix to Sql Server. When I kick off the package
using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP
errors on two fields from Informix which are date data ....no timestamp part

I tried a couple of things:

Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.

Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them
as datetime but it failed.

What options do I have that will work?

View 1 Replies View Related

Odbc - Binding Sql Server Binary Field To A Wide Char Field Only Returns 1/2 The Daat

Jul 23, 2005

Hi ,Have a Visual C++ app that use odbc to access sql server database.Doing a select to get value of binary field and bind a char to thatfield as follows , field in database in binary(16)char lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_C_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);and this works fine , however trying to move codebase to UNICODE antested the followingWCHAR lpResourceID[32+1];rc = SQLBindCol(hstmt, 1, SQL_W_CHAR,&lpResourceID,RESOURCE_ID_LEN_PLUS_NULL , &nLen1);but only returns 1/2 the data .Any ideas , thoughts this would work fine , nit sure why loosing dataAll ideas welcome.JOhn

View 2 Replies View Related

Converting Datetimeoffset Field To Datetime Field / Why Milliseconds Value Is Incorrect

Nov 17, 2012

DECLARE @datetimeoffset datetimeoffset(3)
DECLARE @datetime datetime
SELECT @datetimeoffset = '2012-11-08T17:22:13.575+00:00'
SELECT @datetime = @datetimeoffset
SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime'
__________________________________________________ ___________
Result of above SQL is
@datetimeoffset datetime
2012-11-08 17:22:13.575 +00:002012-11-08 17:22:13.577
__________________________________________________ ____________

The result should be '2012-11-08 17:22:13.575', why the milliseconds value is incorrect

View 2 Replies View Related

Function To Return Remaining Of Field After It Finds A Character In The Field.

Feb 20, 2007

Hi,another problem I have is that have compounded fields in my sql table.Exampleproduct@customerI need a simple function to return "customer", so it should return the valueafter "@", unfortunate "@" will sometimes be character number 6, sometimescharacter number 7 etc.regardsJorgen

View 1 Replies View Related

Transact SQL :: NVARCHAR Field Getting Truncated After Increasing Field Length?

Aug 28, 2015

I have a very strange situation. I've increased the size of an NVARCHAR field from 8 to 9 in a database table. The format of the data that I enter will either be an 8 character field (123456-8) or a 9 character field (1234567-9). The '-' is critical.

It used to only accept the 8 character version, but after increasing the field size, if I try to insert the 9 character field version, it gets truncated after the '-', as though it's still only allowing 8 characters. But that only occurs when I include the '-' or other such characters like '#'. If I try to insert 1234567a9, it works. The following explains the outcomes:

Inserted Value -> Result in table

123456-8 -> 123456-8      *Correct
1234567-9 -> 1234567-     *Wrong
123456789 -> 123456789    *Correct
1234567#9 -> 1234567#     *Wrong
1234567a9 -> 1234567a9    *Correct

Why is it that characters such as '-' and '#' are truncating the value, but only if the string is 9 chars long?

I'm currently using a direct t-sql insert statement in SQLExpress. The field is a simple NVARCHAR(9) field.

View 3 Replies View Related







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