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

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

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

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

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

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

Combining Two Rows Into One

Jul 23, 2014

I have this data I need to query where if there is more than one startdate for a person, I need to get the earliest startdate, however get the latest enddate and money associated with that enddate. Highlighted in blue is an example of the values I need to return within one record.

Personstartdateenddate Money
7d3397/1/201412/31/2014 1000
7d3391/1/20145/23/2014 355

View 2 Replies View Related

SQL Combining Duplicate Rows, Please HELP.

Feb 1, 2008

Hello,
I have a question, what does a statement look like that finds the duplicate rows and combines them,
I have a table named PRODUCTS in it 3 columbs Cost, Stock, Part_number.
I need to find all Part_numbers that dublicate, Combine the rows into 1 & combine (sum, add) their stock together is the new row & take an avarerage of their cost and use it as cost in the new row where they combine.
Please help me, I am stalled. Looked all over the internet & could not find anything, I really need this for a project I can not finish.
I have the following SQL statement:
SELECT part_number,
COUNT(part_number) AS NumOccurrences
FROM Products
GROUP BY Part_number
HAVING COUNT(part_number) > 1

View 7 Replies View Related

HELP - Combining Rows In A View

Jul 21, 2004

Hi All,

I can do this in Access, with VB, but I'm pretty new to SQL Server.

Say you have the following table, call it TblStudents:

Grade Name
8 John
8 Mike
8 Ed
9 Tom
9 Greg
10 Jack
10 Tony

And you wanted a view that would give you:

Grade Name
8 John, Mike, Ed
9 Tom, Greg
10 Jack, Tony

How would you do this in SQL Server?

Thanks.

Henry

View 1 Replies View Related

Combining Rows In A Table(again)

Jan 18, 2006

I've seen a number of questions on combining rows, but not one
exactly like this. I have a solution, but I'd like to know
if there are other ways.
I'd like to select and combine rows from a table. Here's a simplified
version of the table:
tab1
key date status
1 1/1/06 stat1
1 1/2/06 stat2
1 1/3/06 stat3
1 1/4/06 stat4
2 1/1/06 stat1
2 1/2/06 stat2

And the desired results:
key date status prevstatus
1 1/1/06 stat1 null
1 1/2/06 stat2 stat1
1 1/3/06 stat3 stat2
1 1/4/06 stat4 stat3
2 1/1/06 stat1 null
2 1/2/06 stat2 stat1

Here's the simplified version of the solution:
select
a.*,b.status prevstatus
from
tab1 a
left join
tab1 b
on a.key = b.key and
b.date =
(select max(date) from tab1 c
where
a.key = c.key and
a.date > c.date
)

Is there a better way?

View 5 Replies View Related

Combining Like Rows If More Than One UserID In The List

Jun 6, 2014

I have the following query:

Code:
SELECT DISTINCT [WL].[Id]
,[WL].[UserId]
,[WL].[DIF]
,[WL].[MW]
,[WL].[Notes]
,[WL].[WDate]

[Code] ....

And the error i get is:

> Column 'Wsite.dbo.WLog.Id' is invalid in the select
> list because it is not contained in either an aggregate function or
> the GROUP BY clause.

What I am wanting to do is just conbine the data if there are more than one **UserID** in the list.

As an example:

Code:
Id | UserId | .... | Id | UserName | SLength | ....
5843| 99304 | .... | 99304| Bob Barker | 14 | ....
5844| 06300 | .... | 06300| Dean Martin | 104 | ....
5845| 99304 | .... | 99304| Bob Barker | 8 | ....
5846| 99304 | .... | 99304| Bob Barker | 11 | ....
5847| 7699 | .... | 7699 | John Doe | 0 | ....

So it should look like this:

Code:
Id | UserId | .... | Id | UserName | SLength | ....
5843| 99304 | .... | 99304| Bob Barker | 33 | ....
5844| 06300 | .... | 06300| Dean Martin | 104 | ....
5847| 7699 | .... | 7699 | John Doe | 0 | ....

Notice that Bob Barker's SLength was combined (14+8+11=33).

View 1 Replies View Related

Select Statement Combining Rows

Mar 30, 2008

I need some help with a query . I have two tables "config" and "item".

I have table config as follows:
ConfigID
ItemID

With Values
ConfigID ItemID
14583 2776
14583 2798
14583 3112

And table item as follows:
ItemID
ItemTypeID
ItemValue

With Values
ItemID ItermTypeID ItemValue
2776 1 123
2798 2 ABC
3112 3 789

So the query:
SELECT ConfigID,
(SELECT ItemValue WHERE ItemTypeID = '1') AS Model,
(SELECT ItemValue WHERE ItemTypeID = '3') AS Minor,
(SELECT ItemValue WHERE ItemTypeID = '2') AS Customer
FROM config c, item i
WHERE ConfigID = '14583'
AND c.ItemID = i.ItemID

Produces the result:
ConfigID Model Minor Customer
14583 123 NULL NULL
14583 NULL NULL ABC
14583 NULL 789 NULL

How do I change the above query to get one row:
ConfigID Model Minor Customer
14583 123 789 ABC

Thanks for your help

View 6 Replies View Related

Combining Text Data Rows

Jun 4, 2006

I 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?

View 17 Replies View Related

Combining Multiple Results Into One Row

Mar 6, 2006

Hey guys, here is my issue, i'm trying to combine multiple columns of data into one row. For example, I have a temp table:

create table #Custom_Address
( patient_idchar(10)null,
episode_idchar(3)null,
custom_address varchar(100) null
)

As you can tell, the 'custom_address' column is going to house the results of the combination.

I am trying to INSERT (combine) using this statement:

INSERT #Custom_Address
select cm.patient_id, cm.episode_id, (cc.coverage_plan_add1 +' ' + coverage_plan_add2 + ' ' + coverage_plan_city+ ' ' + coverage_plan_st+ ' ' + coverage_plan_zip) as custom_address
FROM #ClaimMaster cm join Coverage_Custom cc on cm.patient_id = cc.patient_id and cm.episode_id = cc.episode_id
WHEREcc.coverage_plan_add1 > 0
OR cc.coverage_plan_add2 > 0
OR cc.coverage_plan_city > 0
OR cc.coverage_plan_st > 0
OR cc.coverage_plan_zip > 0
GROUP BY cm.patient_id, cm.episode_id

I want to insert the patient_id, episode_id, and then combine the 'cc.coverage_plan_add1 +' ' + coverage_plan_add2 + ' ' + coverage_plan_city+ ' ' + coverage_plan_st+ ' ' + coverage_plan_zip' data to represent my "custom_address" column and only when there is data in those columns.

Then I do my update:

UPDATE#ClaimMaster
SET #ClaimMaster.custom_address = ca.custom_address
FROM #ClaimMaster
JOIN#Custom_Address ca on #ClaimMaster.patient_id = ca.patient_id and #ClaimMaster.episode_id = ca.episode_id

However, when I do this, it says my 'cc.coverage_plan_add1 + etc' columns are invalid because they are not contained in either an aggregate function or a GROUP By clause.

If i'm combining all the data to represent a single column, how do I format my group by clause? Or is my entire INSERT statement wrong?

As a side note, my #ClaimMaster table is another temp table, but the problem is not in there.

View 5 Replies View Related

Combining Multiple Records

Dec 19, 2014

I have data that looks like this:

Cus_no Inv_No Trans_type Amt_Inv Amt_Paid
100 12345 Other 0 -21.76
100 12345 Discount 0 -6.39
100 12345 Discount 6.39 6.39
100 12345 Discount 21.76 21.76
100 12345 Sales Inv 218.99 218.99

What I would like to do is a select statement that returns:

Cus_no Inv_no Amt_Inv Amt_Paid Disc_amt Other
100 12345 247.14 218.99 -6.39 -21.76

I've tried something like this but since I'm referencing trans_type I get a message that trans_type must be in Group by. doing that give me multiple records.

select cus_no, Inv_no, Sum(Amount_Invoiced_DC) AS InvAmt,
case trans_type when 'Sales Inv' then sum(Amount_Paid_DC) else 0 end as AmtPaid,
case when trans_type = 'Discount' and sum(Amount_Paid_DC)<0 then sum(Amount_Paid_DC) else 0 end as DiscountAmt
FROM BI50_BankTransactions_AR_InvcDt_H
where cus_no is not null
group by cus_no, Inv_no

View 2 Replies View Related

Combining Multiple Tables - Please Help!

May 4, 2007

Hii all.

quite urgent... I have 3 matix tables.. all the same row headings. I need to be able to make these visible/invisible depending on the user parameters. The reason for this, if a user hides table on the left.. the middle table needs to show the row headings. and last table must not.

The problem:
When making the row headings invisible it doesnt exactly chop off the textboxes... so the tables look disjoint.. and a big gap appears between both tables...

Is there a way to join these tables without leavings gaps ?

any help is appreciated.
Neil

View 7 Replies View Related

Combining Multiple Sources Into 1 Row

Apr 7, 2008

OK I have 4 differant Data Sources... One being a count of one DB, another count of another DB, another count of another and then another process from a script component. Each source returns 1 row of data with 1 column each except the Script Component. It returns 3 columns... Now I need to take each of the row's returned and combine them to a single row (line) and inset them into another table just as one single eatry. I am using a Union All and when it runs I see the 4 Data Sources say 1 Row... But after it hits the Union All it does 4 rows... What am I doing wrong or am I using the wrong component? Please if anyone can help that would be wonderful.

View 10 Replies View Related

Combining Multiple Select Statements In A SP

Jul 27, 2007

I was wondering if it's possible to have a stored procedure that has two select statements which you can combine as a single result set. For instance:select name, age, titlefrom tableaselect name, age, titlefrom tablebCould you combine these queries into a single result set?

View 2 Replies View Related

Combining Information From Multiple Records Into One

Nov 21, 2007

Hi,

I am trying to combine information from two or more records into one and I am completely stuck on a solution for my problem so I hope there is someone out there who can help me.

My table looks like this:
ID - DayNr - Transportation - TransOrder - Route
25 - 1 - Car - 1 - Text A
25 - 1 - Train - 1 - Text B
25 - 1 - Train - 2 - Text C
25 - 7 - Train - 1 - Text D
25 - 7 - Train - 2 - Text E

I want to combine all Route - information belonging to the same combination of ID & DayNr & Transportation into one new record. The result should look like:

Column 1 - Column 2
25/1/Car - Text A
25/1/Train - TextB;TextC
25/7/Train - TextD;TextE

I have tried Coalesce-statements and Cursor-solutions but until now everything I tried didn't work. Ideas anyone?

Thanks.
RMG

P.S. ID is not my primary key and doesn't have to be unique

View 14 Replies View Related

Combining Multiple Queries From Same Table

Oct 17, 2013

I have 3 queries pulling from the same table, trying to define a count on each criteria. I have the data pulling, but the data is in multiple rows. I want the data in one row with all the counts in each separate columns. Also I need to setup a flag if a client purchased and order within 30 days from their last purchase.

I am doing this select for each credit card, check and cash purchases. I do not know how to setup a flag where the client may have ordered and paid by check or cash after 30 days from a credit card purchase. Is this something that can be done?

select
clientnumber,count(distinct clientnumber) as cccnt,
0 as ckcnt, 0 as cacnt
from dbo.purchases
where orderdate >= 20120101 and orderdate <= 20121231 and
payment_type = 'CC'
group by clientnumber;

OUTPUT currently looks like this:
1234 2 0 0
1234 0 1 0
1234 0 0 4

Is it possible to result in this, along with a flag with the criteria above?:
1234 2 1 4 Y

View 3 Replies View Related

Combining Multiple Records Into One Record

Dec 20, 2007

Hi All,

I'm trying to develop a query that joins one record from a table with multiple matching records from another table all in one record,
Table1 has the primary key
id
--
1
2
3
4
Table2 has the follwing records
id year subject
-----------------
1 2000 English
1 2002 French
2 2004 English
2 2005 English
2 2006 English
3 2007 French
I want the result to be like this
id 2000 2001 2002 2003 2004 2005 2006 2007
-----------------------------------------------------------
1 English null French null null null null null
2 null null null null English English English null
3 null null null null null null null English

Appretiate your assistance

View 4 Replies View Related

Combining Information From Multiple Records Into One

Nov 21, 2007

Hi,

I am trying to combine information from two or more records into one and I am completely stuck on a solution for my problem so I hope there is someone out there who can help me.

My table looks like this:
ID - DayNr - Transportation - TransOrder - Route
25 - 1 - Car - 1 - Text A
25 - 1 - Train - 1 - Text B
25 - 1 - Train - 2 - Text C
25 - 7 - Train - 1 - Text D
25 - 7 - Train - 2 - Text E

I want to combine all Route - information belonging to the same combination of ID & DayNr & Transportation into one new record. The result should look like:

Column 1 - Column 2
25/1/Car - Text A
25/1/Train - TextB;TextC
25/7/Train - TextD;TextE

I have tried Coalesce-statements and Cursor-solutions but until now everything I tried didn't work. The big issue here is that I have to base my concatenation on 3 columns. Ideas anyone?

Thanks.
RMG

P.S. ID is not my primary key and doesn't have to be unique

View 1 Replies View Related

SQL 2012 :: Combining Multiple Servers Into Instances

Oct 14, 2015

Curious what the industry standard is combining multiple instances on single server ?

Right now i have separate servers for OLTP, SSAS, and SSIS, and for OTP have Development, Test, and production environments. Im considering combining the SSIS and SSAS services into additional instances on each environment servers.

Now (Production)
OLTP
SSAS
SSIS

After

1 Server with 3 separate instances for SSAS, SSIS, OLTP. Then replicate this model through the environments to utilize development and testing.

View 2 Replies View Related

Transact SQL :: Combining Multiple Similar Queries

Nov 20, 2015

I 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.

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

Easiest Way Of Combining Multiple Fields From Different Records Into One Record?

Jul 20, 2005

I have a table;CREATE TABLE theLiterals (theKey varchar (255) NOT NULL ,theValue varchar (255) NULL)INSERT INTO theLiterals VALUES('defaultServer','\MyServer')INSERT INTO theLiterals VALUES('defaultShare','MyShare')INSERT INTO theLiterals VALUES('defaultFolder','MyFolder')INSERT INTO theLiterals VALUES('defaultFile','MyFile.dat')I then try;SELECTdefaultServer = CASE WHEN theKey = 'defaultServer' THEN theValue END,defaultShare = CASE WHEN theKey = 'defaultShare' THEN theValue END,defaultFolder = CASE WHEN theKey = 'defaultFolder' THEN theValue END,defaultFile = CASE WHEN theKey = 'defaultFile' THEN theValue ENDFROM theLiteralsand I get;defaultServer defaultShare defaultFolder defaultFile\MyServer NULL NULL NULLNULL MyShare NULL NULLNULL NULL MyFolder NULLNULL NULL NULL MyFile.datbut I want it COALESCEd like this;defaultServer defaultShare defaultFolder defaultFile\MyServer MyShare MyFolder MyFile.dat....but my syntax is incorrect. Is there an efficient way of doing this.I want to have a script/UDF where I can say...GetLiteralsFor('defaultServer','defaultShare','def aultFolder','defaultFile')and then my one-row recordset will be...RS(0) will = '\MyServer'RS(1) will = 'MyShare'RS(2) will = 'MyFolder'RS(3) will = 'MyFile.dat'Thanks for any help!

View 5 Replies View Related

Merge Multiple Rows Into A One Or More Rows With Multiple Columns

May 7, 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

ID Pat_ID

1 A

2 A
3 A
4 A
5 A
6 B

7 B
8 B
9 C

10 D

11 D

I want output for the above as:

Pat_ID ID1 ID2 ID3
A 1 2 3
A 4 5 null
B 6 7 8
C 9 null null
D 10 11 null

Please help me. Thanks!

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

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