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