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

Transact SQL :: Converting From Multiple Rows With Single Values To Single Rows With Multiple Values

May 10, 2015

Here is some data that will explain what I want to do:

Input Data:
Part ColorCode
A100 123
A100 456
A100 789
B100 456
C100 123
C100 456

Output Data:
Part ColorCode
A100 123;456;789
B100 456
C100 123;456

View 4 Replies View Related

Combining Multiple Rows Into 1 Row X 1 Column

Jul 30, 2007

I have a table employee: that contains one column and three rows. How can I transform it using SELECT to display only one row and one column, with comma delimited strings: John, Mike, Dale?

Employee Name

John

Mike

Dale

View 5 Replies View Related

Help With Combining Data From Multiple Rows Into One Column In A View

Jul 19, 2007

Hi, I am stumped and was hoping someone could help me out. Any help isappreciated.I have a view that looks sort of like this (but with a lot moreentries of course)UniqueIdentifyierColumn1Column21 9999 1002 9999 2003 9999 300What I want to do is to add a column to the view that will contain alist of the values from column 2 where column 1 is the same.UniqueIdentifyierColumn1Column2Column31 9999100100, 200, 3002 9999200 100, 200, 3003 9999300100, 200, 300

View 1 Replies View Related

How To Merge Multiple Rows One Column Data Into A Single Row With Multiple Columns

Mar 3, 2008

Please can anyone help me for the following?

I want to merge multiple rows (eg. 3rows) into a single row with multip columns.

for eg:
data

Date Shift Reading
01-MAR-08 1 879.880
01-MAR-08 2 854.858
01-MAR-08 3 833.836
02-MAR-08 1 809.810
02-MAR-08 2 785.784
02-MAR-08 3 761.760

i want output for the above as:

Date Shift1 Shift2 Shift3
01-MAR-08 879.880 854.858 833.836
02-MAR-08 809.810 785.784 761.760
Please help me.

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

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

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

Combine Multiple Rows To Single Column

Jul 8, 2014

With the below query iam able to retrieve all the tables invloved in a stored proc. But, what I want to display the table names as comma separated list for each table.

;WITH stored_procedures AS (
SELECT o.id,
o.name AS proc_name, oo.name AS table_name,
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row
FROM sysdepends d
INNER JOIN sysobjects o ON o.id=d.id
INNER JOIN sysobjects oo ON oo.id=d.depid
WHERE o.xtype = 'P')
SELECT id,proc_name, table_name FROM stored_procedures
WHERE row = 1
ORDER BY proc_name,table_name

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

T-SQL (SS2K8) :: Convert Multiple Rows In Single Column

Sep 4, 2014

Scenario is like that single dept can have multiple LocationHeads, If Location heads are multiple then they should display in single column using *starting the name as mentioned bottom under required output.

Below is sample of data:

create table #Temp(depID int, Name varchar(50),LocationHead varchar(50))
insert into #temp values(1,'test','head1')
insert into #temp values(1,'test','head2')
insert into #temp values(1,'test','head3')
insert into #temp values(2,'test1','head1')
insert into #temp values(2,'test1','head2')

Required output

depID Name LocationHead
1test *head1,*head2,*head3
2test1 *head1,*head2

View 2 Replies View Related

SQL Server 2008 :: Split Single Row Into Multiple Rows Based On Column Value (quantity)

Jan 30, 2015

Deciding whether or not to use a CTE or this simple faster approach utilizing system tables, hijacking them.

SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE
FROM @SPLITROW s
INNER JOIN master.dbo.spt_values t ON t.type='P'
AND t.number BETWEEN 1 AND s.QTY

Just wanted to know if its okay to use system tables in a production environment and if there are any pit falls of using them ?

View 1 Replies View Related

Combining Multiple Subreports Into A Single Report

Oct 2, 2006

The goal is to produce a single PDF consisting of a number of subreports. Some are landscape, others are portrait. The subreports may also be run as independent reports. The master report that contains them defaults to the width of the widest subreport, which is landscape. This causes all portrait subreports to spill over producing blank pages. Are there any work-arounds to concatenate multiple, single report PDFs into a single PDF and have page numbering too?

Thanks!

View 11 Replies View Related

Combining Multiple Tables Into A Single Flat File Destination

Aug 21, 2007

Hi,

I want to combine a series of outputs from tsql queries into a single flat file destination using SSIS.

Does anyone have any inkling into how I would do this.

I know that I can configure a flat file connection manager to accept the output from the first oledb source, but am having difficulty with subsequent queries.

e.g. output

personID, personForename, personSurname,
1, pf, langan

***Roles
roleID, roleName
1, developer
2, architect
3, business analyst
4, project manager
5, general manager
6, ceo

***joinPersonRoles
personID,roleID
1,1
1,2
1,3
1,4
1,5
1,6

View 9 Replies View Related

Combining Multiple Rows Into One

Feb 13, 2007

I have data that looks like this:

ID Value
1 Descr1
1 Descr2
1 Descr3

where Descr could range from 1 to 100 for each ID

The result set I need is:
Descr1,Descr2,Desc3...etc.
Does someone have a query to do this?
Thank you

View 8 Replies View Related

Combining Multiple Rows Into One

May 16, 2008

Hi,

I'm trying to do this in T-SQL. I have a query that returns, for this matter, only 2 rows. The table output looks like this:

AdjType
AdjNbrStart
AdjNbrEnd
AdjTotalAmt

1
9
9
-134180

2
8
10
104981.42

How do you do it to bring back only one row that will look like so:

1
9
9
-134180
2
8
10
104981.4

Is it possible? Like I said, I only have 2 rows. I'm not concatenating these columns, just want to bring it back as one row. Any ideas? Thanks.

View 1 Replies View Related

Combining Multiple Rows Based Off Criteria

Mar 21, 2006

Hello again,

Another combining multiple rows teaser, during a few routines I made a mistake and I would like to combine my efforts. Here is my data:

Code:

Table A

ID DSN VN AX Diag
1111296.54
3212318.00

Both DSNs share the same Patient_id in a seperate table which holds the DSN numbers and their corresponding patients.

Code:

Table B

DSN Patient_id
100000001
200000001

So what I need to do is maintain their unique 'ID' number in Table A but update their DSN numbers to reflect the first instance in Table B. So my data would look like this in both tables.

Code:

Table A

ID DSN VN AX Diag
1111296.54
3112318.00

Note: The second rows DSN changed to 1 from 2

Code:

Table B

DSN Patient_id
100000001
(Duplicate row removed with same patient_id)

The result would look like the above but as you noticed I need to remove the duplicate row that had the different DSN in Table B so that only one DSN remains that can map to multiple rows (IDs) in Table A.

Table A:

DSN can map to multiple rows (IDs)
IDs must be unique (aka kept to what they are currently)

Table B:

Second row with same DSN must be removed.

Any takes, ideas? I need to do this on a couple thousand rows....

Thanks, and im happy to clarify if needed.

View 1 Replies View Related

T-SQL (SS2K8) :: Combining Multiple Rows Into One Row Per Employee

Apr 3, 2014

I'm working on a project where I need to retrieve employees data and then combine the data into single row per employee.

Sample Data:

WITH SampleData (PERSON, [DATA], [FIELD]) AS
(
SELECT 1234,'04/02/2014','Date'
UNION ALL SELECT 1234,'123','Department'
UNION ALL SELECT 1234,80.0,'Rate'
)
SELECT *
FROM SampleData;

The results from the above are as follows:

PERSONDATA FIELD
123404/02/2014Date
1234123 Department
123480.0 Rate

The desired results would be:

PERSONDate Department Rate
123404/02/2014 123 80.0

View 7 Replies View Related

SQL Server 2012 :: Combining Multiple Rows Into One

Feb 4, 2015

How I could accomplish taking several rows for one account and concatenate them into one row, for example I have account_num, invoice_date, transaction_num, msg_counter,Message_2,SQL_LAST_UPDATE the special characters &,",!,$,# are used to determine the Message_2 content for a given account_number that are supposed to be together.

I am needing to put all of that accounts_messages in one row to display on a report, the table I am pulling this data from only has a varchar(40) for the message_2, a proprietary source so can't change that length, "I'VE ASKED THEM TO DO THIS, AND THEY REFUSED". So my only option is to insert this data into my table and create a single Message_2 for that account.

00000000332015-01-16 10:09:43.00000&19 confirmation so 2015-01-19 15:34:59.000
00000000332015-01-16 10:09:43.00000"19ACCT 186743. HE SAID RADIO HAD 2015-01-19 15:34:59.000
00000000332015-01-16 10:09:43.00000!19CALLED Carl ABOUT DEACTIVATION OF RADIO 2015-01-19 15:34:59.000
00000000332015-01-16 10:09:43.00000$19FFERENT ACCT # YEARS AGO, BUT 2015-01-19 15:34:59.000
00000000332015-01-16 10:09:43.00000'19I can cancel the (0.00) billing line on 2015-01-19 15:34:59.000

[Code] ....

View 7 Replies View Related

SQL Server 2012 :: Combining Multiple Rows Into One Field

Feb 26, 2015

I am needing to combine the Notes field where Number and date are the same...For example

for Number 0000000003 I need notes to Read ('CHK # 2452 FOR $122.49 REJECTED AS NSF ON 2/25/15') the note counter is different for each row, and is combination of special char, 0-Z and looks like the (!) depicts the start of a new Number.

CREATE TABLE [dbo].[MyTable](
[NUMBER] [varchar](10) NULL,
[HD_DATE_TIMEX] [datetime] NULL,
[TRANS_NO] [varchar](2) NULL,
[MESSAGE_COUNTER] [varchar](1) NULL,

[Code] .....

View 9 Replies View Related

Combining Results Of Multiple Rows Based On Group?

Jul 17, 2013

I have a table of attributes set up as follows:

ID, Value, Group
1, Football, Sports
1, Baseball, Sports
1, Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks
etc.

I want a query that will combine that values for each ID into one field per group. So if ID 1 has multiple sports but also a greek attribute, they end up with two rows; the first row containing the combined sports values and the second row the greek valued not combined, because there was only one value in that group for that ID. For example:

ID, Combined Values, Group
1, Football Baseball Basketball, Sports
2, Baseball, Sports
3, Football, Sports
1, Lambda Sigma, Greeks
2, Delta Delta, Greeks

View 5 Replies View Related

SQL Server 2008 :: Combining Multiple-same Records With Different Values Into One Record

Jun 25, 2015

I have a question about combining multiple records with same ID but different values between records on different fields into a single record with all values combined for all fields. Example: Multiple records with the same record ID:

RECORD_ID BO_ID Code Code_Date SubMsgCD1 SubMsgCD2 LNMsgCD1
1380900 XZ01 12 1/6/2015 P302
1380900 XZ01 12 1/6/2015 L405
1380900 XZ01 12 1/6/2015 P302 1004

INTO a single record:

RECORD_ID BO_ID Code Code_Date SubMsgCD1 SubMsgCD2 LNMsgCD1
1380900 XZ01 12 1/6/2015 P302 L405 1004

View 2 Replies View Related

Transact SQL :: Query To Convert Single Row Multiple Columns To Multiple Rows

Apr 21, 2015

View 6 Replies View Related

Insert Single Row / Multiple Rows Into Multiple Tables

Sep 3, 2014

How to insert single row/multiple rows into multiple tables by using single insert statement.

View 1 Replies View Related

Combining Column Values Into One Column

Oct 30, 2007

Hi Folks, Im new to SQL, and I am trying to do the following:

I have a table Documents with DocID, Path and FileName.
A second table Keywords has KwdID, KeywordString
A third table DocumentKeywords links the two with DocID,KwdID. Multiple keywords are linked to one document.

I want to create a SELECT query that makes a result table that contains Path, FileName and Keywords columns where the Keywords column contains entries like "Keyword1,Keyword2,Keyword3" ie. a comma delimited list of keyword strings which have been built from the keywords that associate with a specific document.

I found a nice sample here
http://www.sqlteam.com/article/using-coalesce-to-build-comma-delimited-string
which shows how to return just the comma delimited string itself:

DECLARE @List varchar(100)

SELECT @List = COALESCE(@List + ', ', '') + Keywords.KeywordString
FROM DocumentKeywords
WHERE KwdID = 1

SELECT @List

I cannot seem to integrate this into the query so that it calculates the string for each row on the fly. My suspicion is that the capability is there. Can somebody point me in the right direction?

Thanks

View 4 Replies View Related

Multiple Values For Single Row

Mar 19, 2007

hi iam totally new to databases , as a project i have to design a database of users...they have to register first like any site..so i used stored procs and made entries to database using insert command...its working for now..now every user will search and add other users in the database..so every user will have a contact list...i have no idea how to implement this...so far i created a table 'UserAccount' with column names as UserName as varchar(50)Password as varchar(50)EmailID as varchar(100)DateOfJoining as datetimeUserID as int ---> this is unique for user..i enabled automatic increment..and this is primary key..so now every user must have a list of other userid's.. as contact list..Any help any ideas will be great since i have no clue how to put multiple values for each row..i didnt even know how to search for this problems solution..iam sorry if this posted somewhere else..THANK YOU !if it helps..iam using sql server express edition..and iam accessing using asp.net/C#

View 1 Replies View Related

Single Row Into Multiple Rows

May 8, 2008

Hi All,

We've a table as in the following format:

PK_Column1
PK_Column2
Issue_Date1
Issue_Amount1
Issue_Category1
Issue_Reject1
Issue_Date2
Issue_Amount2
Issue_Category2
Issue_Reject2

We need to divide it into two new tables as follows:

UniqueID
PK_Column1
PK_Column2
And

UniqueID
PK_Column1
PK_Column2
Sequence_ID
Issue_Date
Issue_Amount
Issue_Category
Issue_Reject

Unique1

1
Issue_Date1
Issue_Amount1
Issue_Category1
Issue_Reject1

Unique2

2
Issue_Date2
Issue_Amount2
Issue_Category2

Unique3

1
xx

xx

Unique4

2
xx

xx

Unique5

3
xx

4

xx

There will be one UniqueID for each row.
We'll get the uniqueID and PK1 and PK2 in a file.
Imp: We need to generate the Sequence_Id depending on number of Issue_dates or Issue_amounts or Issue_Categories or Issue_Rejects as in the above table.

Can we do this without using cursors?
This is going to be one time process.

Any ideas are appreciated.

Thanks,
Siva.

View 1 Replies View Related

Getting Multiple Rows In A Single Row

Jan 21, 2008

Hi,

I've a temp variable where I'm moving some columns like below:

id
value
type1
type2

0
ab
type1val1
type2val1

0
cd
type1val1
type2val1

0
ef
type1val1
type2val1

1
ab
type1val2
type2val2

1
cd
type1val2
type2val2

1
ef
type1val2
type2val2
What I want to do is group these by their id and get the following o/p
ab,cd,ef type1val1 type2val1
ab,cd,ef type1val2 type2val2

The grouped values need to be separated by commas.

What I'm doing currently:
I'm using a temp variable to put all these values but am unable to coalesce and get the desired o/p.

Can anybody help me out?

Thanks,
Subha

View 4 Replies View Related

Transact SQL :: Selecting Rows As Column Name And Other Column Values As Rows

Jun 25, 2015

I have questions and answers from one table, I need to select questions as column names and answers column values as the results for the questions column.

View 28 Replies View Related

Store Multiple Values In A Single Value

Nov 6, 2005

was hoping someone couild provide some insight into a problem I'm trying to solve.

I have a table called SEARCHCRITERIA. It consists of a USERID column and a CRITERIA column. Users will be able to search for other users based on a set of criteria. There are 5 total criteria a user can choose. They can choose as few as none or all five. I'd like to store the criteria chosen as a single number in the SEARCHCRITERIA table. Then use a function to parse out the criteria. For example:

CRITERIAID CRITERIA CRITERIAVALUE
1 AGE 2
2 SEX 4
3 GRADE 8
4 LOCALE 16
5 REGION 32

A user performs a search based on AGE, SEX, and LOCALE. I would then store the value 22 (the sum of 2, 4, and 16) in the SEARCH table. I would then need a function to pull out the three individual values.

Has anyone done anything like this before?

If so, any help would be appreciated!

Thanks in advance!

View 1 Replies View Related

Multiple Values Into Single Cell

May 18, 2007

Taking the Northwind database as an example,I have an order table: 'Orders'a order details table: 'Order Details'and a products table: 'Products'For reasons best ignored, I want to produce a query which has columns:Orders.OrderID, Productswhich has results like:10248, 'Queso Cabrales, Singaporean Hokkien Fried Mee, Mozzarella diGiovanni'10249, 'Tofu, Manjimup Dried Apples'________so for those who don't really know what I'm on about and haven't gotaccess to northwind, I want the second cell to contain the returnedcolumn of a second query (but in text format)could anyone suggest a way this could be done? am I entering the landof cursors?Many thanks,Neil

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

Spliting Single Row Into Multiple Rows

Sep 20, 2006

I have a table that contains many columns in a single row and I'd like to split the table so that it has fewer column values and more rows.
My table structure is:
create table #scoresheet
(Decisions varchar(10), DNumericalValue int, DVI varchar(10), DComments nvarchar(255),
Competence varchar(10), CNumericalValue int, CVI varchar(10), CComments nvarchar(255),
Equipment varchar(10), ENumericalValue int, EVI varchar(10), EComments nvarchar(255));
I would like to have three rows with four columns.
What I've done so far is create a stored procedure that uses a table variable:
create procedure sp_splitsinglerow as

declare @Scoresheet_rows_table_var table (
ReviewArea varchar(25),
NumericalValue int,
VI varchar(10),
Comments nvarchar(255));
insert into @Scoresheet_rows_table_var
(ReviewArea, NumericalValue, VI, Comments)
select Decisions, DNumericalValue, DVI, DComments
from #scoresheet

The trouble with this approach is that I have to explicitly name the columns that I insert into the table variable. What I'd really like to be able to is have a loop construct and select the first 4 columns the first time, the second 4 the next time and the last 4 the third time.

Any ideas on how to achieve that?

BTW, I have resolved this issue by suggesting to the Developers that they change the structure of the original table, but I'd still like to know if there is another solution. :)

View 2 Replies View Related