Concatenate Values From Same Column But Different Record In Single Row

Mar 26, 2008

Hi, I have a difficult case that I need to solve. I will try to be the very clear explaining my problem:


I have a sql query which brings me many records.
This records have a column in common which have the same value (COL1)

There is a second column (COL2) which has different values bewteen these records.
I need to concatenate values from the second column in records with same value in COL1. And I need only one record of the ones that have the same values. If two records have the same COL1 value, only one row should be in my result.

Let me give you an example:
COL1 COL2
RECORD1 1-A HHH
RECORD2 1-A GGG
RECORD3 1-B LLL
RECORD4 1-B MMM
RECORD4 1-B OOO
RECORD5 1-C NNN

Me result should be:


COL1 COL2
RECORD 1-A HHHGGG
RECORD 1-B LLLMMMOOO
RECORD 1-C NNN

It is clear what I need? I dont know if I can solve it through sql or any function inside SSIS.
Thanks for any help you can give me.

View 5 Replies


ADVERTISEMENT

Concatenate Column Value From Multiple Rows Into A Single Column

Feb 27, 2008

Hello,

I need to concatenate a column from multiple rows into a single column in a new table.

How can I do this?

SID NAME PGROUP
------------------------------------------------------------
3467602 CLOTHINGACTIVE
3467602 CLOTHINGDANCE
3467602 CLOTHINGLWR


Need to have

SID NAME PGROUP
------------------------------------------------------------
34676 02 CLOTHING ACTIVE , DANCE, LWR


THANK YOU

View 10 Replies View Related

Concatenate Query From Single Column

Feb 22, 2007

I have a query that I'm stumped on. The data has about 6000 records, of which about 460 of those have distinct dealer names and ids.

I am trying to condense this data so that there is only one record for each dealer, but my 3rd column has different values which is why some dealers have only 2 records, and others may have 10.

I know how to select distinct values, but what I want is to return a list of comma separated values for my 3rd column which consists of all the unique values from that 3rd column for that dealer.

For instance, say I have a dealer with 8 records. They have a unique ID and Dealer Name, but the 8 records appear because the dealer has 8 entries for its activity. The activities may or may not be unique.

So for example I have dealer ABC who has an id of 54, their name is ABC, but for their activites they have 8 entries (apple, orange, banana, pear, apple, banana, mango, peach).

I wnt to be able to select the distinct id, name, and distinct activities so that when my query is returned and displayed the record appears as such:

"54","ABC","apple, orange, banana, pear, mango, peach"

Does that make sense?

View 4 Replies View Related

Stored Procedure To Concatenate Column Value Sin A Single Cell?

Jan 8, 2007

HiI want to achieve the following transformation of data using a storedprocedure.Sourcecol1 col2(varchar)-------------------------1 1.11 1.22 2.12 2.22 2.3=================Desired Resultcol1 col2(varchar)--------------------------1 1.1 | 1.22 2.1 | 2.2 | 2.3=====================Thanks in advance. :)- Parth

View 12 Replies View Related

Reporting Services :: Concatenate 24 Columns With Semicolon Delimited Into Single Column

Sep 17, 2015

How do i concatenate 24 columns with semicolon delimited into a single column without getting data conversion error ....

View 2 Replies View Related

T-SQL (SS2K8) :: Concatenate Specific Values From A Column

May 20, 2014

Give this data set;

declare @BO table (Col1 varchar(50), Col2 varchar(50))
insert into @BO values
('01','009920140516102116'),
('071710811019600001000005',''),
('070534524264000001000005',''),
('001806505517000001000005',''),

[Code] ....

select * from @BO

The 2 digit number that appears on line 1, 7 and 13 (only in this example) i need to have added to the begin of each value below it until the next 2 digit number is encountered. The desired result set would look like:

declare @BOD table (Col1 varchar(50), Col2 varchar(50), col3 varchar(50))
insert into @BOD values
('01','009920140516102116',''),
('071710811019600001000005','','01071710811019600001000005'),
('070534524264000001000005','','01070534524264000001000005'),

[Code] ....

View 7 Replies View Related

SQL Server 2008 :: Concatenate Column Name With Values

Sep 17, 2015

I want to concatenate the column_names with the respective values.

Create table #test1 (Id int,Name varchar(10),Country varchar(10))

insert into #test1 values ( 1,'JOHN','USA'),
(2,'SAM','CANADA'),
(3,'HO','CHINA'),
(4,'RAM','INDIA')

select * from #test1

I have temp table with columns (Id,Name, Country). I want to concatenate column_name with their respective values; means i want column_header with every value for the column and then i want to concatenate multiple columns. I am looking for something like below:

ID values
1,NAME-john-COUNTRY-USA
2,NAME-SAM-COUNTRY-CANADA
3,NAME-HO-COUNTRY-CHINA
4,NAME-RAM-COUNTRY-INDIA

Note: This is just a sample .i am looking something dynamic because i have around 50 tables.i can do with query below but since i don't have static columns and table i am looking for something dynamic

select ID, (a+'-'+B) as Value from (
select ID,'NAME'+'-'+NAME as a,'Country'+'-'+Country as b from #test1

View 2 Replies View Related

Concatenate All Binary Columns Into Single Binary Column?

May 22, 2014

Server is SQL 2000

I have a table with 10 rows with a varbinary column

I wish to concatenate all the binary column into a single binary column and then write that to another table within the database. This application splits a binary file (Word or PDF document) into multiple segments (this is Column2 as below)

example as follows

TableA

Column1 Column2 Column3
aaa 001 <some binary value>
aaa 002 <some binary value>
aaa 003 <some binary value>
aaa 004 <some binary value>
aaa 005 <some binary value>

desired results in TableB

Column1 Column2
aaa <concatenated value of above binary columns>

View 9 Replies View Related

Choosing Between Two Column Values To Return As Single Column Value

Sep 14, 2007

I'm working on a social network where I store my friend GUIDs in a table with the following structure:user1_guid       user2_guidI am trying to write a query to return a single list of all a users' friends in a single column.  Depending on who initiates the friendship, a users' guid value can be in either of the two columns.  Here is the crazy sql I have come up with to give what I want, but I'm sure there's a better way...  Any ideas?SELECT DISTINCT UserIdFROM espace_ProfilePropertyWHERE (UserId IN
(SELECT CAST(REPLACE(CAST(user1_guid AS VarChar(36)) + CAST(user2_guid AS VarChar(36)), @userGuid, '') AS uniqueidentifier) AS UserId FROM espace_UserConnection WHERE (user1_guid = @userGuid) OR
(user2_guid = @userGuid))) AND (UserId IN
(SELECT UserId FROM espace_ProfileProperty))  

View 1 Replies View Related

How To Add All The Values In A Single Column In A Table?

Oct 6, 2005

numbers     2     4     2Above is an example of my table with a single column. My problem is how to add all the numbers in that column to make it 8? The rows are also dynamic. Your help is highly appreciated.

View 4 Replies View Related

Splitting Up Of Values In Single Column

May 21, 2008

Hi,

I have a table that has multiple postal codes in one of the columns. Those have to be split up one per line and stored in another table. The zip codes are comma seperated. Is there a function that can do this...?

Example data in ZipCodeTable. (Name and ZipCode are 2 columns in a table)

NameZipCode
Area119930,19970,19971,19944
Area219934,19938,19901,19903,19904
Area319994,19838

output Table should be:

NameZipCode
Area119930
Area119970
Area119971
Area119944
Area219934
Area219938
Area219901
Area219903
Area219904
Area319994
Area319838

Any thoughts on this would be of much help !!..

Thanks

View 7 Replies View Related

Multiple Values For Single Column In Where Clause

Jun 25, 2004

how does one specify multiple values for a single column in a where clause?

example:

SELECT fname, lname
FROM tblContacts
WHERE (state = 'MI','CA','AZ','TN','NJ')


if my memory serves me there is an IN() value list operator but I can't remember the syntax :confused:

View 2 Replies View Related

Select Comma Separated Values From Single Column

May 27, 2008

Hi,

I have a table -- Table1.
It has two columns -- Name and Alpha.
Alpha has comma separated values like -- (A,B,C,D,E,F), (E,F), (D,E,F), (F), (A,B,C).

I need to pick the values of column -- Name , where in values of Alpha is less than or equal to 'D'.

I tried <=, but got only values less than 'D', but was not able to get equal to 'D'.

Any suggestions??

View 4 Replies View Related

Multiple Rows Into A Single Row And Combining Column Values?

Apr 6, 2014

I joined these two tables and it pulled up the proper amount of records. If you check out the image you will see what the results are for this query.

Now all I need for this part would be to roll these up where I have one row per ProgramID and all the AttributeNames' together in a AttributeNames column for each id.

EXAMPLE: All in one row.

ProgramID | AttributeNames
887 | Studydesign, Control Groups, Primary Outcomes.

I have attached an image of the SQL VIEW that I need to modified so it does this.

THE QUERY:

SELECT TOP (100) PERCENT dbo.tblProgramAttributes.ProgramID, dbo.tblProgramAttributes.AttributeID AS PAattributeID, dbo.tblAttributes.AttributeID,
dbo.tblAttributes.AttributeName
FROM dbo.tblProgramAttributes INNER JOIN
dbo.tblAttributes ON dbo.tblProgramAttributes.AttributeID = dbo.tblAttributes.AttributeID
WHERE (dbo.tblProgramAttributes.AttributeID NOT LIKE '%ProgramType%')
ORDER BY dbo.tblProgramAttributes.ProgramID DESC

View 5 Replies View Related

Removing Duplicate Delimited Values From A Single Column?

Jun 18, 2014

I have a stored procedure that returns a single row based on a parameter of employee ID. This particular procedure uses a CTE to traverse our org structure. One of the columns is returning a delimited string of Windows login values and due to the business rules, it can contain duplicate values. I need to have that column contain only unique values - no dupes.

For example, this one column could contain something like this:

domainuser1;domainuser2;domainuser2;domainuser 3;

The need is to convert to this:

domainuser1;domainuser2;domainuser3;

I know that's a tall order.

View 1 Replies View Related

Transact SQL :: SELECT Unique Values In Single Column?

Jun 8, 2015

I have the following two tables...

tblServer
-serverID
-serverName
-serverLocation
tblSite
-siteID
-serverID
-siteName
-siteIpAddress

I need to write a select query that gets the values of all columns but only returns unique sites because some sites are load balanced across several servers and where this is the case I don't want the site to appear multiple times in the list.

View 4 Replies View Related

How Can I Combine Values Of Multiple Columns Into A Single Column?

Oct 8, 2007



Suppose that I have a table with following values
Table1
Col1 Col2 Col3
-----------------------------------------------------------
P3456 C935876 T675
P5555 C678909 T8888

And the outcome that I want is:
CombinedValues(ColumnName)
----------------------------------------------
P3456 - C935876 - T675
P5555 - C678909 - T8888

where CombinedValues column contains values of coulmn 1,2 & 3 seperated by '-'
So is there any way to achieve this?

View 1 Replies View Related

Query Fails When Returning A Single Record With Numeric Data In NVarChar(16) Column In Device App

Mar 12, 2008

If a Select is done on a column whose data type is nvarchar(16) and contains only numerals (UPC numbers) the select does not return the record.

1. Query with numerals in nvarchar column works as long as multiple records are returned (LIKE '012%')
2. Numeric (INT only one tested) columns works as expected
3. String columns with alpha data works as expected
4. Problem only exist when running in Device Emulator and/or actual device.
5. Same test on desktop app runs as expected.
6. Windows Mobile 6, Vista Ultimate
7. Same results when when connection to device from SSMS
8. SQL Servers comes on

Previous thread discussion of this problem (I thought that Parameters corrected problem, but not in all cases???)

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.ce&mid=0cd9cd3a-f9b0-477f-b1e7-c27eb76158ae

Here is the complete code:


SqlCeConnection _conn = null;

_conn = new SqlCeConnection(@"Data Source=program FilesTestResultSetevsoft.sdf;");

_conn.Open();


// DOES NOT WORK *** This statement does not return the record (it exist)

string _sql = "SELECT * FROM Product where RegDescr='0123456' ";




// works correctly

string _sql = "SELECT * FROM PRODUCT where ProdNum = 6523 ";


// works correctly *** as long as multiple records are returned

string _sql = "SELECT * FROM PRODUCT where RegDescr LIKE '01%' ";

// works correctly


string _sql = "SELECT * FROM PRODUCT where RegDescr='BACARDI SILVER RAZZ'";

SqlCeCommand _cmd = _conn.CreateCommand();

SqlCeDataReader _rdr;

_cmd.CommandText = _sql;

_cmd.CommandType = CommandType.Text;

// Same results using ExecuteResultSet or ExecuteReader

//_rdr = _cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);

_rdr = _cmd.ExecuteReader();



listBox1.Items.Add("In the while loop");

while (_rdr.Read())

{

listBox1.Items.Add(_rdr.GetValue(1) + " / " + _rdr.GetValue(3));

}

listBox1.Items.Add("Done");

View 1 Replies View Related

Print All Column Values For A Record That Has A Mi

May 10, 2007

Hi All -

I have been killing myself over this -

For the attached table,I want to retrieve records that have a minimum Price for all the groups of Category, Symbol, Date


CategorySymbolDate PriceCoupon
1 IBM01/05/20061005
1 IBM01/05/2006995.8
1 IBM01/06/20061005
1 IBM01/06/20069610
2 IBM01/05/20061026
2 IBM01/05/20061055.7

For a given symbol, date and Category, I want to pull in records with the min(Price). My problem is that if I say,

Select min(Price)
,symbol
,date
,Category from TEST group by symbol, date, Category

I lose the coupon information that is attached to the record that has minimum price and maximum price within the group of symbol, date and Category

My results from the above table should be –
CategorySymbolDate PriceCoupon
1 IBM01/05/2006995.8
1 IBM01/06/20069610
2 IBM01/05/20061026

Thanks for all your help.

View 5 Replies View Related

How To Bring Back The Distinct Values In Single Column From Two Tables

Jul 20, 2005

12.) Now you have two different tables - each with two columns.Table #1Single Column2 rows with a value equal to 1 and 2Table #2Single column2 rows with a value equal to 2 and 4Construct a statement returning in a single column all the valuescontained, but not the common values.This is another question that I got in an interview, I missedit.......Thanks,Tim

View 1 Replies View Related

Group By And Count(*) A Single Column Returing Two Counted Values

Feb 26, 2008

I am trying to count a column field in a single table and return two count values as one record set using group by.

field1 = group by (department) nvarachar
field2 = count (closed) datetime

I have tried using derived tables with no luck getting the desired result.

field2 is a datetime field as indicated I want a count for two conditions

1. WHERE field2 is null
2. WHERE field2 is not null

End Results would like this
======
Department | OpenItems | ClosedItems
Department1 | 32 | 24
Departmnet2 | 87 | 46
Department3 | 42 | 76

=======

I got it *almost* working with derived tables, but the group by function was not putting the department as one single row. I was getting multiple rows for departments.

I realize this is probably a simple answer and I am making this a lot harder than it actually is....

Any suggestions?

View 3 Replies View Related

Storing Comma Separated Values In A Single Column Of A Table

Jul 13, 2007

Hi,
I have a table called geofence. It has a primary key geofence_id. Each geofence consists of a set of latitudes and latitudes.
So I defined two columns latitude and longitude and their type is varchar. I want to store all latitude/longitude values as a comma separated values in latitude/longitude columns
So in general how do people implement these types of requirements in relational databases?


--Subba

View 11 Replies View Related

Is There Any Solution For Dispalying String And Percentage Values In Single Column

Dec 1, 2007

i have a report with contains preview of percentage columns example of percentage of student marks in perticular subject like 95%. and if suppose any student not attend any test i have to dispaly like not attended statement.



so i have display two fields like 95% and not attended statement in same column, i given Cstr(Fields!Data.Value), it gives two fields with contains not attended statement of perticular query and it dispalys 0.95 % . but i need 95% and not attended statement for perticular query in same column.


is there any solution for dispalying string and percentage values in single column for given perticular query and those two values are disply same result compare with preview at the time of export to excel sheet



plese send solutions ASAP

Thanks

James vs

View 1 Replies View Related

UPDATE A RECORD FROM COLUMN VALUES OF OTHER TABLE

Jun 10, 2008

HI ALL,
CAN ANYBODY HELP ME TO SOLVE THE ISSUE
I WANNA UPDATE A RECORD OF ONE TABLE WITH VALUES THAT ARE PRESENT IN A COLUMN OF ANOTHER TABLE
OR

CAN I DO THE SAME USING INSERT QUERY

THANKS IN ADVANCE

View 6 Replies View Related

Distinct Record Equal To 2 Values From Same Column

Dec 6, 2013

Distinct name that match both subjects (math, science) from classname in level 2 only. Not sure where to even start. Example table below:

name subject level
bob math 2
hank math 1
joe science 2
bob science 2
joe math 2
ben science 2
carl science 1

View 2 Replies View Related

Concatenate Single Quote...

Mar 3, 2006

Hi,
I am trying to concatenate single quotation mark (ie- ' ) in the select query but not succeed.

If I concatenate word India's, how should I have to write the select query..?

Its just query I have not concern with any specific database.

View 2 Replies View Related

Reporting Services :: SSRS - Summing Up Positive And Negative Values Alone From Single Column?

Sep 18, 2015

MS SQL Server 2008 R2

I have both positive and negative values in a single column, where I want sum total of positive values & negative values. Is there any Expression for this to sort out.

View 8 Replies View Related

Integration Services :: Pass Single Column Of Values From Successful Merge Join To EXECUTE Statement

May 7, 2015

How do I pass a single column of values from a successful merge join to an EXECUTE SQL statement so it can be used with an "IN" criteria of the WHERE clause?  Here's an example of my update statement with two random key values:

UPDATE dbo.MyTable SET MyStatus = 1 WHERE MyPK IN ("XYZ123", "DEF890")

Is this even possible in SSIS, or am I better off using a loop and running the update EXECUTE SQL Statement for each individual key value, as in the following example?

UPDATE dbo.MyTable SET MyStatus = 1 WHERE MyPK = "XYZ123"
UPDATE dbo.MyTable SET MyStatus = 1 WHERE MyPK = "DEF890"

View 6 Replies View Related

Aggregate Function To Concatenate Columns Data Into A Single Row

Aug 9, 2007

Hi all,

I have a scenario which I am not able to figure out how to do it better for quite some time.

Assume I have a few rows of data :
RunningID Date WOid
1234 1/23/2007 23
1236 1/24/2007 23
1239 1/2/2007 24
1222 1/4/2007 23
1321 2/4/2007 22

My objective is to merge all RunningID into a single cell when WOid is the same (this will most probably use a "group by" to get the different WOid out). Maybe some aggregate function that can do it as:
select ReturnConca(RunningID, "#") as RunningID_str, max(Date) as MaxDate, max(WOid) as WO from tableXXX
group by WOid

Results:
RunningID_str MaxDate WO
1234#1236#1222 1/24/2007 23
1239 1/2/2007 24
1321 2/4/2007 22

Any advise would be much appreciated.

View 6 Replies View Related

Multiple Columns With Different Values OR Single Column With Multiple Criteria?

Aug 22, 2007

Hi,

I have multiple columns in a Single Table and i want to search values in different columns. My table structure is

col1 (identity PK)
col2 (varchar(max))
col3 (varchar(max))

I have created a single FULLTEXT on col2 & col3.
suppose i want to search col2='engine' and col3='toyota' i write query as

SELECT

TBL.col2,TBL.col3
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col2,'engine') TBL1
ON

TBL.col1=TBL1.[key]
INNER JOIN

CONTAINSTABLE(TBL,col3,'toyota') TBL2
ON

TBL.col1=TBL2.[key]

Every thing works well if database is small. But now i have 20 million records in my database. Taking an exmaple there are 5million record with col2='engine' and only 1 record with col3='toyota', it take substantial time to find 1 record.

I was thinking this i can address this issue if i merge both columns in a Single column, but i cannot figure out what format i save it in single column that i can use query to extract correct information.
for e.g.;
i was thinking to concatinate both fields like
col4= ABengineBA + ABBToyotaBBA
and in search i use
SELECT

TBL.col4
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABBToyotaBBA"') TBL1
ON

TBL.col1=TBL1.[key]
Result = 1 row

But it don't work in following scenario
col4= ABengineBA + ABBCorola ToyotaBBA

SELECT

TBL.col4
FROM

TBL
INNER JOIN

CONTAINSTABLE(TBL,col4,' "ABengineBA" AND "ABB*ToyotaBBA"') TBL1
ON

TBL.col1=TBL1.[key]

Result=0 Row
Any idea how i can write second query to get result?

View 1 Replies View Related

How Do I Concatenate Different Values For The Same Primary Key Without Using Cursor..

Mar 21, 2002

Can I avoid cursor (use while of if statement instead ) if for example I have two sales people for the same company and I need one row per company?

View 1 Replies View Related

Concatenate List Of Values Under A Group

Aug 4, 2007

I have the following dataset:

State ZIP Homes Schools
WA 98007 2000 4
WA 98052 3000 5
WA 98079 2000 3

Now if I have set the group by expression on State but as display if I want to show it as €œ[98007, 98052, 98079]€? how can I accomplish this.

My report needs to show:
State Homes Schools
[98007, 98052, 98079] 7000 12


Any help will be greatly appreciated

View 6 Replies View Related

Reporting Services :: Count Values In A Column Based Upon Distinct Values In Another Column In SharePoint List

Sep 7, 2015

We have SharePoint list which has, say, two columns. Column A and Column B.

Column A can have three values - red, blue & green.

Column B can have four values - pen, marker, pencil & highlighter.

A typical view of list can be:

Column A - Column B
red  - pen
red - pencil
red - highlighter
blue - marker
blue - pencil
green - pen
green - highlighter
red  - pen
blue - pencil
blue - highlighter
blue - pencil

We are looking to create a report from SharePoint List using SSRS which has following view:

                    red     blue   green
    pen            2       0      1
    marker       0       1      0
    pencil          1       3      0
    highlighter  1       1      1 

We tried Sum but not able to display in single row.

View 2 Replies View Related







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