Transact SQL - Combining Two Columns Of Data
Oct 23, 2001How 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
ADVERTISEMENT
Combining Data From Two Columns
Feb 8, 2007Basically 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.
Transact SQL :: Select And Parse Json Data From 2 Columns Into Multiple Columns In A Table?
Apr 29, 2015I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below)Â and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, Â fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
Transact SQL :: Striping Data Into Two Columns
Jun 3, 2015In SQL statement is it possible to split the below column into two columns?
COLUMN_AAAAA AAAAAAA CO A.A. # 4030
BBBBBB BBB BBB CO. # 4260
CCC CCCCC #3001
Transact SQL :: Put One Column Data Into Many Columns?
Oct 15, 2015how to split a single column into multiple columns with | delimited. For example I have column old_values in this I have a data with | delimited and I need to split this data into multiple columns.
Old_values
xxx|yyy|zzz|aaa|bbb|ccc|ddd
into
A Â Â B Â Â Â C Â Â Â Â D Â Â Â E Â Â Â Â F Â Â Â G
xxx  yyy   zzz   aaa   bbb   ccc   ddd
Combining Columns
Mar 3, 2000I 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
Combining Columns
Jun 19, 2008Hello 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
Combining Columns
Aug 29, 2005Hi
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
Combining Columns
Jul 18, 2007I need to combine my first 3 columns into 1.
SELECT [Category]
,[Sub Category - I]
,[Sub Category - II]
,[MSC #]
,[Price]
FROM [Pricing Analysis].[dbo].[Table1]
Combining 2 Columns Into 1
Mar 21, 2008Hey 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
Renaming Columns Based On Data Using Transact Sql
Jan 18, 2008A happy belated New Year to all!
Can someone please help me with the following. How do I, in SS 2000, use a value to label a column?
example
Col A Col B Col C Col D
CA '12/1/07' '11/1/07' '10/1/07'
I want to use the month and year of the date to rename the column.
Col A 12-2007 11-2007 10-2007
CA '12/1/07' '11/1/07' '10/1/07'
Thank you in advance for any help you can offer.
TMendez
Combining 3 Columns Into One (not Concatenation)
Oct 13, 2005Greetings,
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
Combining Results Into Columns
Oct 18, 2013I 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
Combining Columns From 2 Tables
Aug 7, 2014I 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.
Combining Columns In Output
Feb 23, 2006Using 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.
Combining Columns And Grouping By....
Jul 20, 2005Hi,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 RelatedCombining Established Columns Into One
Aug 2, 2006I 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 RelatedTransact SQL :: Generate Unique Username From Data Columns
Oct 19, 2015I am trying to achieve the below problem statement, however I am getting stuck at the looping part.
EX: We have three columns
First Name |Â Last Name | Mothers Name
I want to generate the username field using above columns, so lets consider, I have following data
First Name |Â Last Name | Mothers Name
a                   b                 cdef
a                   b                 cdfg
a                   b                 cdfj
Expected Usernames:
1: a.b
2: a.b.c
3. a.b.cd
Basically, it has to be FirstName.LastName.(incremental letters from MothersName until the name becomes unique)
Transact SQL :: Joining / Combining Two CTE Queries?
Apr 29, 2015I have these two CTE queries, the first one is the existing one which runs fine and returns what we need, the second is a new CTE query which result I need to join in to the existing CTE, now that would be two CTE's can that be done at all?The second query is working on the same table as the first one so there are overlaps in names, and they are the same, only columns I need to "join" is the "seconds" and "AlarmSessions".
;with AlarmTree as (
select NodeID, ParentID, NodeLevel, NodeName,
cast('' as varchar(max)) as N0,
cast('' as varchar(max)) as N1,
cast('' as varchar(max)) as N2,
cast('' as varchar(max)) as N3,
[code]....
Transact SQL :: Split Data Into Two Columns Based On Where Space Appears
Jul 8, 2015I have a field in a table that contains addresses e.g
15 Green Street
5F Brown Steet
127 Blue Street
1512 Red Road
I want to output the numbers into one column and the address to another column as i need to produce a report that only shows streets and roads but no numbers.
So basically no matter how many characters before the first space which can be numbers or letters i want these output into two columns.
Transact SQL :: Get DataTypes And Other Schema Related Data Of Columns In A Query
Sep 11, 2015Is it possible to get data-type,default-value, etc. (basically schema information) of columns in a particular query.This is something similar to what we get when we execute the followingÂ
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Employees'
But instead of TABLE_NAME i would like to give a query like
SELECT [EmployeeID],[LastName],[FirstName] from [dbo].[Employees] order by [EmployeeID].
Query Assistance Combining Columns Into One
Oct 5, 2006I 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 RelatedTransact SQL :: Combining Parts Of 2 Dates Into Third Date
May 7, 2015In a stored procedure I have 2 dates that I need to combine parts of into a third date. I need the year from date1 and the month/day from date2.
Date1 = '2015/6/1'
Date2 = '2016/1/1'
Date3 needs to be '2015/1/1'
I have been messing around with datepart and dateadd with no luck.
Transact SQL :: Combining Two Queries From Different Linked Databases
May 20, 2015I have 2 DBs located on separate Sql Servers but the DBs are linked. I am querying data from both DBs but want to combine the results. Here is my query but it doesn't seem to be working.
(SELECT DISTINCT
idname, name, address, address2, awardedtoname, suppno
FROM contract INNER JOIN
house ON contract.idname = house.idname)
JOIN
(SELECT DISTINCT
tpd.PropertyNumber AS [Property No], tpd.Address1 + ' , ' + tpd.Address2 AS Estate, tpd.Address1 AS Address1,
[Code] ....
How I could combine the results?
Transact SQL :: Combining Multiple Similar Queries
Nov 20, 2015I have to run this 3 times for similar but slightly different parameters. I only change the very top peice of code slightly each time:
1. Partition by Forename, Surname, DOB, Postcode
2. Partition by Forename, DOB, Postcode
3. Forename, Surname, DOB.
As you can see very subtle change, ideally I'd like to find a way to run just one report where all of the above occur, the issue I face running separately is one person may appear on 1 or more giving duplicates.
USE HealthBI_Views;this bit below is basically grouping my output on Forename, Surname, DOB & Postcode. What the whole process is trying to achieve is to show where a patient which has all of the above fields identical but has a different patient identifier suggesting that the service has allocated to unique identifiers to the same person which will result in both records needing to be merged.
WITH cte
AS (SELECT *,
COUNT(HEYNo) OVER (
PARTITION BY Forename, Surname,
DOB
[code]...
--- this bit below is simply showing all instances where the above criteria is met and there are more then one instances.WHERE countOfHeyNo > 1
--- The final output display all patient identifiable information from the MF_PATIENT table so that the report can be created in SSRS and run routinely by the Data Quality Team who can then investigate each occurance.
Transact SQL :: Combining Dates To Group Into A Date Range?
Nov 17, 2015My scenario is: a person has many events, all based on a date. I need to aggregate the person to show min and max dates for a period, the period being defined as ending when there is not an event following the next date.
DECLARE @Events TABLE
(
PK_EventINTIDENTITY(1,1) PRIMARY KEY
,FK_UserINTNOT NULL
,EventDateDATENOT NULL
)
DECLARE @User TABLE
[Code] ....
I would expect the groups to look something like below:
Is this where a recursive CTE may be used?
Combining Data From 2 Tables Help
Jul 12, 2004Hi...
I have two tables (teachers, students) each table has 3 fields (FirstName, LastName, EMail). I'm trying to combine the data from both tables into one so I have a "master list" to send e-mails to all teachers and students via an ASP script/CDO.
I've been reading up on Joins but I can't figure out the SQL to combine data from both tables. Any ideas?
Thanks,
Steven Lee
Combining Data Into One Field
Apr 14, 2008Hello,
I'm not sure if this is possible, but here is what I'm trying to do:
I have a table that contains for example the following:
SPECIES PERCENT
s 19
P 25
B 32
D 24
I want to end up with a single field, titled S_P_B_D with the values 19_25_32_24.
Possible?
Thanks in advance.
Amber
Combining Data From Two Tables
Apr 29, 2008Hello all - I'm relatively new to SQL and has been struggling with the below query. Any help is greatly appreciated. Thanks.
I have the following 2 tables:
I'm trying to get the below output:
Here's the SQL I've been using:
SELECT
a.PERSON_ID,
sum(a.AMOUNT),
CASE
WHEN b.JOB_CODE = 10 then "Level 2 Seller"
WHEN b.JOB_CODE = 15 then "Level 3 Seller"
ELSE
'Level 1 Seller'
END as "Job_Code"
FROM
tbl_SALES a,
tbl_JOBFUNCTION b
WHERE
a.PERSON_ID = b.PERSON_ID
GROUP BY
a.PERSON_ID,
b.JOB_CODE
Here's what my results are with the query I've been using:
Each person could have many job codes, but when they have 10 then they're consider "level 2".
- If they have 15 then they're "level 3".
- If they don't have either 10 or 15 then they're "level 1".
- If they have both 10 and 15 they're still "level 3".
Please help.
Thanks,
Jim
Combining Data From Two Tables
Jun 3, 2008Hey all
I'm trying to get data from two tables that have the same fields. One is a log table and the other is the actual table. I have the following:
SELECT tbl_LCData.* FROM tbl_LogOfLCDataTable, tbl_LCData
WHERE (((SELECT DATENAME(MONTH, tbl_LCData.PrintDate)) = (SELECT DATENAME(Month,getdate())) AND (SELECT DATENAME(MONTH, tbl_LogOfLCDataTable.PrintDate)) = (SELECT DATENAME(Month,getdate()))))
I am trying to display records that were printed this month. I only get data from tbl_LCData. It doesn't display data from the log table. For example:
Contents of tbl_LCData:
Id, Forename, Surname, PrintDate
17, A, Sample, 02/06/2008
18, B, Whatever, 11/04/2007
Contents of tbl_LogOfLCData:
Id, Forename, Surname, PrintDate
17, A, Sample, 01/06/2008
So, I want the query to display:
Id, Forename, Surname, PrintDate
17, A, Sample, 02/06/2008
17, A, Sample, 01/06/2008
The above shows records printed in the month of June!
Is there a join I need to use??
Many thanks,
Rupa
Combining Data From Two Tables
Nov 24, 2015I have database with two different tables BaseData and QualityDetails as given below with ID as the Primary key
BaseData.
Sent_to_Client  Name ID
1/2/2015 Â ABC1 1000000
2/24/2015 Â ABC2 1000001
5/24/2015 ABC3 1000002
3/24/2015 Â ABC4 1000003
QualityDetails
ID Error
1000000 Â Â 1
1000001 2
1000002 Â Â 0
1000003 Â Â 3
I want to write an SQL Query to get the data by combining both the tables by dates from 2/1/2015 to 5/30/2015 in BaseData table. My table after executing the query should be
ID Name Error
1000001 ABC2 2
1000002 ABC3 0
1000003 ABC4 3
Combining Text Data Rows
Jun 4, 2006I am working with a database derived from text documents. One of the tables (TEXT001) contains the text of the documents with each paragraph of each document assigned to its own row with a paragraph number in a SectionNo column. I want the entire text of each document in a single row with its own unique number (so that I can do a full text search with SQL Server 2005 that will search and return the entire document as a result). How do I combine the rows with the same DocumentID into a single row of text data? This will put the entire text content of each document in its own row.
TEXT001 table as it is
DocumentID
SectionNo
SectionText
1
1
Paragraph 1 of Document 1
1
2
Paragraph 2 of Document 1
1
3
Paragraph 3 of Document 1
2
1
Paragraph 1 of Document 2
2
2
Paragraph 2 of Document 2
New TEXT table
DocumentID
SectionText
1
Entire text of Document 1
2
Entire text of Document 2
I realize that I can use €œunion€? to combine tables with the same data type, but that is not what I am trying to do. Ideally, there is a way to create a new table and fill it with the combined SectionText data as a batch command. If anyone can tell how to do this, I would appreciate your help.
More modestly, I tried to use the €œGroup By€? clause to combine the SectionText data using this query:
SELECT DocumentID, SectionText FROM TEXT001
GROUP BY DocumentID
And got this error message:
Msg 8120, Level 16, State 1, Line 5
Column 'TEXT001.SectionText' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I figured that I could not contain the SectionText data as an aggregate function since it is text data and cannot be €œsummed€?, so I tried including it in the GROUP BY clause:
SELECT DocumentID, SectionText FROM TEXT001
GROUP BY DocumentID, SectionText
And got his error message:
Msg 306, Level 16, State 2, Line 5
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Where do I go from here to accomplish my goal of combining the paragraphs of each document into one row per document?
Combining Data From Variable Number Of Tables
Jul 20, 2005I have a requirment to take data from a large set of tables where thetotal number of these tables may change on a regular baisis andoutput into another table. All the tables willl have the samecolumns. Frequency is being debated but it maybe as much as once perhour.Example1) I need to choose all the following tablesselect * from dbo.sysobjects where name like '_CPY%.2) then I need the followingfor each of the tables found above, I need the outfrom from each ofthose tables to be inputted into another table. basically, I wouldwant the following output from each of the tables found in step 1select machineid,name from _cpy_offermanager_6783) In the end I would have something like dbo.ALLCPY with recordscombined from all other _CPY tablesRon Sorrell
View 2 Replies View Related