Showing posts with label rows. Show all posts

Thursday, March 29, 2012

Combining two rows into one

I am trying to combine two rows of data into one row for comparison
purposes and I can do it using a number of steps but thought that there
had to be a way to do it in one SQL statement. Any help would be
appreciated...
Instead of having two rows with the different period end dates, I would
like the query to return the results as follows:
Name, Ticker, CIK, PeriodEndDate, PeriodEndDateLastYear,
NetIncomeCurrentYear, NetIncomeLastYear, OpCashFlowCurrentYear,
OpCashFlowLastYear... All in one row.
Table:
CREATE TABLE [dbo].[CompanyRatios_3Y] (
[Name] [varchar] (160),
[Ticker] [varchar] (10),
[CIK] [varchar] (10),
[PeriodEndDate] [datetime],
[DurationType] [varchar] (3),
[NetIncome] [decimal](38, 6) NULL ,
[OperatingCashFlow] [decimal](38, 6) NULL ,
[TotalAssets] [decimal](38, 6) NULL ,
[TotalRevenue] [decimal](38, 6) NULL
) ON [PRIMARY]
GO
Sample Data:
INSERT INTO CompanyRatios_3
(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
OperatingCashFlow, TotalAssets, TotalRevenue)
VALUES ('ABC Company', 'ABC', '00112233', CONVERT(DATETIME, '2005-12-31
00:00:00', 102), 'TTM', 12345, 23456, 45678, 56789)
INSERT INTO CompanyRatios_3
(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
OperatingCashFlow, TotalAssets, TotalRevenue)
VALUES ('ABC Company', 'ABC', '00112233', CONVERT(DATETIME, '2004-12-31
00:00:00', 102), 'TTM', 23456, 11111, 11111, 22222)
INSERT INTO CompanyRatios_3
(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
OperatingCashFlow, TotalAssets, TotalRevenue)
VALUES ('XYZ Company', 'XYZ', '00332244', CONVERT(DATETIME, '2005-12-31
00:00:00', 102), 'TTM', 22222, 33333, 44444, 55555)
INSERT INTO CompanyRatios_3
(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
OperatingCashFlow, TotalAssets, TotalRevenue)
VALUES ('XYZ Company', 'XYZ', '00332244', CONVERT(DATETIME, '2004-12-31
00:00:00', 102), 'TTM', 33333, 44444, 55555, 66666)
*** Sent via Developersdex http://www.examnotes.net ***try this.
select
a.Name, a.Ticker, a.CIK, a.PeriodEndDate, b.PeriodEndDate as
PeriodEndDateLastYear,
a.Netincome as NetIncomeCurrentYear, b.Netincome as NetIncomeLastYear,
a.operatingcashflow as OpCashFlowCurrentYear,
b.operatingcashflow as OpCashFlowLastYear
from CompanyRatios_3 a
,CompanyRatios_3 b
where a.name = b.name
and a.ticker = b.ticker
and a.cik = b.cik
and year(a.PeriodEndDate) = year(b.periodEndDate) + 1|||Here is one approach, but it makes some assumptions about that data
that I am not comforatble with:
SELECT C.Name,
C.Ticker,
C.CIK,
C.PeriodEndDate,
PeriodEndDateLastYear = P.PeriodEndDate,
NetIncomeCurrentYear = C.NetIncome,
NetIncomeLastYear = P.NetIncome,
OpCashFlowCurrentYear = C.OperatingCashFlow,
OpCashFlowLastYear = P.OperatingCashFlow
FROM CompanyRatios_3 as C -- as in Current
JOIN CompanyRatios_3 as P -- as in Prior
ON C.Ticker = P.Ticker
AND C.CIK = P.CIK
WHERE C.PeriodEndDate = '20051231'
AND P.PeriodEndDate = '20041231'
The problem is that there must be EXACTLY the same Ticker and CIK
values for both years, or you don't get any data for either year.
This can be allowed for, at the price of some complication:
SELECT K.Name,
K.Ticker,
K.CIK,
C.PeriodEndDate,
PeriodEndDateLastYear = P.PeriodEndDate,
NetIncomeCurrentYear = C.NetIncome,
NetIncomeLastYear = P.NetIncome,
OpCashFlowCurrentYear = C.OperatingCashFlow,
OpCashFlowLastYear = P.OperatingCashFlow
FROM (select distinct Name, Ticker, CIK
from CompanyRatios_3) as K -- for Key
LEFT OUTER
JOIN CompanyRatios_3 as C -- as in Current
ON K.Ticker = C.Ticker
AND K.CIK = C.CIK
AND C.PeriodEndDate = '20051231'
JOIN CompanyRatios_3 as P -- as in Prior
ON K.Ticker = P.Ticker
AND K.CIK = P.CIK
AND P.PeriodEndDate = '20041231'
Roy Harvey
Beacon Falls, CT
On Tue, 02 May 2006 13:25:39 -0700, Jason . <jrp210@.yahoo.com> wrote:

>I am trying to combine two rows of data into one row for comparison
>purposes and I can do it using a number of steps but thought that there
>had to be a way to do it in one SQL statement. Any help would be
>appreciated...
>Instead of having two rows with the different period end dates, I would
>like the query to return the results as follows:
>Name, Ticker, CIK, PeriodEndDate, PeriodEndDateLastYear,
>NetIncomeCurrentYear, NetIncomeLastYear, OpCashFlowCurrentYear,
>OpCashFlowLastYear... All in one row.
>Table:
>CREATE TABLE [dbo].[CompanyRatios_3Y] (
> [Name] [varchar] (160),
> [Ticker] [varchar] (10),
> [CIK] [varchar] (10),
> [PeriodEndDate] [datetime],
> [DurationType] [varchar] (3),
> [NetIncome] [decimal](38, 6) NULL ,
> [OperatingCashFlow] [decimal](38, 6) NULL ,
> [TotalAssets] [decimal](38, 6) NULL ,
> [TotalRevenue] [decimal](38, 6) NULL
> ) ON [PRIMARY]
>GO
>Sample Data:
>INSERT INTO CompanyRatios_3
>(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
>OperatingCashFlow, TotalAssets, TotalRevenue)
>VALUES ('ABC Company', 'ABC', '00112233', CONVERT(DATETIME, '2005-12-31
>00:00:00', 102), 'TTM', 12345, 23456, 45678, 56789)
>INSERT INTO CompanyRatios_3
>(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
>OperatingCashFlow, TotalAssets, TotalRevenue)
>VALUES ('ABC Company', 'ABC', '00112233', CONVERT(DATETIME, '2004-12-31
>00:00:00', 102), 'TTM', 23456, 11111, 11111, 22222)
>INSERT INTO CompanyRatios_3
>(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
>OperatingCashFlow, TotalAssets, TotalRevenue)
>VALUES ('XYZ Company', 'XYZ', '00332244', CONVERT(DATETIME, '2005-12-31
>00:00:00', 102), 'TTM', 22222, 33333, 44444, 55555)
>INSERT INTO CompanyRatios_3
>(Name, Ticker, CIK, PeriodEndDate, DurationType, NetIncome,
>OperatingCashFlow, TotalAssets, TotalRevenue)
>VALUES ('XYZ Company', 'XYZ', '00332244', CONVERT(DATETIME, '2004-12-31
>00:00:00', 102), 'TTM', 33333, 44444, 55555, 66666)
>
>
>
>*** Sent via Developersdex http://www.examnotes.net ***|||On Tue, 02 May 2006 16:59:16 -0400, Roy Harvey <roy_harvey@.snet.net>
wrote:

>SELECT K.Name,
> K.Ticker,
> K.CIK,
> C.PeriodEndDate,
> PeriodEndDateLastYear = P.PeriodEndDate,
> NetIncomeCurrentYear = C.NetIncome,
> NetIncomeLastYear = P.NetIncome,
> OpCashFlowCurrentYear = C.OperatingCashFlow,
> OpCashFlowLastYear = P.OperatingCashFlow
> FROM (select distinct Name, Ticker, CIK
> from CompanyRatios_3) as K -- for Key
> LEFT OUTER
> JOIN CompanyRatios_3 as C -- as in Current
> ON K.Ticker = C.Ticker
> AND K.CIK = C.CIK
> AND C.PeriodEndDate = '20051231'
> JOIN CompanyRatios_3 as P -- as in Prior
> ON K.Ticker = P.Ticker
> AND K.CIK = P.CIK
> AND P.PeriodEndDate = '20041231'
I missed the second LEFT OUTER:
SELECT K.Name,
K.Ticker,
K.CIK,
C.PeriodEndDate,
PeriodEndDateLastYear = P.PeriodEndDate,
NetIncomeCurrentYear = C.NetIncome,
NetIncomeLastYear = P.NetIncome,
OpCashFlowCurrentYear = C.OperatingCashFlow,
OpCashFlowLastYear = P.OperatingCashFlow
FROM (select distinct Name, Ticker, CIK
from CompanyRatios_3) as K -- for Key
LEFT OUTER
JOIN CompanyRatios_3 as C -- as in Current
ON K.Ticker = C.Ticker
AND K.CIK = C.CIK
AND C.PeriodEndDate = '20051231'
LEFT OUTER
JOIN CompanyRatios_3 as P -- as in Prior
ON K.Ticker = P.Ticker
AND K.CIK = P.CIK
AND P.PeriodEndDate = '20041231'
Roy|||Thanks for both solutions. There will always be a CIK, Ticker, and name
but the dates will not always be 12/31/2005 and 12/31/2004. I was using
those dates as examples.
*** Sent via Developersdex http://www.examnotes.net ***|||On Tue, 02 May 2006 18:29:13 -0700, Jason . <jrp210@.yahoo.com> wrote:

>Thanks for both solutions. There will always be a CIK, Ticker, and name
>but the dates will not always be 12/31/2005 and 12/31/2004. I was using
>those dates as examples.
That should be easily corrected. Just change the date tests:
AND datepart(year, C.PeriodEndDate) = datepart(year,getdate())
AND datepart(year, P.PeriodEndDate) = datepart(year,getdate()) - 1
Roy|||Thanks! There could be more than two dates per CIK so I am guessing I
would have to add the following to get the latest two dates:
WHERE (a.PeriodEndDate =
(SELECT MAX(c.PeriodEndDate)
FROM CompanyRatios_3 c
WHERE a.CIK = c.CIK))
*** Sent via Developersdex http://www.examnotes.net ***|||yeah.. I guess that should do the trick.
You will have to find the max of both this year and the previous year
--
"Jason ." wrote:

> Thanks! There could be more than two dates per CIK so I am guessing I
> would have to add the following to get the latest two dates:
> WHERE (a.PeriodEndDate =
> (SELECT MAX(c.PeriodEndDate)
> FROM CompanyRatios_3 c
> WHERE a.CIK = c.CIK))
>
> *** Sent via Developersdex http://www.developersdex

Combining two rows in a view

I have created a view for reporting. Im basically just joining a few
tables. It is for a University so the results shows students names and
the credits they are currently taking and the school code (There is 3
Colleges under one ownership)
The problem is some students attend two colleges and appear twice,
one for each enrollment. For example
FName LName Credits SchoolCode
John Smith 12 1468
John Smith 4 1469
I need to combine these results so it would look like this
John Smith 16 1468
This is not for all students just certain ones. I would like to do
this in the view if possible. Any help is appreciated.
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Programming...50.h
tml
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz
.com/eform.php?p=904750Looks like you want to return just one of the school codes? In that case,
just group the data by student, and aggregate the measures:
SELECT StudentID, FName, LName, SUM(Credits) AS TotalCredits,
MIN(ScheelCode) AS MinSchoolCode
FROM ViewName
GROUP BY StudentID, FName, LName;
BG, SQL Server MVP
www.SolidQualityLearning.com
Join us for the SQL Server 2005 launch at the SQL W

in Israel!
[url]http://www.microsoft.com/israel/sql/sqlw

/default.mspx[/url]
"TheCount" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_904750_a05cfa9ea57158f694c614723c
ee26e9@.dbforumz.com...
>I have created a view for reporting. I'm basically just joining a few
> tables. It is for a University so the results shows students names and
> the credits they are currently taking and the school code (There is 3
> Colleges under one ownership)
> The problem is some students attend two colleges and appear twice,
> one for each enrollment. For example
> FName LName Credits SchoolCode
> John Smith 12 1468
> John Smith 4 1469
> I need to combine these results so it would look like this
> John Smith 16 1468
> This is not for all students just certain ones. I would like to do
> this in the view if possible. Any help is appreciated.
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
> http://www.dbforumz.com/Programming...pict262850.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
> http://www.dbforumz.com/eform.php?p=904750|||Take a look at this example:
http://milambda.blogspot.com/2005/0...s-as-array.html
ML

Combining text data rows

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

Paragraph 1 of Document 1

Paragraph 2 of Document 1

Paragraph 3 of Document 1

Paragraph 1 of Document 2

Paragraph 2 of Document 2

New TEXT table

DocumentID

SectionText

Entire text of Document 1

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?

Hi moonshadow, the following will create a stored procedure that will fullfill the requested task. run the sript, it will have only one constraint, is that i supposed that the maximum section length is hundreds of characters i.e: it will be great if you can use nvarchar instead of ntext, but if more characters are needed to be stored then comment and we will work around it.

IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'myProc' AND type = 'P')
DROP PROCEDURE myProc
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'NewText' AND type = 'U')
DROP table NewText
CREATE TABLE NewText
( DocumentID int,
Paragraph1 ntext,
)
GO
CREATE PROCEDURE myProc
AS
declare @.a int
declare @.b nvarchar(3000)
declare @.c int
DECLARE myCursor CURSOR FOR
SELECT DocumentID,SectionText FROM Text001
OPEN myCursor
FETCH NEXT FROM myCursor into @.a,@.b
WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.c = @.a
update NewText set Paragraph1 = cast(Paragraph1 as nvarchar) + @.b where DocumentID = @.a
else
insert into NewText(DocumentID,Paragraph1) values (@.a,@.b)
set @.c=@.a
FETCH NEXT FROM myCursor into @.a,@.b
END
CLOSE myCursor
DEALLOCATE myCursor
select * from NewText
go
--ToCall your procedure:
execute myProc

|||

Hi Mario. Thanks for script. This is exactly what I am looking for.

I created a new query, pasted in your script, and clicked Execute.

A new table ("NewText") was created with the appropriate columns.

However the data from "Text001" was not copied to "NewText". When I open the "NewText" table, the content of the rows is "null".

As a Newbie to SQL Server, I am wondering if I should be doing something else to call the "myProc" procedure. Do I need to do another step to combine the rows from "Text001" and copy the combined data to "NewText"?

|||

yes moonshadow,

first it is great that you copied the script and started its excecution.

i am sure that it will work, but look what you will have to do:

EITHER: change the data type of SectionText from ntext to nvarchar, and then test. if you are urged to use ntext, then we can figure it out... but as a first step, just for your test, do not use ntext or at least do not use large text in your records under SectionText.

OR change the datatype of DocumentID to int.

look, the Table Text001 is like the following (i created this table upon your specifications):

CREATE TABLE [Text001] (
[DocumentID] [int] NULL ,
[SectionText] [ntext] COLLATE SQL_1xCompat_CP850_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

|||

Thanks for your patience Mario. Here is what I tried:

1. I tried to change the data type of SectionText from ntext to nvarchar but it would only allow nvarchar(50) or nvarchar(max). I chose nvarchar(max) since the SectionText contents are likely be much more than 50 characters. NewText table was created but still empty.

2. I changed the DocumentID to int with the same result as before.

What next?

|||

Mario:To simplify and make it more concrete for working with your query, I created a new database called “Practice”

In that database I ran this query based on your example to create a table called “Text001”:

CREATE TABLE [Text001] (

[DocumentID] [int] NULL ,

[SectionNo] [int] NULL ,

[SectionText] [ntext]

COLLATE SQL_1xCompat_CP850_CI_AS NULL )

ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I put data into five rows of the table Text001 as follows:

DocumentID

SectionNo

SectionText

Blue

Red

Green

White

Black

I ran the initial query that you provided which was “executed successfully”

The NewText table that was created looked like this:

DocumentID

Paragraph1

Null

I think the NewText Table should have looked like this:

DocumentID

Paragraph1

Blue

Red

Green

White

Black

I am learning alot from working with this and am thankful for your help.

|||

moonshadow!can you try this please:

add the following, IN RED,

...

declare @.c int
DECLARE myCursor CURSOR FOR
SELECT DocumentID,SectionText FROM Text001
OPEN myCursor
FETCH NEXT FROM myCursor into @.a,@.b

IF @.@.FETCH_STATUS <> 0
PRINT " ERROR!"

WHILE @.@.FETCH_STATUS = 0
BEGIN
if @.c = @.a

...

test it, cos it seems it is not entering the loop, if there was no error, try to update the following line:

if @.c = @.a
update NewText set Paragraph1 = Paragraph1 + @.b where DocumentID = @.a
else...

also, replace all field datatypes in tables Text001, and NewText to nvarchar (i.e: do not use ntext)

...CREATE TABLE NewText
( DocumentID int,
Paragraph1 nvarchar(3000), or max
)...

|||

Mario:

1. I changed all "ntext" datatypes in tables Text001, and NewText to "nvarchar(max)"

2. I copied and pasted this

IF @.@.FETCH_STATUS <> 0
PRINT " ERROR!"

as you suggested and got this error message:

Msg 128, Level 15, State 1, Procedure myProc, Line 14 The name "ERROR!" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

3. I also tried to update the following line as you suggested:

if @.c = @.a
update NewText set Paragraph1 = Paragraph1 + @.b where DocumentID = @.a
else...

but got the same error message.

|||ok moon shadow, instead of "ERROR!" just put anything, an insert statement, or a Print 1, just to check if the fetching is occuring with/without errors, just further troubleshooting|||

Mario: I tried numerous things without luck until I typed in a constant expression without the quotations:

IF @.@.FETCH_STATUS <> 0

PRINT 10

The command completed successfully but the content of the "NewText" table was still Null.

I figured 10 (or any number) is a constant expression as per the error message and should be appropriate but may not have served your purpose to see if the fetching is occurring. Any other thoughts on how to proceed?

|||

try to insert a record instead of PRINT, like the following:

IF @.@.FETCH_STATUS <> 0

insert into NewText(DocumentID,Paragraph1) values (1,'Error')

and check the NewText table

good luck

|||

Mario:

I used your new script and the "query executed successfully" but the content of the NewText table was still "null"

|||

moonshadow! run the following:

execute myProc

and then check the result in NewTable

|||

Mario: Yes!! The NewText table is now filled with the combined data. I told you I was a Newbie.

Perhaps you can help me with one refinement. The data in the NewNext table SectionText column is run together (ie., "blueredgreen") which will not work too well with the paragraphs in my original database. Is there a way to automatically format the new data fields (in NewText) so that each row of the original table (Text001) remains on its own line with a space between paragraphs when it is read in an application? Such as this:

blue

red

green

Thanks for your patience and knowledge in getting this far.

|||

Great MoonShadow!!!

now you've got the concept, you can expand it as you like...

regarding your concern, you can add a carriage return while filling each paragraph, i.e: add what is in red to the sql script:

...

update NewText set Paragraph1 = Paragraph1 + char(13)+char(10) + @.b where DocumentID = @.a
...

good luck

Tuesday, March 27, 2012

Combining rows in a table(again)

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?Your resultset doesn't make much sense. Can you explain it?|||Your resultset doesn't make much sense. Can you explain it?It's a "PeopleSoft" join.

No gams, I'm pretty sure that is optimal for the case you've presented.

-PatP|||Yes.
The idea is to get a row and the most recent previous status. The first row in a set will have no previous status.|||It's a "PeopleSoft" join.

No gams, I'm pretty sure that is optimal for the case you've presented.

-PatP
Does that make me a "PeopleSoft" joiner? What is a "PeopleSoft" join?|||The "PeopleSoft join" was a reference for Brett's information. Brett is quite familiar with the glories of PeopleSoft.

PeopleSoft is an ERP package. The PeopleSoft packages use a data representation that often needs to reference the "prior" row based on a presumed sequence.

-PatP

Combining records/Foreach Loop

I'm working on a data migration that requires combining rows/values from one table to update rows in another table, and I can't figure out if I need to do a nested FOREACH or something else. Here's the example.

I have a table called Health that has a unique child record, key is childID.

I have another table called Concerns that has multiple records for each child. The Concerns table structure has several Boolean fields that need to capture and retain a true value, no matter what the value is in the next record, i.e. once a field is true, it's always true. Then those values need to update the child record in the Health table.

So if the Concerns table has the following records for a child:

ChildID, DentalConcern, VisionConcern, HearingConcern.

1, True, False, False

1, False, True, False

1, False, False, False

The final values I need to update the Health table are:

1, True, True, False.

And of course, my recordset of Concerns has records for many children.

O.K., that's the background. I have Foreach Loop container set up to enumerate through the ADO recordset of the Concerns table. I have recordset variables set up for childID and each of the boolean Concerns fields. My thought was then to do a nested Foreach Loop container on the childID variable, with a Script Task to read in the recordset variables, then collect the True/False values in my readwrite variables I set up to "collect" the values of each record.

I think then I can compare the incoming recordset childID with the readwrite childID variable to see if it's changed, and if it has then I want to do the SQL update to the Health table. I'm stuck trying to figure out where to put my Execute SQL task to update the child record when I'm finished with one child. in the the Script Task. If it's in the nested Foreach, won't it execute the SQL for every record? Same question on the outer Foreach that's looping through the entire ADO recordset.

So should I put the Update sql statement in the Script Task instead of a separate Execute SQL Task?

Or is there a totally different way I need to look at looping through the entire recordset but doing processing on a subset based on the childID value?

Hope that makes sense, and thanks in advance for any help/suggestions.

Chera

cboom wrote:

I'm working on a data migration that requires combining rows/values from one table to update rows in another table, and I can't figure out if I need to do a nested FOREACH or something else. Here's the example.
I have a table called Health that has a unique child record, key is childID.
I have another table called Concerns that has multiple records for each child. The Concerns table structure has several Boolean fields that need to capture and retain a true value, no matter what the value is in the next record, i.e. once a field is true, it's always true. Then those values need to update the child record in the Health table.
So if the Concerns table has the following records for a child:
ChildID, DentalConcern, VisionConcern, HearingConcern.
1, True, False, False
1, False, True, False
1, False, False, False
The final values I need to update the Health table are:
1, True, True, False.
And of course, my recordset of Concerns has records for many children.
O.K., that's the background. I have Foreach Loop container set up to enumerate through the ADO recordset of the Concerns table. I have recordset variables set up for childID and each of the boolean Concerns fields. My thought was then to do a nested Foreach Loop container on the childID variable, with a Script Task to read in the recordset variables, then collect the True/False values in my readwrite variables I set up to "collect" the values of each record.
I think then I can compare the incoming recordset childID with the readwrite childID variable to see if it's changed, and if it has then I want to do the SQL update to the Health table. I'm stuck trying to figure out where to put my Execute SQL task to update the child record when I'm finished with one child. in the the Script Task. If it's in the nested Foreach, won't it execute the SQL for every record? Same question on the outer Foreach that's looping through the entire ADO recordset.
So should I put the Update sql statement in the Script Task instead of a separate Execute SQL Task?
Or is there a totally different way I need to look at looping through the entire recordset but doing processing on a subset based on the childID value?
Hope that makes sense, and thanks in advance for any help/suggestions.

Chera

Won't the following work:

UPDATE h

SET h.DentalConcern = c.MaxDentalConcern,

h.VisionConcern = c.MaxVisionConcern,

c.HearingConcern = c.MaxHearingConcern

FROM Health h

INNER JOIN (

SELECT ChildID,

CAST(MAX(CAST(DentalConcern as tinyint)) AS bit) as MaxDentalConcern,

CAST(MAX(CAST(VisionConcern as tinyint)) AS bit) as MaxVisionConcern,

CAST(MAX(CAST(HearingConcern as tinyint)) AS bit) as MaxHearingConcern,

FROM concerns

GROUP BY ChildID

) c

ON h.ChildID = c.ChildID

-Jamie

|||

Well, back to basic Transact-SQL for me. Did play with doing Max on the boolean fields which obviously didn't work, and didn't even think to Cast to integer. Many, many thanks.

Chera

Combining multiple rows into one

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 youselect case when Descr1=... end as Descr1, case when Descr2=... end as Descr2, case when Descr3=... end as Descr3 from <your table name>

my first contribution here, quite similar to what I was trying to do in a project, do correct me if it's wrong.|||I will not be able to use CASE since the values of Descr1 etc. are always different|||What i mean is that the values in Value column are always different and are unknown. Therefore i will not be able to use CASE|||Ta da!

http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx|||this is pretty much what adam is doing, but it's more compact and mysterious the first time you see it:

http://sqlblindman.googlepages.com/creatingcomma-delimitedstrings

plus it's from a regular here. :)|||Thank you.

Basically I came up with the following:

DROP FUNCTION dbo.ConcatDescr
go

CREATE FUNCTION dbo.ConcatDescr(@.TXRCODE CHAR(8))
RETURNS VARCHAR(300)
AS
BEGIN
DECLARE @.Output VARCHAR(300)
SET @.Output = ''
SELECT @.Output = CASE @.Output
WHEN '' THEN MON_TEXT
ELSE @.Output + ', ' + MON_TEXT
END
FROM PCLONG
WHERE TXRCODE = @.TXRCODE
order by MON_PCH

RETURN @.Output
END
GO

SELECT TXRCODE, dbo.ConcatDescr(TXRCODE)
FROM PCLONG
WHERE TXRCODE = '01100008'

The code above works to concatenate lines into one however it truncates data after 256 characters. I looked in help and it says that varchar can be up to 8000 chars. Is there something I am doing wrong?

Thank you again.|||it is because of the length of the variable where u r putting the data...increase it and the Return as well

RETURNS VARCHAR(300)
......
DECLARE @.Output VARCHAR(300)|||Your data may also be getting truncated by Query Analyzer. Check the QA options and bump up the maximum character output enough to display your results.

Sunday, March 25, 2012

Combining Multiple rows into 1 row x 1 column

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

There are a number of ways to complete what you wish. Some features that you can take advantage of include:

select with CASE and MAX

User defined functions

SELECT with FOR XML syntax (better in SQL 2005 than SQL 2000)

PIVOT

Transact SQL SELECT extensions|||Very Cool, Thanks.

|||

Just for the sake of completeness, this can also be achieved via cursors:

Assuming #Employee temp table contains the data.

declare @.sql varchar(200), @.k int

set @.sql = ''

set @.k = 0

declare @.EmpName varchar(50)

declare abc cursor for select EmployeeName from #Employee

open abc

fetch next from abc into @.EmpName

while @.@.FETCH_STATUS = 0

begin

if @.k > 0 set @.SQL = @.SQL +', '

set @.SQL = @.SQL + @.EmpName

set @.k = @.k +1

fetch next from abc into @.EmpName

end

close abc

deallocate abc

SELECT @.SQL as Employees

Drop Table #Employee

|||

Code Snippet

declare @.Output varchar(max)

select @.Output = isnull(@.Output + ', ' + [Employee Name] , [Employee Name] )

from MyTable

select @.Output as [OneColumn]

combining multiple rows in 1 row

how can i get a 1 row result set having multiple rows joined into 1 row
if i have 1 column having 5 rows i want to use a select statement that selects all rows joined into 1 row (results seperated by a comma for example)
thx
samhamWant to show us the query? What would make them join together?|||see Using COALESCE to Build Comma-Delimited String (http://sqlteam.com/item.asp?ItemID=2368)

rudy
http://r937.com/|||He wants to combine multiple rows..

And you don't use COALESCE to build a comma delimited srting..

Is used so that any null value in the string does not blow away the results...

It's the ability to do SELECT @.x = @.X + col1

like...

DECLARE @.x varchar(8000)
SELECT @.x = ISNULL(@.x,'') + ISNULL(FirstName,'') FROM Employees
SELECT @.x

The coalesec trick allows you to eliminmate commas if the value in the column is Null

so you dont get 1,,2,3,4,,5|||He wants to combine multiple rows yes, he wants the values from multiple rows to be put into a comma-delimited string
And you don't use COALESCE to build a comma delimited srting damned straight on that one, i certainly don't, i would never do it that way -- in fact, i would probably just never do it
The coalesec trick allows you to eliminmate commas if the value in the column is Null yes, that's correct, that's what it does for the first row

rudy|||thx guys that's exactly what i wanted

i'll use the code from the article

DECLARE @.EmployeeList varchar(100)

SELECT @.EmployeeList = COALESCE(@.EmployeeList + ', ', '') +
CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @.EmployeeList
--Results--

---
1, 2, 4

Combining Fields to string

I have a function that takes a field with values separated by commas within the field and splits them to multiple rows.

Example:
Field - Interior
Value - abc,def,efg,ghi

Output:
ID Item
1 abc
2 def
3 efg
etc

This is working great thanks to help that I received on here.

Now I am combining multiple fields to a string.
Example:
Field1: abc, def
Field2: ghi, jkl

using

SELECT (Field1 + ',' + Field2) From ....

This is working great unless there is a field that has a NULL value. Then I get a NULL result.

Is there an easy way to only put the fields with value into my string and leave out the NULL fields? Some have one NULL field, some have multiple. I just need to get the string to work and get only the fields that have values.

Any suggestions are always appreciated.It has been resolved on another post.

THANKS!!|||

Quote:

Originally Posted by rpeacock

It has been resolved on another post.

THANKS!!

I am having the same problem - can you tell me what other post solved the issue?

Thanks in advance

RIP

Combining DELETE and JOIN statements

In SQL Server 2000/2005 (not CE) I can use the following T-SQL statement to delete orphaned rows from a table:

DELETE GroupsMembers FROM GroupsMembers LEFT OUTER JOIN Groups ON GroupsMembers.GroupID = Groups.ID WHERE Groups.ID IS NULL

SQL Server CE does not seem to support combining the JOIN statement with the DELETE statement. Is this correct? If yes, is there any alternative statement that could be used to accomplish the same thing?

GerritYou could try with a NOT IN.

DELETE FROM GroupsMembers WHERE GroupID NOT IN (SELECT ID FROM Groups)
|||Thanks, that does seem to do the trick.

Gerrit

Thursday, March 22, 2012

combining 2 rows

Hello,
I have a query that returns 2 rows, which I need to combine into 1 row.
The query looks like this:
SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
CASE
WHEN t.TCode = 'debit_batch' THEN SUM(t.LAmount/t.ExchangeRate)
END
AS 'Amount (incl. commission)',
CASE
WHEN t.TCode = 'commission' THEN SUM(t.LAmount/t.ExchangeRate)
END
AS 'Commission'
FROM Reporting.dbo.RollUp t
WHERE (t.PTCode='debit')
AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
GROUP BY t.CName, t.AName, t.ACurrency, t.TCode
ORDER BY t.CName, t.AName, t.ACurrency
...the 2 rows that the query returns are:
CName AName ACurrency Payments Amount (incl. commission) Commission
ClientA ClientA EUR 69 NULL 173.27
ClientA ClientA EUR 69 3465.29 NULL
...and I want to combine those 2 rows into a single row that looks like
this:
CName AName ACurrency Payments Amount (incl. commission) Commission
ClientA ClientA EUR 69 3465.29 173.27
Thanks in advance,
Craig H.Use your query as a derived table and collapse the rows in a query around
it -- like this:
Select CName, AName, ACurrency, Payments, Sum( [Amount (incl. commission)]),
Sum ([Commission])
From
(
SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
CASE
WHEN t.TCode = 'debit_batch' THEN SUM(t.LAmount/t.ExchangeRate)
END
AS 'Amount (incl. commission)',
CASE
WHEN t.TCode = 'commission' THEN SUM(t.LAmount/t.ExchangeRate)
END
AS 'Commission'
FROM Reporting.dbo.RollUp t
WHERE (t.PTCode='debit')
AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
GROUP BY t.CName, t.AName, t.ACurrency, t.TCode
ORDER BY t.CName, t.AName, t.ACurrency
) T
Group By CName, AName, ACurrency, Payments
Order By CName, AName, ACurrency
hth,
Daniel Wilson
Senior Software Solutions Developer
Embtrak Development Team
http://www.Embtrak.com
DVBrown Company
"Craig H." <spam@.thehurley.com> wrote in message
news:u2%23N1$OnFHA.3336@.tk2msftngp13.phx.gbl...
> Hello,
> I have a query that returns 2 rows, which I need to combine into 1 row.
> The query looks like this:
> SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
> CASE
> WHEN t.TCode = 'debit_batch' THEN SUM(t.LAmount/t.ExchangeRate)
> END
> AS 'Amount (incl. commission)',
> CASE
> WHEN t.TCode = 'commission' THEN SUM(t.LAmount/t.ExchangeRate)
> END
> AS 'Commission'
> FROM Reporting.dbo.RollUp t
> WHERE (t.PTCode='debit')
> AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
> GROUP BY t.CName, t.AName, t.ACurrency, t.TCode
> ORDER BY t.CName, t.AName, t.ACurrency
>
> ...the 2 rows that the query returns are:
> CName AName ACurrency Payments Amount (incl. commission) Commission
> ClientA ClientA EUR 69 NULL 173.27
> ClientA ClientA EUR 69 3465.29 NULL
>
> ...and I want to combine those 2 rows into a single row that looks like
> this:
> CName AName ACurrency Payments Amount (incl. commission) Commission
> ClientA ClientA EUR 69 3465.29 173.27
>
> Thanks in advance,
> Craig H.|||hi
it might work, if u remove
t.TCode from the group by clause
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.SQLResource.com/
---
"Craig H." wrote:

> Hello,
> I have a query that returns 2 rows, which I need to combine into 1 row.
> The query looks like this:
> SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
> CASE
> WHEN t.TCode = 'debit_batch' THEN SUM(t.LAmount/t.ExchangeRate)
> END
> AS 'Amount (incl. commission)',
> CASE
> WHEN t.TCode = 'commission' THEN SUM(t.LAmount/t.ExchangeRate)
> END
> AS 'Commission'
> FROM Reporting.dbo.RollUp t
> WHERE (t.PTCode='debit')
> AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
> GROUP BY t.CName, t.AName, t.ACurrency, t.TCode
> ORDER BY t.CName, t.AName, t.ACurrency
>
> ...the 2 rows that the query returns are:
> CName AName ACurrency Payments Amount (incl. commission) Commission
> ClientA ClientA EUR 69 NULL 173.27
> ClientA ClientA EUR 69 3465.29 NULL
>
> ...and I want to combine those 2 rows into a single row that looks like
> this:
> CName AName ACurrency Payments Amount (incl. commission) Commission
> ClientA ClientA EUR 69 3465.29 173.27
>
> Thanks in advance,
> Craig H.
>|||> it might work, if u remove
> t.TCode from the group by clause
No. It will give you the error t.TCCode is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:3485024E-D8DF-42DE-A9F5-13C37C0B22FF@.microsoft.com...
> hi
> it might work, if u remove
> t.TCode from the group by clause
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://www.SQLResource.com/
> ---
>
> "Craig H." wrote:
>|||Looks like you just need to remove TCode from your GROUP BY list:
...
GROUP BY t.CName, t.AName, t.ACurrency
David Portas
SQL Server MVP
--|||Good catch. I missed it too. So put the CASE expression inside the SUM
aggregate. Something like the following, depending on the desired
result of the SUM:
SELECT t.CName, t.AName, t.ACurrency, Sum(t.NumPayments) AS 'Payments',
SUM(CASE WHEN t.TCode = 'debit_batch'
THEN t.LAmount/t.ExchangeRate END) AS 'Amount (incl. commission)',
SUM(CASE WHEN t.TCode = 'commission'
THEN t.LAmount/t.ExchangeRate END) AS 'Commission'
FROM Reporting.dbo.RollUp t
WHERE (t.PTCode='debit')
AND ((t.TCode='debit_batch') OR (t.TCode='commission'))
GROUP BY t.CName, t.AName, t.ACurrency
ORDER BY t.CName, t.AName, t.ACurrency ;
David Portas
SQL Server MVP
--|||David,
Correct me If I am wrong.
As per my understanding , removing TCode fro the Group By list will
throw the error
TCCode is invalid in the select list because it is not contained in
either an aggregate function or
the GROUP BY clause.
SELECT pub_id,
CASE WHEN type = 'business' THEN SUM(ytd_sales) END
FROM Titles
GROUP By pub_id
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1123599468.276266.144040@.g44g2000cwa.googlegroups.com...
> Looks like you just need to remove TCode from your GROUP BY list:
> ...
> GROUP BY t.CName, t.AName, t.ACurrency
> --
> David Portas
> SQL Server MVP
> --
>|||Never mind. I had seen the other post only after sending this.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Roji. P. Thomas" <thomasroji@.gmail.com> wrote in message
news:%23iPyJWPnFHA.764@.TK2MSFTNGP14.phx.gbl...
> David,
> Correct me If I am wrong.
> As per my understanding , removing TCode fro the Group By list will
> throw the error
> TCCode is invalid in the select list because it is not contained in
> either an aggregate function or
> the GROUP BY clause.
>
> SELECT pub_id,
> CASE WHEN type = 'business' THEN SUM(ytd_sales) END
> FROM Titles
> GROUP By pub_id
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1123599468.276266.144040@.g44g2000cwa.googlegroups.com...
>

Tuesday, March 20, 2012

Combine Rows in Search Result

In Sql Server 2005 Express I have this table:

CREATE TABLE [dbo].[Sections](
[SectionID] [int] NOT NULL,
[DocumentNo] [smallint] NULL,
[SequenceNo] [smallint] NULL,
[SectionNo] [smallint] NULL,
[DocumentTypeID] [smallint] NULL,
[SectionText] [ntext] NULL)

Each paragraph of text (SectionText) is in its own row(SectionNo) Each primary document has a DocumentTypeID of 1 withthree subdocument types (2=Index, 3=Background, 4=Report).

I run this query and return a collection of single rows from various documents grouped together by DocumentNo:

SELECT *
FROM Sections
WHERE CONTAINS (SectionText, 'exercise')
ORDER BY DocumentNo

For each row that contains the search term, I would like toreturn the full document (all rows as parapraphs within one row ofreturned data). In other words, I want to reconstitute the fulldocument as it existed prior to being inserted into the database withparagraph separation.

For exampe, if the search term is in row 3of DocumentNo=5, DocumentTypeID=2, I want to return all the rows ofthat document in one block of text that retains paragraph format(preferablly with a line break and carriage return betweenparagraphs). How can this be done?

You can do this trick which will lead you to solve the problem.

Okay, let say you need to group each page's paragraph in one record insted of many records (as in your current case).

Step#1:

So, Create another table with following columns :
1) BookID: Int or smallint
2) PageID: Int or smallint
3) PageText: Text or NText

Step#2:

1) Do acursor that will loop throug all of theparagraphs related to aspecific page.
2) DoINSERT thefirst record into thePageText field of thenew created table, while you doUPDATEfor therest of recordsafter concatenatingthem with value already exists in thePageText field.

Step#3:

Do this for each page in each book.

Result:

At the end you will have one table from which you can query and seach about any word/paragraph in any page in any book!!

Good luck.

|||

Thanks for the suggestion. I will give it a try.

Combine rows data into a Column

I have two tables, which you can call master and details. I want to pull data
from master table and respective details from details table. Here are tables
and data information.
Master Table
MID Description
1 Person â' 1
2 Person -2
3 Person â' 3
Detail Table
DID MID Credit Card
1 1 Visa Card
2 1 Master Card
3 2 Visa Card
4 3 Visa Card
5 3 Master Card
and I want report something like this:
================================= Description Credit Card
================================= Person -1 Visa Card, Master Card
Person â' 2 Master Card
Person â' 3 Visa Card, Master Card
Now I am not sure this is possible in Reporting Services.You will have to write an SP which returns data in the format you want.
>--Original Message--
>I have two tables, which you can call master and details. I want to pull data >from master table and respective details from details table. Here are tables >and data information.
>Master Table
>MID Description
>1 Person =E2?" 1
>2 Person -2 >3 Person =E2?" 3
>Detail Table
>DID MID Credit Card
>1 1 Visa Card
>2 1 Master Card
>3 2 Visa Card
>4 3 Visa Card
>5 3 Master Card
>
>and I want report something like this:
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D
>Description Credit Card
>=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D==3D=3D=3D=3D=3D=3D=3D=3D=3D
>Person -1 Visa Card, Master Card
>Person =E2?" 2 Master Card
>Person =E2?" 3 Visa Card, Master Card
>
>Now I am not sure this is possible in Reporting Services.
>.
>|||You'd have to join the two tables. Take a look at
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_1l4j.asp?frame=true
for examples.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sam B" <SamB@.discussions.microsoft.com> wrote in message
news:29AF9D45-CAFE-4749-9754-E5B6EA0DC660@.microsoft.com...
> Thanks for your quick response.
> Now I am not clear how would I do that, can you please provide me some
hints?
> "anonymous@.discussions.microsoft.com" wrote:
> > You will have to write an SP which returns data in the
> > format you want.
> >
> > >--Original Message--
> > >I have two tables, which you can call master and details.
> > I want to pull data
> > >from master table and respective details from details
> > table. Here are tables
> > >and data information.
> > >
> > >Master Table
> > >
> > >MID Description
> > >1 Person Ã¢?" 1
> > >2 Person -2
> > >3 Person Ã¢?" 3
> > >
> > >Detail Table
> > >
> > >DID MID Credit Card
> > >1 1 Visa Card
> > >2 1 Master Card
> > >3 2 Visa Card
> > >4 3 Visa Card
> > >5 3 Master Card
> > >
> > >
> > >and I want report something like this:
> > >
> > >=================================> > >Description Credit Card
> > >=================================> > >Person -1 Visa Card, Master Card
> > >Person Ã¢?" 2 Master Card
> > >Person Ã¢?" 3 Visa Card, Master Card
> > >
> > >
> > >Now I am not sure this is possible in Reporting Services.
> > >
> > >.
> > >
> >

Combine multiple rows into one

I have two tables that I need to join to get the required data, but I get too many rows returned with everything I have tried. Basically, both tables have and IDEA_ID field, and a simple SELECT query on TABLE1 will give me the data that I want from that table, but I get too many rows returned from TABLE2 as there are multiple rows with the same IDEA_ID in TABLE2.

Basically,

SELECT *
FROM TABLE1

gives me all the data I need with the exception of 6 columns for which the data is in TABLE2.

TABLE2 has 3 columns - IDEA_ID, ROLE_ID and ADATE. IDEA_ID matches the IDEA_ID field in TABLE1; ROLE_ID is a number from 1 to 6; and ADATE is a date. There can be 1 to 6 rows for each IDEA_ID, with the IDEA_ID and ROLE_ID combination being unique.

How can I run a query that will give me rows that have the fields from TABLE1, and 6 additional columns from TABLE2?

Table structures are:

TABLE1
IDEA_ID, PROD_TYPE, TITLE, DESC

TABLE2
IDEA_ID, ROLE_ID, ADATEWhich six columns do you need from the second table?

-PatP|||Pat

For each IDEA_ID in TABLE2, there are potentially 6 rows with that IDEA_ID, but with a number from 1 to 6 in the ROLE_ID column.

What I'm trying to do is to create a dataset that has all of the columns from TABLE1, but I want to create 6 new columns with data from TABLE2. Each of the new columns will represent a row from TABLE2 for each of the values of ROLE_ID from 1 to 6. The value that will go into each of these columns is the value in ADATE.

I can query TABLE2 to get these values:

SELECT idea_id,
decode(role_id, 1, adate),
decode(role_id, 2, adate),
decode(role_id, 3, adate),
decode(role_id, 4, adate),
decode(role_id, 5, adate),
decode(role_id, 6, adate)
FROM TABLE2

but this produces 6 rows of output. What I want from TABLE2 is a single row with each of these values. The query above produces a dataset with IDEA_ID plus 6 columns, each row having only one of those 6 column containing a value.

How do I produce just one row of output with all 6 of these columns having a value in it?

Combine matching multiple rows into one row

IS there a way to combine all matching rows in a table so that it
outputs as one row, for example:

tblMyStuff
UniqueID int IDENTITY
ParentID int
SomeSuch nvarchar(50)
SomeSuch2 nvarchar(50)

Table data:
UniqueID ParentID SomeSuch SomeSuch2
1 1 Dog Bark
2 1 Cat Meow
3 3 Cow Moo
4 3 Horse Whinnie
5 5 Pig Oink

Desired query result from Query:
SELECT ? as myText from tblMyStuff WHERE ParentID = 3
myText = Cow Moo, Horse Whinnie

Help is appreciated,
lqlaurenq uantrell (laurenquantrell@.hotmail.com) writes:
> IS there a way to combine all matching rows in a table so that it
> outputs as one row, for example:
> tblMyStuff
> UniqueID int IDENTITY
> ParentID int
> SomeSuch nvarchar(50)
> SomeSuch2 nvarchar(50)
> Table data:
> UniqueID ParentID SomeSuch SomeSuch2
> 1 1 Dog Bark
> 2 1 Cat Meow
> 3 3 Cow Moo
> 4 3 Horse Whinnie
> 5 5 Pig Oink
> Desired query result from Query:
> SELECT ? as myText from tblMyStuff WHERE ParentID = 3
> myText = Cow Moo, Horse Whinnie

SELECT ltrim(str(UniqueID)) + '|' + ltrim(str(ParenID) + '|' +
SomeSuch + '|' + SomeSuch2
FROM tbl

Of course these theme can be varied in several ways, depending if you
want a delimiter, the numeric values to be padded etc.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Will this do you?

DECLARE @.Str nvarchar(500)
SELECT @.Str=CASE WHEN @.Str IS NULL THEN '' ELSE @.Str+', ' END+SomeSuch+'
'+SomeSuch2 from tblMyStuff WHERE ParentID = 3
SELECT @.Str

Mr Tea
http://mr-tea.blogspot.com

"laurenq uantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1106447396.269656.91240@.z14g2000cwz.googlegro ups.com...
> IS there a way to combine all matching rows in a table so that it
> outputs as one row, for example:
> tblMyStuff
> UniqueID int IDENTITY
> ParentID int
> SomeSuch nvarchar(50)
> SomeSuch2 nvarchar(50)
> Table data:
> UniqueID ParentID SomeSuch SomeSuch2
> 1 1 Dog Bark
> 2 1 Cat Meow
> 3 3 Cow Moo
> 4 3 Horse Whinnie
> 5 5 Pig Oink
> Desired query result from Query:
> SELECT ? as myText from tblMyStuff WHERE ParentID = 3
> myText = Cow Moo, Horse Whinnie
> Help is appreciated,
> lq

Monday, March 19, 2012

Combine many rows to one row?

Dear friends,

I have a problem that need some help from expert.Is there any way I could combine many rows into a row in Access using Visual Basic. I want to change the below table from TABLE A to TABLE B

TABLE A SampleCode Test Name Result ID Name Sex 9300105Peripheral Blood Film....
a few poikilocytes are present.S7585512EDHANDAPANI MAHESHM9300105Peripheral Blood Film....
No blast cells seen.S7585512EDHANDAPANI MAHESHM9300105Peripheral Blood Film....
microcytes, elongated cells andS7585512EDHANDAPANI MAHESHM9300105Peripheral Blood Film....
hypochromic but normocytic: . SomeS7585512EDHANDAPANI MAHESHM9300105Peripheral Blood Film....
Majority of rbcs appear slightlyS7585512EDHANDAPANI MAHESHM

Output:

TABLE B SampleCode Test Name Result ID Name Sex 9300105Peripheral Blood Film....
a few poikilocytes are present, No blast cells seen.microcytes, elongated cells and hypochromic but normocytic. Some Majority of rbcs appear slightlyS7585512EDHANDAPANI MAHESHM

Your help would be greatly appreciated

Thanks a lot,

Chicky

You might want to give this thread from yesterday a look:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1335992&SiteID=1

Combine Detail rows in one column

combine data in one row?

I need to display data in such a way that one row represents one Id. In
other words, I need to combine multiple rows data, separated by commas,
per each Id.
create table #temp
(a int,
b varchar(20))
insert into #temp values (1, 'green')
insert into #temp values (1, 'blue')
insert into #temp values (2, 'red')
insert into #temp values (3, 'black')
insert into #temp values (4, 'yellow')
insert into #temp values (4, 'white')
I need a query to give me this -
a b
-- --
1 green,blue
2 red
3 black
4 yellow, white
Thanks for your help.
*** Sent via Developersdex http://www.examnotes.net ***Check out the following thread:
http://groups.google.com/group/micr...4c4b0ff09ad4d58|||Thanks Jeff! I'll try to make it work in my case. However, I need to
make one change in the DDl. The ID fld is a varchar and it conatins
aplhanumeric values. see below the revised code:
create table #temp
(a varchar (20),
b varchar(20))
insert into #temp values ('1a', 'green')
insert into #temp values ('1a', 'blue')
insert into #temp values ('2v', 'red')
insert into #temp values ('3k', 'black')
insert into #temp values ('4x', 'yellow')
insert into #temp values ('4x', 'white')
I need a query to give me this -
a b
-- --
1a green,blue
2v red
3k black
4x yellow, white
Thanks for your help!!!
*** Sent via Developersdex http://www.examnotes.net ***|||You're kidding, right?
Just change the type of the ID column from int to varchar(20)|||Thanks, Jeff!
It is all working!
*** Sent via Developersdex http://www.examnotes.net ***

Combine 2 rows from derived table into 1 row w/o repeating query?

I'm trying not to use a temp table, but i may have to do so..

i have a derived table that makes the following results:

ID Status Name

2 1 "A"

2 2 "B"

I want to get the following:

ID Name1 Name2

2 "A" "B"

but like I said before, I can't repeat the query that gets the first 2 rows, as it's pretty invovled. a temp table is the best route I see right now, but I just wanted to be sure I'm not missing something.

Here it is,

Code Snippet

Create Table #data (

[ID] int ,

[Status] int ,

[Name] Varchar(100)

);

Insert Into #data Values('2','1','A');

Insert Into #data Values('2','2','B');

Select

,max(case when Status=1 Then [Name] end) [name1]

,max(case when Status=2 Then [Name] end) [name2]

from

(

Select * from #data -- Your Derived Table

) as Data

Group By

|||

The solution will work, I just needed to think about how to expand it for more columns, but I got it now.

If it's very very slow, I will try something with a CTE - I think that'll work as well.

|||

The CTE was 50-60% faster than the other route! but that method is also useful if using sql2000.|||

Yeah a CTE is going to be the way to go on this in 2005 for sure. Never been a fan of using temp tables and I avoid them when I can. So here's my own variation on the above sample...

Code Snippet

SELECT
[ID],
max(case when Status=1 Then [Name] end) [name1],
max(case when Status=2 Then [Name] end) [name2]

from (
SELECT 2 As [ID], 1 As Status, 'A' As [Name]
UNION SELECT 2, 2, 'B'
) AS Data
GROUP BY [ID]

Combinding two rows into one and then displaying in a ddl

I'm pretty sure this can be done just don't know how. I need to combind two rows from the same table in an sql server database into one row that I can then put into a dropdown list.
ex.
ProductID ProductDescription are the two rows That I need to display in the dropdown list.
12 Backpack
should show up in the ddl as 12 Backpack as a choice.
Thanks in advance for the helpSELECT
CONVERT(varchar,ProductID) + ' ' + ProductDescription
FROM
Products|||

thanks that's exactly what I was looking for

Thursday, March 29, 2012

Combining two rows into one

Combining two rows in a view

Combining text data rows

Null

Null

Tuesday, March 27, 2012

Combining rows in a table(again)

Combining records/Foreach Loop

Combining multiple rows into one

Sunday, March 25, 2012

Combining Multiple rows into 1 row x 1 column

combining multiple rows in 1 row

Combining Fields to string

Combining DELETE and JOIN statements

Thursday, March 22, 2012

combining 2 rows

Tuesday, March 20, 2012

Combine Rows in Search Result

Combine rows data into a Column

Combine multiple rows into one

Combine matching multiple rows into one row

Monday, March 19, 2012

Combine many rows to one row?

Combine Detail rows in one column

combine data in one row?

Combine 2 rows from derived table into 1 row w/o repeating query?

Combinding two rows into one and then displaying in a ddl

Collation problem

Blog Archive

About Me