Concatenate A Field In A Slect Statement

May 17, 2008

I have a query that returns multiple results

Select fname from table
returns

fred
joe
dave

What I want to do is have the query return only one result like this
"fred, joe, dave"

Any ideas?

Thanks

Kal

View 2 Replies


ADVERTISEMENT

Concatenate One Field From Multiple Rows?

Oct 18, 2006

I have a SQL statement that fetches book information via a TITLE_ID which is fine if we only have one edition (hardback), but if there are two editions (hardback and paperback) it will return two rows like:

Title - Author - Edition
The Amazing Pixies - A. N. Author - Hdbk
The Amazing Pixies - A. N. Author - Pbk

Is there any way to concatenate the Edition field so the two lines become one? I have searched for ways to do this but have had no luck.

View 2 Replies View Related

Concatenate Multiple Records Into One Field

Feb 7, 2007

Someone please help!. I am trying to create a view in SQL Server 2000 to use for a report but I am having problems with concatenating multiple values into one field. In my example below I am trying to list all records in my queried table in columnA then concatenate a list of all other records that share the same value in column B of the queried table into another field. If there are no other matches for a row in columnA then I would leave the corresponding field in columnB blank. Thanks in advance.

TABLE
ColumnAColumnB
1.......A
2.......B
3.......C
4.......A
5.......A
6.......B
7.......C
8.......D
9.......C
10......E

EXPECTED OUTPUT
ColumnA ColumnB
1.......4,5
2.......6
3.......7
4.......1,5
5.......1, 4
6.......2
7.......3
8.......
9.......3,7
10......

View 2 Replies View Related

How To Concatenate Multiple Rows Into One Field?

Jul 20, 2005

Hi,I hope someone here can help me.We have a product table which has a many-to-many relationto a category table (joined through a third "ProductCategory" table):[product] ---< [productCategory] >--- [category]--------- ---------------- ----------productID productCategoryID categoryIDproductName productID categoryNamecategoryIDWe want to get a view where each product occupies just one row, andany multiple category values are combined into a single value, eg(concatenating with commas):Product Category-------------------cheese dairycheese solidmilk dairymilk liquidbeer liquidwill become:Product Category-------------------cheese dairy, solidmilk dairy, liquidbeer liquidWhat is the best way to do it in SQL?Thanks and regards,Dmitri

View 4 Replies View Related

Concatenate On An Insert Statement

Nov 12, 2007

I am running an insert in a stored procedure, but need tio concatenate on of the fields. How do I do that? Below is an example:

INSERTsam.dbo.product
(productName,
ProdDesc,
TransDateBegin,
TransDateEnd
)

SELECTCode, Description + 'the product is displayed' + <br><br>'To purchase this <a href=/purchaseitem.aspx?' >here</a>

FROMweb.dbo.order_codes AS OC
WHEREproductName = code


Any ideas how I can get the select statement working?
SB

View 4 Replies View Related

How Do I Concatenate Text To A Sql Statement

Oct 20, 2005

The following sql statement fails:SELECT IdFlight, OrganizerLastName + ", " + OrganizerFirstName + "Flt:" + FlightNumber As Concat FROM TableFlights Order ByOrganizerLastName, OrganizerFirstNameI'm trying to concatenate fields and words, but my asp.net page givesme an error saying " Flt:" is not a field.Thanks,Marvin

View 1 Replies View Related

SSIS Script Component To Concatenate Field

Jun 28, 2007

Is there a Data Flow Transromation in SSIS to take multiple columns from an excel sheet and concatinate them into one filed.

Ex:
'Strt-Address', 'City', 'State' into 'HomeAddress'.

I could dump them into a temp table and concatinate the fields and insert that into the destination but that seems like a waiste of using SSIS.

Thanks

View 1 Replies View Related

Transact SQL :: Convert Int To Time And Concatenate It With Datetime Field?

Sep 16, 2015

I have a datetime field for e.g 2015-09-15 00:00:00.000 and an integer field for e.g.100809.

The integer field is actually a time value as in hours,minutes,seconds. For e.g. the value

100809 means
10-hours
08-minutes
09-seconds

I need to combine the datetime and the int field to get the output as below

2015-09-15 10:08:09

View 7 Replies View Related

How To Concatenate Two Fields In A Textbox One Integer And Other Charecter Field

Jul 2, 2007

I have the folliwing two fields, want tp concatenate:



=Fields!sequenceno.Value & =Fields!LogType.Value



Thank you very much for the information.

View 3 Replies View Related

TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement

Oct 29, 2007

Hi guys,
I have the query below (running okay):



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
FROM myTables
WHERE Conditions are true
ORDER BY Field01

The results are just as I need:


Field01 Field02

------------- ----------------------

192473 8461760

192474 22810



Because other reasons. I need to modify that query to:



Code Block
SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02'
INTO AuxiliaryTable
FROM myTables
WHERE Conditions are true
ORDER BY Field01
SELECT DISTINCT [Field02] FROM AuxTable
The the results are:

Field02

----------------------

22810
8461760

And what I need is (without showing any other field):

Field02

----------------------

8461760
22810


Is there any good suggestion?
Thanks in advance for any help,
Aldo.

View 3 Replies View Related

Reporting Services :: IF Statement If Dataset Field Value Equals Value Of Dataset Field

Sep 3, 2015

Using this IIF statement:

=CountDistinct(IIF(Fields!Released_DT.Value = Fields!Date2.Value, Fields!Name.Value,
Nothing))
Released_DT = a date  - 09/03/2015 or 09/02/2015
Date2 = returns another date value in this case 09/03/2015

What I'm trying to do is: count distinct number of people (Fields!Name.Value) if the Relased_DT = Date2.My IIF statement is returning a zero value.

View 4 Replies View Related

Field Names From SQL Statement

Mar 9, 2004

Is there anyway to determine what the resulting Field Names are going to be from a SQL Statement?

For example:
SELECT TABLE1.FIELD1, TABLE1.FIELD2, TABLE1.FIELD3, TABLE2.FIELD1 AS ANOTHERNAME
FROM TABLE1 INNER JOIN TABLE2 ON TABLE1.PK = TABLE2.FK

resulting field names:
FIELD1
FIELD2
FIELD3
ANOTHERNAME

Seems easy enough splitting all values before "FROM" by comma and doing some manipulation to remove table names and anything before the word "AS". However, it gets more difficult when you have complex CASE statements embedded in you query that may also contain commas.

Just a shot in the dark because I don't know if anyone has already done something like this before.

Thank you in advance,

Jeff

View 2 Replies View Related

The Select Statement Is In A Field

Aug 31, 2005

Ok, I inherited this database and there is a field that stopres a selectstatement. Is there anyway possible to execute the value of the fieldwithin a select statement?For example:the table:Name "george"lookupForName "Select orders from Ordertable"So maybe something like select name, execute(lookupforname) as ordersSorry, I didn't design this, just inherited :)george

View 1 Replies View Related

Checkbox - Using In Update Statement As Bit Field

Jul 24, 2006

hi I have a bit field in sql server represented by a checkbox ... I am updating the database in code ( ie not using formview generated update .. ) the line that is falling over is .Parameters.Add("@archive", SqlDbType.Bit).Value = txtarchive.Checkedit falls over saying failed to convert string parameter to boolean the
value of txtarchive.checked is either true or false - how do i convert
this to 1 or 0 or something that sql is happy with ... thanks

View 2 Replies View Related

Include ID Field In GROUP BY Statement

May 15, 2007

I've got a query where i need to return a max value based on a select but one of the fields i need to return in the results is the records primary key ID No. This messes up the MAX bit and means that all results are returned, not just the max one.
 The query i'm using is very long so i've simplified what i mean by the example below. Say i have a table 'Fruits':
ID      FruitName      Cost1       Apple             0.452       Apple             0.633       Apple             0.524       Pear              0.895       Pear             0.83
And run the query:
select max(Cost),FruitName From Fruitsgroup by FruitName
It'll correctly return:
FruitName      CostApple             0.63Pear              0.89
Now i need the ID also returned by my query so i go:
select max(Cost),FruitName,ID From Fruitsgroup by FruitName,ID
This doesnt return the above results with the ID appended to it, it instead returns:
ID      FruitName      Cost1       Apple             0.452       Apple             0.633       Apple             0.524       Pear              0.895       Pear             0.83
As the ID is always distinct and therefore messes up the grouping. How in this instance would i return the correct result of:
ID      FruitName      Cost2       Apple             0.634       Pear              0.89
 Thanks.

View 9 Replies View Related

Query To Sum The Same Field Twice In The Select Statement

Mar 17, 2008

 Hello friends ,    I have table (MoneyTrans) with following structure
[Id] [bigint] NOT NULL,
[TransDate] [smalldatetime] NOT NULL,
[TransName] [varchar](30) NOT NULL, -- CAN have values  'Deposit' / 'WithDraw'
[Amount] [money] NOT NULL
I need to write a query to generate following output
Trans Date, total deposits, total withdrawls, closing balance
i.e. Trans Date,  sum(amount) for TransName='Deposit' and Date=TransDate , sum(amount) for TransName=Withdraw and Date=TransDate , Closing balance (Sum of deposit - sum of withdraw for date < = TransDate )
I am working on this for past two days with out getting a right solution. Any help is appreciated
Sara

View 5 Replies View Related

Select Statement With A New Identity Field

Mar 25, 2008

Hello,
 Is it possible to generate a identityfield dynamically upon select, like this:
SELECT tempID AS identity(1,1), username FROM table1 ORDER BY username ASC
I want the output to be:
1 - Name12 - Name23 - Name3
The reason for this, is that i want to change the sort order in many diffrent ways, but i need to get the IDs from 1-?? even when the sort order changes.
Like:
SELECT tempID AS identity(1,1), username FROM table1 ORDER BY username DESC
I want the output to be:
1 - Name32 - Name23 - Name1
 
Patrick

View 4 Replies View Related

Select Statement Eliminate Field Name

Oct 5, 2001

Hi

I have tabelA, Which has 10 columns, I need to select 10 column values only no field names. Is there any way I can select only table values not field names. I don't want to see field name in my query result set. Please let me know. I appreciate your help.

Thanks

Regards
-Leong

View 2 Replies View Related

SQL Insert Statement That Returns The ID Field

Sep 7, 2000

I'm fairly new to SQL, so this might be simple question:

I am adding records to an SQL7 database by using the INSERT statement. The table has an IDENTITY field which is auto-incremented, so a value is not needed for the field in the query.

Is there any parameters for INSERT that returns to me the value of the IDENTITY field for the record I just created?...

Any help or suggestions would be appreciated.

View 2 Replies View Related

EXECUTING A SQL STATEMENT AGAINST DATA FIELD

May 21, 2008



HELLO I'M NEW OF SQL SERVER. I'VE BEEN CHARGED BY MY COMPANY TO MOVE THE MDB WHICH WE WORKED TILL NOW TO SQL.
OBVIOUSLY I HAVE NOW TO CREATE THE DATABASE'S UPGRADING PROCEDURES WE WERE USING ON ACCESS.

MY PURPOSE IS TO MAKE RUN THE FOLLOWIG PROCEDURE, ONLY IF THE VALUE OF THE FIELD 'UPTGRD11' IS EQUAL AT '1', BELOW THE SQL STATEMENT I'M USING.
THIS OPTION IN ACCESS IS VERY EASY, BY MACRO OR BY VBA, I'M NOT ABEL TO DO IT IN T-SQL

IT DOESN'T RUN LIKE THAT


IF 'dbo.uptgrdt.uptgrd11' = '1'


BUT IT RUNS USING IS NOT NULL


IF 'dbo.uptgrdt.uptgrd11' IS NOT NULL


is there an easy way to do what i'm trying to?

View 3 Replies View Related

Insert Statement With Today's Date In One Of The Field

Nov 20, 2006

How do I write an Insert SQL statement with a default today's date inserted into one of the field?
 Help is apreciated.

View 9 Replies View Related

How Do I Call A Select Statement Properly When The Field Is A Yes/no?

Dec 4, 2006

Hello,
i am pretty new to asp. I am trying to do a select statement for sending an email to everyone who is not an admin. the code is below, i know it must be fairly simple, yet i do not know how to do it. With the code below, I select everyone. I want to know how to do it properly, similar to the second which does not work.
Dim cmd As New OleDbCommand("SELECT Username, Pass, Gender, FirstName, LastName, Email, NickName FROM tblUsers", conn)
DOES NOT WORK:
Dim cmd As New OleDbCommand("SELECT Username, Pass, Gender, FirstName, LastName, Email, NickName FROM tblUsers WHERE Admin = 'N'", conn)
Thanks in advance.

View 3 Replies View Related

Bind Data From A Sql Statement To A Hidden Field?

Apr 18, 2008

What is the vb.net syntax to bind data from a sqldatasource to a hidden field in a form? 

View 6 Replies View Related

SQL Update Statement Set Field Value To Column Default

May 17, 2008

Is there a way to set a field value to the column default in an update statement?
Eg.
UPDATE Table2 SET field1 = DefaultValue
where DefaultValue is the field1 column default in the table definition.
The reason I need to do this is when I delete a record from Table1, I need to set the foreign key in Table2 to the default (I don't want to delete the record in Table2, just want to set the key to a default key). I could hard-code the default value in the stored procedure but I think that's just not clean. If I create a new instance of the DB and the default value changes, I'd need to change the stored procedure(s). Just not clean...
To avoid a drawn-out discussion, there are reasons why I can't setup a relationship between the two tables and use "ON DELETE SET DEFAULT".
Any info greatly appreciated.

View 9 Replies View Related

Error In Image Field When Using CASE Statement

Dec 7, 2004

I've this Stored procedure on a SQLserver 2000 SP3:


SELECT *,CASE immagine WHEN NULL THEN 0 ELSE 1 END AS hasImage
FROM Squadre WHERE squadra = @squadra

this is a flag that returns if the image field is present or not..
i've a lot of this type of stored procedures.. but this one returns me an error..

---------------------------
Microsoft SQL-DMO (ODBC SQLState: 42000)
---------------------------
Errore 306: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
---------------------------
OK
---------------------------

An i can't save.. why?
reme,ber that in the same Db there's other Stored like this.. the same syntax and the same field or table.. can anyone help me??

View 2 Replies View Related

Decrypt Password Field - Select Statement

Aug 13, 2014

I've got a encrypted column in sql which holds the password field, e.g. TPSK9RlOz0/2BhuQntVeaBda+9g=, is their a way in a select statement to get the password ?

View 3 Replies View Related

SQL Server 2012 :: Set Field Value In Select Statement

Aug 4, 2015

I have a query that displays a bunch of fields, specifically a createdon field and closedate field.I need to find the diff in days between date1 and date2 and only disiplay those results.For example: where the NumDays = a certain value. For example, 81.I have the NumDays displaying in the query, but I don't know how to reference these values in the where clause.

SELECT
TBU.businessunitidnameAS 'BU Name',
LEADS.statecodenameAS 'Status',
LEADS.statuscodeAS 'Status Code',
LEADS.accountidnameAS 'Account Name',

[code]...

View 5 Replies View Related

SELECT Statement - How To Not Get Column Field Names?

Jan 24, 2007

I do a SELECT * from table command in an ASP page to build a text fileout on our server, but the export is not to allow a field name rows ofrecords. The first thing I get is a row with all the field names. Whydo these come in if they are not part of the table records? How do Ieliminate this from being produced? Here's the ASP code....<html><head><title>Package Tracking Results - Client Feed</title></head><body><%' define variablesdim oConn ' ADO Connectiondim oRSc ' ADO Recordset - Courier tabledim cSQLstr ' SQL string - Courier tabledim oRSn ' ADO Recordset - NAN tabledim nSQLstr ' SQL string - NAN tabledim objFSO ' FSO Connectiondim objTextFile ' Text File' set and define FSO connection and text file object locationSet objFSO = CreateObject("Scripting.FileSystemObject")'Set objTextFile =objFSO.CreateTextFile(Server.MapPath("textfile.txt"))'Response.Write (Server.MapPath("textfile.txt") & "<br />")Set objTextFile = objFSO.OpenTextFile("C: extfile.txt",2)' write text to text file'objTextFile.WriteLine "This text is in the file ""textfile.txt""!"' SQL strings for Courier and NAN tablescSQLstr = "SELECT * FROM Courier"' set and open ADO connection & oRSc recordsetsset oConn=Server.CreateObject("ADODB.connection")oConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" &"c:/Database/QaTracking/QaTracking.mdb" & ";"set oRSc=Server.CreateObject("ADODB.Recordset")oRSc.Open cSQLstr, oConnResponse.ContentType = "text/plain"Dim i, j, tmpIf Not oRSc.EOF ThenFor i = 1 To oRSc.Fields.CountobjTextFile.Write oRSc.Fields(i-1).NameIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineWhile Not oRSc.EOFFor i = 1 To oRSc.Fields.CountIf oRSc.Fields(i-1) <"" Thentmp = oRSc.Fields(i-1)' If TypeName(tmp) = "String" Then' objTextFile.Write "" &_'Replace(oRSc.Fields(i-1),vbCrLf,"") & ""' ElseobjTextFile.Write oRSc.Fields(i-1)' End IfEnd IfIf i < oRSc.Fields.Count ThenobjTextFile.Write " "End IfNextobjTextFile.WriteLineoRSc.MoveNextWendEnd IfobjTextFile.CloseSet objTextFile = NothingSet objFSO = NothingoRSc.CloseSet oRSc = NothingoConn.CloseSet oConn = Nothing%></body></html>

View 1 Replies View Related

Running A LIKE Statement When Searching For A Date Field...

Feb 14, 2006

I am trying to run a like statement that has a datetime column and for some reason it does not return any values. I looked in the SQL help files and in states in there that when trying to select using a datetime that the preferred way of doing it is using a like statment. Does anybody know a better way of doing this? Here is my example: (I have dates in this column ie 2006-02-13 11:30:54.220)

SELECT * FROM workorderhistory WHERE wheninstalled LIKE '%2006-02%'

View 7 Replies View Related

Select Statement With Run Time Field Selection

Jan 3, 2008



I have this SELECT statement.


SELECT [issueID], [name] FROM [MyIssue]


What I wanted to do is in addition to the above statement, I want to add two run time fields like this:


99 [issueID],'All Issues' [name]

So let's say the above select statements generates this list:

Summer 2007 Issue
Winter 2007 Issue

The two addition fields will make the result list like this:

01 Summer 2007 Issue
02 Winter 2007 Issue
99 All Issues


How do I accomplish this? Any help is much appreciated.

View 5 Replies View Related

Using A Field Alias For A CASE Statement In A GROUP BY Clause

May 20, 2004

When I created a CASE statement (This is at work, Pat:)) it is about 30-40 lines long. I gave it a name and set the name = to the case statement:

ie,

SELECT fieldname1 =
CASE
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
ELSE thisandthat
END
, fieldname2
, fieldname3
FROM tablename1
GROUP BY CASE
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
ELSE thisandthat
END, , fieldname2, fieldname3

etc.


The long CASE statement in my GROUP BY is awkward to me. Is this the only way to do it? I tried using the fieldname1 but it comes back as an invalid field name and asks for the "expression".

Regards,

Dave

View 5 Replies View Related

Help With SQL Statement To Find First And Last Populated Field In String Of Fields

Jun 21, 2006

Hello all,I'm at a loss on how to do this. We're using MS SQL 2000 Server and Ihave a list of fields I need to find the first and last entry for.Here's an example of the table:Number - VarChar(10)Jan - IntFeb - IntMar - IntApr - IntMay - IntJune - IntANd it'll look something like this:NumberJanFebMarAprMayJun12322001901922012032054432433322 4565423754694665And I need to create a table with this:NumberFirstLastDifference123220020554432433456235423754665-89I'm not sure if this'll copy over correctly, but I have gaps in thedata so I can't just say Jun-Jan, but I need tofind the first fieldwith data and last field with data, then find the difference of these.Suggestions? Is there a loop or something I can do in TSQL that'll dothis? I'd like to do this in Query Analyzer since it's just a one-timereport. Thanks --Alex

View 2 Replies View Related

Convert A Time Field In The Select Statement Of The Query

May 21, 2007

Hi,



I have a field called "Starting DateTime" and I want to convert into my local time. I can convert it in the report with the expression "=System.TimeZone.CurrentTimeZone.ToLocalTime(Fields!Starting_DateTime.Value)", but that is too late. I want to convert it in the Select statement of the query.



Can anyone help me please?



Thx

View 6 Replies View Related







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