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

I am adding data to a table from a table of identified duplicates.
These duplicates are not Key Violations, they are actual double entries
where one was deactivated prior to the new record being added (not very
clean but necessary given the system the data originates from). What I
would like to do is to take the data and combine some fields of the
duplicate rows.
I have created my mapping table (the table I want to add data to) which
looks like:
UniversalID (Identity)(PK)
Original_ID1 <-key from 1st row
Original_ID2 <-key from 2nd row
2ndSystemID
My data would look like this:
OriginalID Name 2ndSystemID
123 Smith, John KHGK39
124 Smith, John KHGK39
..
What I want is
UniversalID Original_ID1 Original_ID2 2ndSystemID
1 123 124 KHGK39
...
I know that this is doable, and I also know that I should know how, but
just can't seem to see the forest because all of the trees are in my
way.
Thanks for any help you can provide.
MTAnd if there are triplicates?
Don't try fixing old flaws by introducing new ones - such as breaking normal
form. I'd sugesst either deleting unwanted rows or adding a table to store
duplicate values of OriginalID referencing a proper primary key (e.g
2ndSystemID). Normalize now and prevent further difficulties.
ML
http://milambda.blogspot.com/|||Not really trying to break normal form. I need all of these values to
reference back to the original systems where the data lives. This is
why I need all of them. The UniversalID will be the new Key, the old
IDs, will become simple references.
I truly wish I could use only one, but unfortunately due to the nature
of the data I am dealing with, I am unable to and must find a work
around.
MT|||MT wrote:
> Not really trying to break normal form. I need all of these values to
> reference back to the original systems where the data lives. This is
> why I need all of them. The UniversalID will be the new Key, the old
> IDs, will become simple references.
> I truly wish I could use only one, but unfortunately due to the nature
> of the data I am dealing with, I am unable to and must find a work
> around.
> MT
Won't your proposal fail if there are more than 2 original ids? How
many do you expect to have to cope with?
Unfortunately you haven't given much information about keys. Here's a
guess at what I'd do:
CREATE TABLE users (universalid INTEGER PRIMARY KEY, name VARCHAR(50)
NOT NULL UNIQUE, systemid INTEGER NOT NULL);
CREATE TABLE original_ids (original_id INTEGER NOT NULL PRIMARY KEY,
universalid INTEGER NOT NULL REFERENCES users (universalid));
INSERT INTO users VALUES (1, 'Smith, John', 'KHGK39');
INSERT INTO orginal_ids VALUES (123,1);
INSERT INTO orginal_ids VALUES (124,1);
A join will map any number of original ids to the universal one.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Combining multiple tables - please help!

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
Try putting each matrix inside a rectangle, then hide the rectangle containing the table based on the user parameter.

Additionally, put all 3 rectangles within one parent rectangle. Should help with the spacing.|||Hi Andy

thanks for the reply!

While I like that idea and provides a lot more control over the positioning of the tables.. the problem still exits...

It seems that making a textbox "visibility = true" just produces an empty textbox..but still leaves the space allocated to textbox in place... and therefore the gap.. the rectangles dont seem to help.. unless im doing something wrong..

Does this seem right ?
|||I also just tried making the middle rectangle hidden = true and it didnt close the gap as expected.. very confused.. I just draggged the rectangles into the parent rectangle... ?

very strange.. any idea's why this may not be working for me ?
|||Well now I'm confused about what you're doing. One partyou're talking about matrixes, the next you're talking about tables. Which are you using? Matrixes or tables? Do you have 3 separate matrixes or 3 rows in one matrix?

Have you tried setting the visibility property for the whole row (or column) instead of the individual textboxes?|||

Using Matrix

Select appropriate textbox in matrix

Right click

Select Edit Group...

Visibility tab

Select Expression

Add your expression

It will not work if you put it on the textbox properties.

Using Table

Select appropriate column

Go to properties

Go down to visibility tag and select expression

Add your expression

You can use a variety of expressions to hide the columns like parameters or counts.

=IIF(Fields!City.Value=@.City, FALSE, TRUE)

or

=IIF(Trim(Fields!City.Value) = "", TRUE, FALSE)

Hope this helps

|||Sorry, I mean matrix...

My problem is not making the whole matrix invisible... I have 3 of them lined from left to right.
All three have the same row (group headings - sorry I should have made that clear). I dont need all the to show the same headings on the row groups. So, I just want to beable to hide these headings depending on the users selection.

The problem is that making the row headings + total header hidden, leaves a gap between the matrices as if the headers were there but just with no text or borders in them (invisible to the human eye but as if its like a brick stopping the matrix from moving closer to the other ones)

Sorry for the confusion.. thanks for helps !
But any idea.. anyone ?
|||Hi Brady,

Thanks for the suggestion.. while I have been trying that.. the problem there is that bigger Textbox located in the top left of the matrix.. that wont be invisible and therefore will stop my matrix from moving closer to the other matrix..

Is there a way to get rid of that Textbox...?
I have tried deleting it , but does not go away...

Thanks!sqlsql

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

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

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

---

Chicky

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

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

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

Id

,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

Id

|||

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

COM conflict resolver EXPERT idea needed(urgent)

Hi,
In COM conflict resolver, let's say, subscriber tries to upload an
INSERTED row to the publisher. By setting
rct = REPOLEAllChanges
in the IVBCustomResolver_GetHandledStates COM function I can catch all
the changes not only conflicts.
And in IVBCustomResolver_Reconcile function of the COM resolver, I
want to manipulate the INSERTED row from the source and eventually
insert that manipulated row to the destination. I tried
rrc.InsertRow
rrc.CopyRowFromSource
rrc.UpdateRow
but they did not work.
What is missing? What should I do to get an inserted row(Not updated
one, update works without any problem) from source in COM resolver
and manipulate it and INSERT it to the destination.
Any help would be greatly appreciated.
Thanks,
Nury SWORD
I found it, and would like to share it with the ones working on COM
conflict resolver before they suffer like me.
In COM conflict resolver, InsertRow is almost useless, it does not do
anything at all.
In Reconcile method, let's say a row is inserted from source to
destination, and you want to manipulate the inserted data row and
INSERT that manipulated row to the destination. In order to do that,
the sequence is :
SetColumn ...
SetColumn ...
...
(you manipulate as much columns as you want with SetColumn)
and then the magical ;
CopyRowFromSource method should be invoked. This is not documented in
anywhere, and I am happy to announce that.. I hope Microsoft includes
a little bit documentation about;
HOW TO MANIPULATE an INSERTED ROW (updated row is given as VB sample)
in COM Conflict resolver...
I hope that helps..
Nury Sword (MCDBA - MCSD)
Toronto
nurysword@.hotmail.com

Columns & rowns interchage on string the data

Hi,

Is it possible to interchange column & row data from the t-sql on
storing into a file?

Thanks

*** Sent via Developersdex http://www.developersdex.com ***Simon,

Thanks for your reply. I woule like the following output data

col1 col2 col3 ......

row1 r1c1 r1c2 c1c3 ...

row2 r2c1 r2c2 r2c3 ....

row3 r3c1 r3c2 r3c3 ......

..
..
..

to be output like

row1 row2 row3 ......

col1 r1c1 r2c1 r3c1

col2 r1c2 r2c2 r3c2

col3 r1c3 r2c3 r3c3
..
..
..

Thanks

*** Sent via Developersdex http://www.developersdex.com ***|||I'm not sure I understand your question, but I think you're asking if
you can export a crosstab report to a file? If so, a reporting tool is
probably the best general solution (MSSQL Reporting Services, Business
Objects etc), but you could create your own qrosstab queries:

http://www.aspfaq.com/show.asp?id=2462

To export the results to a file, you could use osql.exe, bcp.exe or
DTS.

Simon|||Here's an example:

CREATE TABLE foo (foo_key INTEGER PRIMARY KEY, x INTEGER NOT NULL, y
INTEGER NOT NULL, z INTEGER NOT NULL) ;

INSERT INTO foo (foo_key, x, y, z)
SELECT 1,10,11,12 UNION ALL
SELECT 2,20,21,22 UNION ALL
SELECT 3,30,31,32 ;

SELECT foo_key, x, y, z
FROM foo ;

SELECT 'x' AS col,
MAX(CASE WHEN foo_key = 1 THEN x END) AS row1,
MAX(CASE WHEN foo_key = 2 THEN x END) AS row2,
MAX(CASE WHEN foo_key = 3 THEN x END) AS row3
FROM foo
UNION ALL
SELECT 'y',
MAX(CASE WHEN foo_key = 1 THEN y END),
MAX(CASE WHEN foo_key = 2 THEN y END),
MAX(CASE WHEN foo_key = 3 THEN y END)
FROM foo
UNION ALL
SELECT 'z',
MAX(CASE WHEN foo_key = 1 THEN z END),
MAX(CASE WHEN foo_key = 2 THEN z END),
MAX(CASE WHEN foo_key = 3 THEN z END)
FROM foo ;

--
David Portas
SQL Server MVP
--

Column Size

From what I understand there is not a function or script available to
determine the row size of a table containing variable width columns.
Specifically I have a text column and was trying to determine the size of
the data being inserted, for each specific row.
I see the DATALENGTH function but that is returning the length in bytes,
not the size in bytes.
What if I ran a query in DTS to export the data in the column to a text
file, say "select messagebody from ifsmessages where messageid = 433"?
Would the size of the text file indicate the size stored in the column?
Message posted via http://www.sqlmonster.com
Robert Richards via SQLMonster.com wrote:
> I see the DATALENGTH function but that is returning the length in
> bytes, not the size in bytes.
The length is the size, pretty much. You could use datalength to add up
all the lengths across all columns for a given row to estimate the row
size. There is some row byte overhead depending on how columns are
defined.
http://msdn.microsoft.com/library/de...es_02_92k3.asp
David Gugick
Imceda Software
www.imceda.com

Column Size

From what I understand there is not a function or script available to
determine the row size of a table containing variable width columns.
Specifically I have a text column and was trying to determine the size of
the data being inserted, for each specific row.
I see the DATALENGTH function but that is returning the length in bytes,
not the size in bytes.
What if I ran a query in DTS to export the data in the column to a text
file, say "select messagebody from ifsmessages where messageid = 433"?
Would the size of the text file indicate the size stored in the column?
Message posted via http://www.droptable.comRobert Richards via droptable.com wrote:
> I see the DATALENGTH function but that is returning the length in
> bytes, not the size in bytes.
The length is the size, pretty much. You could use datalength to add up
all the lengths across all columns for a given row to estimate the row
size. There is some row byte overhead depending on how columns are
defined.
http://msdn.microsoft.com/library/d...>
_02_92k3.asp
David Gugick
Imceda Software
www.imceda.com

Column Size

From what I understand there is not a function or script available to
determine the row size of a table containing variable width columns.
Specifically I have a text column and was trying to determine the size of
the data being inserted, for each specific row.
I see the DATALENGTH function but that is returning the length in bytes,
not the size in bytes.
What if I ran a query in DTS to export the data in the column to a text
file, say "select messagebody from ifsmessages where messageid = 433"?
Would the size of the text file indicate the size stored in the column?
--
Message posted via http://www.sqlmonster.comRobert Richards via SQLMonster.com wrote:
> I see the DATALENGTH function but that is returning the length in
> bytes, not the size in bytes.
The length is the size, pretty much. You could use datalength to add up
all the lengths across all columns for a given row to estimate the row
size. There is some row byte overhead depending on how columns are
defined.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_02_92k3.asp
David Gugick
Imceda Software
www.imceda.com

Column Row Delimeter Problem

I'm trying to import a comma delimited text file into a SQL table-

The row delimiters I am assuming are {CR}{LF}

When I try to open up my file in Excel, the data file parses perfectly.

When I try to port it over in SSIS, I get an error:

"The column delimeter for column <my column> was not found."

"An error occurred while processing the file <my file> on data row 2076"

I've tried looking at that data row, and i am having a hard time finding anything wrong with the row.

Anybody know of any good ways to debug that?

n/m- I figured out my problem- the data was buggy- it had dual double-quotations- yet the double-quotations are what signified text qualifiers- and SSIS was not correctly picking up the text qualifiers correctly.

How do you get SSIS to understand quotes if the text qualifier is a quote?

Column Lenght

What is the query to find the maximum lenght used in the
row of a column. Ex: The column is defined to be varchar
(2500) and the longest used row size is 1852.
T.I.ATry something like this:
SELECT MAX(LEN(ColumnName)) FROM TableName
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:084701c52a72$a20d8300$a501280a@.phx.gbl...
> What is the query to find the maximum lenght used in the
> row of a column. Ex: The column is defined to be varchar
> (2500) and the longest used row size is 1852.
> T.I.A|||SELECT MAX(DATALENGTH(column_name)) FROM Table
--
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:084701c52a72$a20d8300$a501280a@.phx.gbl...
> What is the query to find the maximum lenght used in the
> row of a column. Ex: The column is defined to be varchar
> (2500) and the longest used row size is 1852.
> T.I.A|||Thanks.........
>--Original Message--
>Try something like this:
>SELECT MAX(LEN(ColumnName)) FROM TableName
>--
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"Jim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:084701c52a72$a20d8300$a501280a@.phx.gbl...
>> What is the query to find the maximum lenght used in the
>> row of a column. Ex: The column is defined to be varchar
>> (2500) and the longest used row size is 1852.
>> T.I.A
>
>.
>

Column Lenght

What is the query to find the maximum lenght used in the
row of a column. Ex: The column is defined to be varchar
(2500) and the longest used row size is 1852.
T.I.A
Try something like this:
SELECT MAX(LEN(ColumnName)) FROM TableName
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:084701c52a72$a20d8300$a501280a@.phx.gbl...
> What is the query to find the maximum lenght used in the
> row of a column. Ex: The column is defined to be varchar
> (2500) and the longest used row size is 1852.
> T.I.A
|||SELECT MAX(DATALENGTH(column_name)) FROM Table
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:084701c52a72$a20d8300$a501280a@.phx.gbl...
> What is the query to find the maximum lenght used in the
> row of a column. Ex: The column is defined to be varchar
> (2500) and the longest used row size is 1852.
> T.I.A
|||Thanks.........

>--Original Message--
>Try something like this:
>SELECT MAX(LEN(ColumnName)) FROM TableName
>--
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"Jim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:084701c52a72$a20d8300$a501280a@.phx.gbl...
>
>.
>

Column Lenght

What is the query to find the maximum lenght used in the
row of a column. Ex: The column is defined to be varchar
(2500) and the longest used row size is 1852.
T.I.ATry something like this:
SELECT MAX(LEN(ColumnName)) FROM TableName
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:084701c52a72$a20d8300$a501280a@.phx.gbl...
> What is the query to find the maximum lenght used in the
> row of a column. Ex: The column is defined to be varchar
> (2500) and the longest used row size is 1852.
> T.I.A|||SELECT MAX(DATALENGTH(column_name)) FROM Table
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Jim" <anonymous@.discussions.microsoft.com> wrote in message
news:084701c52a72$a20d8300$a501280a@.phx.gbl...
> What is the query to find the maximum lenght used in the
> row of a column. Ex: The column is defined to be varchar
> (2500) and the longest used row size is 1852.
> T.I.A|||Thanks.........

>--Original Message--
>Try something like this:
>SELECT MAX(LEN(ColumnName)) FROM TableName
>--
>Vyas, MVP (SQL Server)
>SQL Server Articles and Code Samples @.
http://vyaskn.tripod.com/
>
>"Jim" <anonymous@.discussions.microsoft.com> wrote in
message
>news:084701c52a72$a20d8300$a501280a@.phx.gbl...
>
>.
>