Combining 3 Columns Into One (not Concatenation)

Oct 13, 2005

Greetings,
I am trying to "Fix" a poorly normalized table, and I wanted some info on the best way to go about this. It is an orders table that has items associated with it, and also "add-ons" to those items in the same table, like so:

order# Part# Addon1 Addon2 Addon3

What I would like to do is break the addons into a new table. Is there a way using a query/view/SP to bring all the addon fields into one column to create a new table with? or would I have to create some form of append to add the additional columns one at a time. Here is an example of what I want:

Old Table: addon1 Addon2 Addon3

New Table:
Addon1
Addon2
Addon3

Of course I would also provide a link between the part and the applicable addons.

Thanks

View 1 Replies

Concatenation Of Two Columns

Jun 16, 2004

Hi all,

I am trying to concatenate two columns First_Name and Last_Name to display as Name in a View. I used the following statement but the result only shows the First_Name.

Select First_Name + Last_Name as Name from Address;

How do i combine the two columns??

SQL 2000 running on Win 2000

Thanks in advance.

View 2 Replies View Related

Concatenation Of Columns.

Feb 22, 2008

My table looks like this.
Col1 Col2
-------------------------------------------------
Andrew 1 0394588893
Charles 6 0495869457
Carmon 5 9878957874

But, I want the result like this.
Col1 Col2
---------------------------------------------------
Andrew 10394588893
Charles 60495869457
Carmon 59878957874

The last digit in the col1 has to be joined as the first digit in the col2. How to write this query?.

View 7 Replies View Related

Concatenation Formula For Int Columns

May 29, 2006

Column A, Column B and Column C : All Integer.
I want a concatenation. For example A=111, B=222. C should be 111222 NOT 333. Is it possible? If it's possible, what is the formula?
Thanks in advance...

View 7 Replies View Related

Default Value Being A Concatenation Of Other Columns

Jul 19, 2007

Hello all,Does anybody know if it is possible to set the default value of afield to equal the concatenation of 2 other fields?I.E.Field 1 = 123Field 2 = 345Field 3 = (Field1 + Field2) = 123345This is easy enough to do using a stored procedure, but I'm trying touse the import tool and so need to do the concatenation by default.Many thanks,Drew.

View 1 Replies View Related

Combining Columns

Mar 3, 2000

I have 2 columns in a table and would like to combine
the 2 columns into 1 column separates by a delimiter.

Do anyone know the syntax??
Thanks, Vic

View 1 Replies View Related

Combining Columns

Jun 19, 2008

Hello everybody,

I have the following problem.

I have a database containing about 300 million record made out of 4 years worth of Exchange Logfiles.

I would like to make a query that retreives al send emails, its recipients and message_size.

SELECT msgid, sender_address, recipient_address, number_recipients, total_bytes FROM tbllogfiles

Results:
MSGID, SA , RA , NR , TB
5566 me@domain.nl 1@domain.nl 3 55
5566 me@domain.nl 2@domain.nl 3 55
5566 me@domain.nl 3@domain.nl 3 55

But i'd like to get;

MSGID, SA , RA , NR , TB
5566 me@domain.nl 1@domain.nl,3@domain.nl,2@domain.nl 3 55

Can anyone help me with this query?

Best Regards,

Sidney

View 3 Replies View Related

Combining Columns

Aug 29, 2005

Hi

What is the best way to add two columns into a single column. Lets say I have two columns with first and last names. How can I have a new column with first,last name??

Thanks

View 8 Replies View Related

Combining Columns

Jul 18, 2007

I need to combine my first 3 columns into 1.

SELECT [Category]
,[Sub Category - I]
,[Sub Category - II]
,[MSC #]
,[Price]
FROM [Pricing Analysis].[dbo].[Table1]

View 2 Replies View Related

Combining 2 Columns Into 1

Mar 21, 2008

Hey everyone,

I have a windows app with a particular report that has 2 date columns in it. I need to combine these two columns based on a choice that the user makes on a previous form. So the user enters two dates (beginning and ending) and then makes a selection. So if my query brings back

Column1 Column2
1/1/2000 1/1/2004
2/2/2002 2/2/2007
3/3/2005 3/3/2008
.... .....

If the user selects option 1 column1 is displayed on the report, option 2 then column2, and if they select option 3 then columns 1 and 2 must be combined together based on other criteria.

I'm passing a parameter with the selection to my report, is there any way to combine these columns strictly in the report, or would it be easiest to do this in my sql query, and if so how? Keep in mind that I must keep each column separate because after the user makes the selection I have to make certain comparisons to each column before combining them. If any more information is needed then just let me know and I will post it as well.

Thanks in advance,
Aaron

View 4 Replies View Related

Combining Results Into Columns

Oct 18, 2013

I have a query (SELECT * FROM Pricing) that produces the following results

Stockcode|ProductID|OurSellPrice|SupplierTypeID|CompetitorPrice
SC0001___|123______|22.45_______|1_____________|25.23
SC0001___|123______|21.45_______|2_____________|25.23

I want to convert this to the following :

Stockcode|ProductID|OurSellPriceType1|OurSellPriceType2|CompetitorPrice
SC001____|123______|22.45____________|21.45____________|25.23

We only have two types of suppliers but not every product is available from each type of supplier so we might get the following results:

Stockcode|ProductID|OurSellPrice|SupplierTypeID|CompetitorPrice
SC0002___|124______|22.45_______|1_____________|25.23

Stockcode|ProductID|OurSellPriceType1|OurSellPriceType2|CompetitorPrice
SC001____|123______|22.45____________|NULL_____________|25.23

View 1 Replies View Related

Combining Columns From 2 Tables

Aug 7, 2014

I am using the JOIN function to pull data from two tables. Table_A has all columns I need; Table_B contains only 1 column I need. The column I need data from in Table_A is called CITY_NAME and stops May 1st. The column I need in Table_B (which has the same values but begins May 2nd) is labeled CITY. In Table_A I have NULL values starting Mat 1st for CITY_NAME. In Table_B, I have NULL values for any date before May 2nd.

I need to replace the NULL values in table B (May 1st and forward) with the values that are in Table B

SELECT
a.DATE,
a.STATE,
b.CITY
FROM TABLE_A a
LEFT JOIN TABLE_B b ON a.ID = b.ID

I need to use a function similar to UNION, but TABLE_A has 10 columns and TABLE_3 has 3 columns.

View 3 Replies View Related

Combining Columns In Output

Feb 23, 2006

Using MySQL 5, MySQLQuery (latest). Complete Newbie,

I have the following query:

SELECT i.IndivId, i.Surname, i.First_Names, i.Parents,
(SELECT i.Surname FROM individuals i, families f
where (i.IndivId = f.father_ID) and (i.indivId = f.Family_ID))
as "father"
FROM individuals i
where i.Parents>0
order by i.Parents;

It returns nulls for the subselect. There are 2 tables, individuals and Parents. I am trying to use a father id. in the PArents table to cause the individual id. (being the father of the individual(s)) to be named in the father column. I have a feeling I'm going wrong here.

I think the query above is pretty self explanatory!

Many thanks.

View 4 Replies View Related

Combining Data From Two Columns

Feb 8, 2007

Basically what I am doing is uploading inventory information from our point of sale/inventory management software into our website. I have a third party app that does most of the work for me but I need a view of our item table in our POS that this 3rd party software will use as the data source for what gets uploaded into the web. I have created the view with the following sql statement:
CREATE VIEW dbo.vwsfwebitemswine
AS
SELECT dbo.Item.*, dbo.Category.Name AS CategoryName, dbo.Department.Name AS DepartmentName
FROM dbo.Item INNER JOIN
dbo.Category ON dbo.Item.CategoryID = dbo.Category.ID INNER JOIN
dbo.Department ON dbo.Item.DepartmentID = dbo.Department.ID
WHERE (dbo.Item.DepartmentID = 7) AND (dbo.Item.WebItem = 1)

As you can see I am pulling from 3 tables. Item, Category and Department so that I can get the actual category and department names not just their numeric ID codes referenced in the standalone item table. The view data looks like this:

UPC ….. CategoryName DepartmentName
12345….. MerlotWine
34556….. ChadonnayWine
Etc…

What I need is an additional column created that combines the DepartmentName column values with the CategoryName column values and includes a ~ in between like this.

UPC ….. CategoryName DepartmentName Combined
12345….. MerlotWine Wine~Merlot
34556….. ChardonnayWineWine~Chardonnay
Etc…

I’m not a SQL expert by any stretch. I’ve gotten this far using this forum but I’m stuck. Any help would be greatly appreciated.

View 1 Replies View Related

Combining Columns And Grouping By....

Jul 20, 2005

Hi,I have the following SQLSELECT Table1.Col1, Table3.Col1 AS Expr1,COUNT(Table1.Col2) AS Col2_No, COUNT(Table1.Col3) AS Col3_No etc,FROM Table3INNER JOIN Table2 ON Table3.Col1=Table2.Col1RIGHT OUTER JOIN Table1 ON Table2.Col2=Table2.Col2GROUP BY Table1.Col1, Table3.Col1The output rows have a value in either Table1.Col1 or Table3.Col1 but notboth.I'd like to combine Table1.Col1 and Table3.Col1 and group by the combinedcolumn in the result but don't know how.Thanks gratefully

View 5 Replies View Related

Combining Established Columns Into One

Aug 2, 2006

I have a table whose schema is already defined and populated with data. I would like to create a column named Name that combines the first and last name columns in the following format "last name, first name". I tried to create a formula that concatenated these two columns, but it kept spitting up on me. Any ideas?

View 5 Replies View Related

Query Assistance Combining Columns Into One

Oct 5, 2006

I have a query that gets three columns of data. PRODUCT_ID, SMALL_TEXT_VALUE, AND LARGE_TEXT_VALUE. I'd like to know if there is a way that I can alter my query below so that whenever SMALL_TEXT_VALUE is Null, it uses the value thats in the LARGE_TEXT_VALUE column. Whenever the small is null, the data I need is in the large column. My Query: Select EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID, EXTENDED_ATTRIBUTE_VALUES.SMALL_TEXT_VALUE, EXTENDED_ATTRIBUTE_VALUES.LARGE_TEXT_VALUEFrom EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTESWhere EXTENDED_ATTRIBUTE_VALUES.Ext_Att_ID = EXTENDED_ATTRIBUTES.Ext_Att_IDORDER BY Product_ID DESC

View 10 Replies View Related

Transact SQL - Combining Two Columns Of Data

Oct 23, 2001

How do I combine two columns (ex: [first name] [last name]) to get a combined result in a new column of ([first name] [last name]).

Thank you,

Andrew

View 2 Replies View Related

Concatenation

Jun 12, 2008

I need to concatenate two date fields so they appear in a drop-down list like this: 8:00 AM - 10:00 AM
I'm using MS SQL 2005 and my query looks like this: SELECT ClinicTimesID, ClinicID, (CTStartTime + ' - ' + CTEndTime) AS TimeSlot FROM Clinics_Times WHERE (ClinicID = 1) and I get this error: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Can I strip out the date so that only the time appears or is it even possible to concatenate these fields? If so, how?
Thanks!

View 1 Replies View Related

Concatenation

Sep 8, 2005

Hello all, I'm trying to combine two columns of data into a third column using a formula on the thrid column. Each of the columns could contain nulls and each of the columns could contain padding after or before the data. I'm trying to use the following formula yet SQL is throwing an error. Can someone provide another set of eyes to check this out? ISNULL(LTRIM(RTRIM([user_Define_4a])),’’) + ISNULL(LTRIM(RTRIM([user_Define_1])),’’) Thanks

View 2 Replies View Related

DTS Concatenation - Please Help

Nov 17, 1999

I am importing via DTS a .csv file. I have 2 issues:

-One field from the source needs to be inserted into two existing fields.

-Two fields from the source, First_Name and Last_Name need to be
concatenated to a destination field, Full_Name.

I do not know VB but I do know SQL.

Can anyone help?

View 1 Replies View Related

Concatenation

Mar 7, 2007

Hi. I want to make a query that concatenate the address of the person with a comma inbetween and neglecting the null value.

table - pspersonaldata
dcno name streetbldg1 streetbldg2 streetbldg3
1 jon santol1 null santol3
2 mike mangga1 mangga2 null
3 jay langka1 langka2 langka3
4 joy null buko2 buko3
5 jean null null null

expected output is:

name address
jon santol1, santol3
mike mangga1, mangga2
jay langka1, langka2, langka3
joy buko2, buko3
jean null

thanks.
-Ron-

View 14 Replies View Related

Concatenation

Oct 3, 2007

I am completely new to SQL and I have some syntax questions. I am trying to concatenate 4 fields and some padded constants to form a new key field to perform joins. The result should be a twelve character field without spaces. My problem is that the current fields use spaces as place holders and I need to replace the spaces with ‘0’.

[RD_ID] nvarchar length 5
[RDWY_ID] nvarchar length 1
[MLGE_TYPE] nvarchar length 1
[OVLAP_MLGE_CD] nvarchar length 1

Concatenate 12 characters

1st ‘0’ (constant)
2nd, 3rd, and 4th, from [RD_ID] (without the suffix)
5th and 6th from [RD_ID] suffix or replace spaces with ‘00’
7th 1 or 2 from [RDWY_ID]
8th Z from [MLGE_TYPE] or replace space with ‘0’
9th 1 – 9 from [OVLAP_MLGE_CD] or replace space with ‘0’
10th ‘S’ (constant)
11th ‘0’ (constant)
12th ‘0’ (constant)

Results should resemble
0001CQ100S00 or 000100100S00

This is the query I used in Access.

LRS: "0" & IIf((Len(LTrim(RTrim([HITIS2_TVCLEAR2]![RD_ID])))=3),LTrim(RTrim([HITIS2_TVCLEAR2]![RD_ID])) & "00",LTrim(RTrim([HITIS2_TVCLEAR2]![RD_ID]))) & [HITIS2_TVCLEAR2]![DIR_RDWY_ID] & IIf([HITIS2_TVCLEAR2]![MLGE_TYPE]=" ",0,[HITIS2_TVCLEAR2]![MLGE_TYPE]) & IIf([HITIS2_TVCLEAR2]![OVLAP_MLGE_CD]=" ",0,[HITIS2_TVCLEAR2]![OVLAP_MLGE_CD]) & "S00"

Thanks for any help.

View 6 Replies View Related

Concatenation

Feb 27, 2008

My file is in this format

Col1 Col2 Col3
---------------------------------------------------------------------------
Andrews S 93845877712 P
Sylvia 9999876669 J
Bill K 7657654677 L

I need the output like this
Col1 Col2 Col3
--------------------------------------------------------------------------
AndrewsS 93845877712 P
Sylvia 99999876669 J
BillK 76576546677 L

The character on the left of Col2 has to be joined to Col1 and character on the right of col2 has to be joined to Col3. Can anybody suggest a query for this.

Thanks,

View 3 Replies View Related

Concatenation

Apr 8, 2008

I need to concatenate the strings of the grouped result set in my report. Lets say if I have

ID Text
-----------------
1 text1
1 text2
1 text3

and if I am grouping by ID, the resulted text should be "text, text2, text3". Any ideas on this? Its kind of emergency

Thanks
Dinesh

View 5 Replies View Related

Dynamic SQL - Concatenation!

May 1, 2008

Hi All,
I've been trying to create a dynamic query using the 'Like' clause and '%'.
my code snippet looks like this:
while (@@FETCH_STATUS = 0)begin set @likeString = @likeString + ' item_Text LIKE ''%'+@word+'%'' OR ' fetch next from theLike into @word end

-- strip off last ORset @likeString = ltrim(rtrim(substring(@likeString, 0, (len(@likeString) - 3))))-- ================================================-- create query to find keywords in the index and store in temp table-- ================================================set @query = 'INSERT into #resulttable (itemcount, item_id) SELECT COUNT(d.item_id), d.item_id FROM tp_index_details AS d INNER JOIN tp_index ON d.idx_id = tp_index.idx_id 'set @query = @query +' WHERE (d.idx_id IN (SELECT idx_id FROM tp_index AS i WHERE ( 'set @query = rtrim(@query) + @likeStringset @query = @query + ' ) AND (subscription_id = 1000))) GROUP BY d.item_id ORDER BY d.item_id DESC'
The problem is the @query string gets truncated. My question is how to get the quotes around '%' variables to work in a string?
Thanks for any help!
regards
Davej

View 4 Replies View Related

SQL And Proper Concatenation Within VB

Jun 9, 2008

Hi all - I have posted inquiries on this rather vexing issue before, so I apologize in advance for revisting this. I am trying to create the code to add the parameters for two CheckBoxLists together. One CheckBoxList allows users to choose a group of Customers by Area Code, the other "CBL" allows users to select Customers by a type of Category that these Customers are grouped into. When a user selects Customers via one or the other CBL, I have no problems. If, however, the user wants to get all the Customers from one or more Area Codes who ALSO may or may not be members of one or more Categories; I have had trouble trying to create the proper SQL. What I have so far:Protected Sub btn_CustomerSearchCombined_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_CustomerSearchCombined.Click Dim CSC_SqlString As String = "SELECT Customers.CustomerID, Customers.CustomerName, Customers.CategoryID, Customers.EstHours, Customers.Locality, Category.Category FROM Customers INNER JOIN Category ON Customers.CategoryID = Category.CategoryID WHERE " Dim ACItem As ListItem Dim CATItem As ListItem For Each ACItem In cbl_CustomersearchAREA.Items If ACItem.Selected Then CSC_SqlString &= "Customers.AreaCodeID = '" & ACItem.Value & "' OR " End If Next CSC_SqlString &= "' AND " <-- this is the heart of my problem, I believe For Each CATItem In cbl_CustomersearchCAT.Items If CATItem.Selected Then CSC_SqlString &= "Customers.CategoryID = '" & CATItem.Value & "' OR " End If Next CSC_SqlString = Left(CSC_SqlString, Len(CSC_SqlString) - 4) CSC_SqlString &= "ORDER By Categories.Category" sql_CustomersearchGrid.SelectCommand = CSC_SqlString End SubAny help on this is much appreciated, many thanks --

View 5 Replies View Related

Problem With Concatenation In SP

Sep 10, 2004

Hi,
I'm trying to mak emy query dynamic such that depending upon certain conditions, the conditions in the WHERE clause change. I have my SP as shown below and it doesn't seem to work correctly this way and seems like it is not even taking it as a condition. Please advise on what is going wrong here. I'm building the @Condition variable dynamically and appending it to the where clause below. Any help wud be greatly appreciated.

Thanks

CREATE PROCEDURE dbo.CabsSchedule_ViewSchedule
(
@SiteCode smallint = 0,
@YearMonth int = NULL,
@ByYearMonth bit = 1
)
AS

DECLARE @tempYearMonth int
DECLARE @Condition varchar(1000)
SET @tempYearMonth = 0

IF @YearMonth IS NULL OR @YearMonth = 0
BEGIN
EXECUTE CabsSchedule_GetYearMonth @tempYearMonth, @YearMonth OUTPUT
END

IF @ByYearMonth = 0
BEGIN
DECLARE @Year int
DECLARE @Month int
SET @Year = CAST(SUBSTRING(CAST(@YearMonth AS VARCHAR(6)),1,4) AS INT)
SET @Month = CAST(SUBSTRING(CAST(@YearMonth AS VARCHAR(6)),5,2) AS INT)
SET @Condition = ' DATEPART ([month], FullDate) = ' + CAST(@Month AS VARCHAR(2)) + ' AND DATEPART ([year], FullDate) = ' + CAST(@Year AS VARCHAR(4)) + ' AND '
END
ELSE
BEGIN
SET @Condition = ' YearMonth = ' + CAST(@YearMonth AS VARCHAR(6)) + ' AND '
END

SELECT BillPeriod =
CASE
WHEN BillPeriod = 32 THEN 'NB'
WHEN BillPeriod = 33 THEN 'Holiday'
ELSE Convert(nVarChar(7), BillPeriod)
END,
WorkDay =
CASE
WHEN WorkDay = -1 THEN ''
WHEN WorkDay = 0 THEN 'Holiday'
ELSE Convert(nVarchar(7), WorkDay)
END,
JulianDate, CalendarDay, CalDayBillRcvd, Remarks, FullDate
FROMdbo.CabsSchedule
WHERE YearMonth = @YearMonth AND SiteCode = @SiteCode
GO

View 7 Replies View Related

SELECT CONCATENATION

Aug 28, 2000

I'm trying to implement a system where I want to use something like a group by with a concatenation clause.

e.g.

Table 1.
ID Field
1 null
2 null

Table 2.
ID Value
1 AAA
1 BBB
2 CCC
2 DDD
2 EEE

Using one query I would like to transform Table 1 to

ID Field
1 AAABBB
2 CCCDDDEEE

Is there any way of doing this ? At the moment I'm using two cursors to accomplish this task, but this is not really efficient.

Maurice v/d Zwaan

View 2 Replies View Related

Concatenation Problem

Dec 18, 2007

i am having data something like this:
number starting point ending point
1 ABC DEF
2 DEF ABC
1 PQR STU
2 STU PQR
1 ABC DEF
2 DEF PQR
3 PQR ABC

and i want it to be like this in my new table based on number column:
CONCATENATED
ABC/DEF/ABC
PQR/STU/PQR
ABC/DEF/PQR/ABC

can anyone suggest me how to write a query for this one?

View 3 Replies View Related

Interger Concatenation

Dec 15, 2004

Hey,

Does anyone know how to do integer concatenation using sql?

Say for instance I have 2004 and 12, I need 200412 and not 2016.

Can this be done?

Thanks

View 12 Replies View Related

Concatenation Of Text

Feb 24, 2005

I have an instance where I need to concatenate some data that is stored in a text datatype. I can't cast it to a varchar/char because that may well truncate the data. I just read about UPDATETEXT, which I think I can use, but I need to use it for a bunch or rows and it looks like this works on one row at a time. Anyone have experience with this?

View 7 Replies View Related

NULL && Concatenation

Mar 23, 2006

I am using the following sql statement to concatenate fields from a sql server in my query.

SELECT RTRIM(title) + ' ' + RTRIM(fname) + ' ' + RTRIM(lname) AS name, id FROM contact

2 questions:

1. How can I avoid a Null name field resulting from either fname or lname being Null? One Null field in the contatenation yields a Null field, even though the other field is not Null.

2. Does concatentation in the sql statement reduce performance significanlty?

View 5 Replies View Related