Concatenate strings from different rows

Hello.

I have a table like:

Id Name Description OrderId
1 Microsoft This is a 0
1 Microsoft huge company. 1

I need to create a select query that will concatenate description for both entries and output it like

Microsoft - This is a huge company.

Try using the USE XML PATH () as part of the select statement; maybe something like:

Code Snippet

declare @.testData table
( id integer,
[Name] varchar(10),
Description varchar(15),
orderId integer
)
insert into @.testData
select 1, 'Microsoft', 'This is a', 0 union all
select 1, 'Microsoft', 'huge company.', 1

select distinct
[Name] + ' - ' +
( select description + ' ' as [text()]
from @.testData b
where a.id = b.id
order by orderId
for xml path ('')
) as outputString
from @.testData a

/*
outputString
--
Microsoft - This is a huge company.
*/

Concatenate Rows into Columns

I have a table 2 columns (Object and Weight) with data like below:

Object Weight(lb)
table 5
Chair 6
Computer 3
Computer 5
TV 20
TV 15
Radio 10
Computer 10

Question: I would like to create a new table with one column that would join the above two columns like below

Object
table 5
Chair 6
Computer 3, 5, 10
TV 20, 15
Radio 10

Thanks for your help in advance.

SELECT t3.Object, MAX(case t3.seq when 1 then t3.Weight end)

+ MAX(case t3.seq when 2 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 3 then ', ' + t3.Weight else '' end) AS Weight

FROM ( SELECT Object, Weight, (SELECT COUNT(*) FROM mergeTable1$ AS t2 WHERE t2.Object = t1.Object and t2.Weight <= t1.Weight) AS seq

FROM mergeTable1$ AS t1

) as t3

GROUP BY t3.Object

ORDER BY t3.Object

|||

What if I do not know the Max number of Weights for each object. eg. an object may have n number of weights in the table.

Thanks

|||

I would guess a maximum possible number in this solution. For example:

SELECT t3.Object, MAX(case t3.seq when 1 then t3.Weight end)

+ MAX(case t3.seq when 2 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 3 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 4 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 5 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 6 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 7 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 8 then ', ' + t3.Weight else '' end) AS Weight

FROM ( SELECT Object, Weight, (SELECT COUNT(*) FROM mergeTable1$ AS t2 WHERE t2.Object = t1.Object and t2.Weight <= t1.Weight) AS seq

FROM mergeTable1$ AS t1

) as t3

GROUP BY t3.Object

ORDER BY t3.Object

By the way, are you using SQL Server 2005? There are other solutions to handle this one.

|||

Yes, I am using sql server 2005

Thanks

|||

In SQL 2005, you can try like this

Create table tbl(Object varchar(100), Weight int)

insert tbl

Select 'table', 5 union all

Select 'Chair', 6 union all

Select 'Computer', 3 union all

Select 'Computer', 5 union all

Select 'TV' , 20 union all

Select 'TV' , 15 union all

Select 'Radio' , 10 union all

Select 'Computer' ,10

with CTE (Object,Weight1) as

(

select Object,Weight1=cast(Weight as varchar(50)) from tbl

union all

select a.Object,Weight1=convert(varchar(24),Weight1)+','+convert(varchar(25),a.Weight)

from tbl a inner loop join CTE b

on a.Object=b.Object and patindex('%'+convert(varchar(50),a.Weight)+'%',Weight1)<1

)

select distinct Object, max(Weight1) from CTE

Group by Object

|||

--We can use CTE in SQL Server 2005. The recursive function will take care of the number of records for the same object in your table.

With MyCTE(Object, Weight, Weights, myNum) AS

(SELECT a.Object, CONVERT(varchar(50), MIN(a.Weight)) as col1, CONVERT(varchar(50),(a.Weight)) as Weights, 1 as myNum

FROM mergeTable1$ a GROUP BY a.Object, CONVERT(varchar(50),(a.Weight))

UNION ALL

SELECT b.Object, CONVERT(varchar(50), b.Weight), CONVERT(varchar(50), (c.Weights + ',' + b.Weight)), c.myNum+1 as myNum

FROM mergeTable1$ b INNER JOIN MyCTE c ON b.Object=c.Object

WHERE b.Weight>c.Weight

)

SELECT a.Object, Weights FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.Object FROM MyCTE a1

group by a1.Object) b on a.Object=b.Object AND a.myNum= b.myNumMax ORDER BY a.Object

|||

Yet another way to do it, using data from one of the other posts, and the 2005 techniques from:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

Create table tbl(Object varchar(100), Weight int)

insert tbl

Select 'table', 5 union all

Select 'Chair', 6 union all

Select 'Computer', 3 union all

Select 'Computer', 5 union all

Select 'TV' , 20 union all

Select 'TV' , 15 union all

Select 'Radio' , 10 union all

Select 'Computer' ,10

SELECT
Object,
Weights = LEFT(o.list, LEN(o.list)-1)
FROM
(select distinct object from tbl) as tbl --if these values are defined in another related table it is more clear
CROSS APPLY
(
SELECT CONVERT(VARCHAR(12), Weight) + ',' AS [text()]
FROM tbl as t
WHERE t.object = tbl.object
ORDER BY weight
FOR XML PATH('')
) o (list)

|||If you don't mind, what does MYCTE stand for ?|||CTE (Common Table Expression) is a new function in SQL Server 2005. You can look it up from Book Online. MyCTE is a name like you would call a table mytable in your database. I like Louis's solution a lot. It uses another new CROSS APPLY funtion in SQL Server 2005 too.|||

For some ideas, see:

http://www.projectdmx.com/tsql/rowconcatenate.aspx

--

Anith

|||

i belive this can help

well use this function to get retrive the string that cotaians the rows of the specific ID.

CREATE FUNCTION dbo.ConRow(@.JID int)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @.Output VARCHAR(8000)

SELECT @.Output = COALESCE(@.Output+', ', '') + CONVERT(varchar(20), JP.a)

FROM [E_JobPending] JP

WHERE JP.JobID = @.JID

RETURN @.Output

END

select dbo.ConRow(jobid), vE_Job.*

from

vE_Job

DROP FUNCTION dbo.ConRow

Concatenate Rows into Columns

I have a table 2 columns (Object and Weight) with data like below:

Object Weight(lb)
table 5
Chair 6
Computer 3
Computer 5
TV 20
TV 15
Radio 10
Computer 10

Question: I would like to create a new table with one column that would join the above two columns like below

Object
table 5
Chair 6
Computer 3, 5, 10
TV 20, 15
Radio 10

Thanks for your help in advance.

SELECT t3.Object, MAX(case t3.seq when 1 then t3.Weight end)

+ MAX(case t3.seq when 2 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 3 then ', ' + t3.Weight else '' end) AS Weight

FROM ( SELECT Object, Weight, (SELECT COUNT(*) FROM mergeTable1$ AS t2 WHERE t2.Object = t1.Object and t2.Weight <= t1.Weight) AS seq

FROM mergeTable1$ AS t1

) as t3

GROUP BY t3.Object

ORDER BY t3.Object

|||

What if I do not know the Max number of Weights for each object. eg. an object may have n number of weights in the table.

Thanks

|||

I would guess a maximum possible number in this solution. For example:

SELECT t3.Object, MAX(case t3.seq when 1 then t3.Weight end)

+ MAX(case t3.seq when 2 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 3 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 4 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 5 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 6 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 7 then ', ' + t3.Weight else '' end)

+ MAX(case t3.seq when 8 then ', ' + t3.Weight else '' end) AS Weight

FROM ( SELECT Object, Weight, (SELECT COUNT(*) FROM mergeTable1$ AS t2 WHERE t2.Object = t1.Object and t2.Weight <= t1.Weight) AS seq

FROM mergeTable1$ AS t1

) as t3

GROUP BY t3.Object

ORDER BY t3.Object

By the way, are you using SQL Server 2005? There are other solutions to handle this one.

|||

Yes, I am using sql server 2005

Thanks

|||

In SQL 2005, you can try like this

Create table tbl(Object varchar(100), Weight int)

insert tbl

Select 'table', 5 union all

Select 'Chair', 6 union all

Select 'Computer', 3 union all

Select 'Computer', 5 union all

Select 'TV' , 20 union all

Select 'TV' , 15 union all

Select 'Radio' , 10 union all

Select 'Computer' ,10

with CTE (Object,Weight1) as

(

select Object,Weight1=cast(Weight as varchar(50)) from tbl

union all

select a.Object,Weight1=convert(varchar(24),Weight1)+','+convert(varchar(25),a.Weight)

from tbl a inner loop join CTE b

on a.Object=b.Object and patindex('%'+convert(varchar(50),a.Weight)+'%',Weight1)<1

)

select distinct Object, max(Weight1) from CTE

Group by Object

|||

--We can use CTE in SQL Server 2005. The recursive function will take care of the number of records for the same object in your table.

With MyCTE(Object, Weight, Weights, myNum) AS

(SELECT a.Object, CONVERT(varchar(50), MIN(a.Weight)) as col1, CONVERT(varchar(50),(a.Weight)) as Weights, 1 as myNum

FROM mergeTable1$ a GROUP BY a.Object, CONVERT(varchar(50),(a.Weight))

UNION ALL

SELECT b.Object, CONVERT(varchar(50), b.Weight), CONVERT(varchar(50), (c.Weights + ',' + b.Weight)), c.myNum+1 as myNum

FROM mergeTable1$ b INNER JOIN MyCTE c ON b.Object=c.Object

WHERE b.Weight>c.Weight

)

SELECT a.Object, Weights FROM MyCTE a INNER JOIN (SELECT Max(a1.myNum) as myNumMax, a1.Object FROM MyCTE a1

group by a1.Object) b on a.Object=b.Object AND a.myNum= b.myNumMax ORDER BY a.Object

|||

Yet another way to do it, using data from one of the other posts, and the 2005 techniques from:

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

Create table tbl(Object varchar(100), Weight int)

insert tbl

Select 'table', 5 union all

Select 'Chair', 6 union all

Select 'Computer', 3 union all

Select 'Computer', 5 union all

Select 'TV' , 20 union all

Select 'TV' , 15 union all

Select 'Radio' , 10 union all

Select 'Computer' ,10

SELECT
Object,
Weights = LEFT(o.list, LEN(o.list)-1)
FROM
(select distinct object from tbl) as tbl --if these values are defined in another related table it is more clear
CROSS APPLY
(
SELECT CONVERT(VARCHAR(12), Weight) + ',' AS [text()]
FROM tbl as t
WHERE t.object = tbl.object
ORDER BY weight
FOR XML PATH('')
) o (list)

|||If you don't mind, what does MYCTE stand for ?|||CTE (Common Table Expression) is a new function in SQL Server 2005. You can look it up from Book Online. MyCTE is a name like you would call a table mytable in your database. I like Louis's solution a lot. It uses another new CROSS APPLY funtion in SQL Server 2005 too.|||

For some ideas, see:

http://www.projectdmx.com/tsql/rowconcatenate.aspx

--

Anith

|||

i belive this can help

well use this function to get retrive the string that cotaians the rows of the specific ID.

CREATE FUNCTION dbo.ConRow(@.JID int)

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @.Output VARCHAR(8000)

SELECT @.Output = COALESCE(@.Output+', ', '') + CONVERT(varchar(20), JP.a)

FROM [E_JobPending] JP

WHERE JP.JobID = @.JID

RETURN @.Output

END

select dbo.ConRow(jobid), vE_Job.*

from

vE_Job

DROP FUNCTION dbo.ConRow

Concatenate Rows

Hi

I have a table similar to the following:

Date ID Name Job Number JobType

12/12/2007 123456 Fred Smith 111111 Full Day

12/12/2007 654321 Bob Blue 222222 Half Day AM

12/12/2007 654321 Bob Blue 333333 Half Day PM

I need the following output:

Date ID Name Job Number JobType

12/12/2007 123456 Fred Smith 111111 Full Day

12/12/2007 654321 Bob Blue 222222 Half Day AM

12/12/2007 654321 Bob Blue 333333 Half Day PM

Now before you say the output is the same . It isn't! There are only 2 records in the output. The italic lines are one record, with a carriage return linefeed between each piece of data. So for job number the field is equal to 111111 + CHAR(10) + CHAR(13) + 222222

Could someone please point me in the right direction?

Cheers

You could to use SELECT FOR XML PAHT with empty tag:

Code Snippet

create table t2

(

Date datetime,

ID int,

Name varchar(20),

JobNumber varchar(20),

JobType varchar(20)

)

go

insert into t2 values('12/12/2007', 123456,'Fred Smith','111111','Full Day')

insert into t2 values('12/12/2007', 654321,'Bob Blue',' 222222','Half Day AM')

insert into t2 values('12/12/2007', 654321,'Bob Blue',' 333333','Half Day PM')

select

replace( (SELECT name + '##' FROM t2 as d where d.ID=m.ID FOR XML PATH('')), '##', char(10)+char(13) ) as CName

,ID from t2 m group by ID

|||Hi Kosinsky,
Your querry is not working in SQL200 is it for SQL 2005 or it will run properly in sql2000 also if not then wht will be the querry for sql2000,

I got the following error when i am trying to run your select querry in sql2000

Code Snippet

Server: Msg 170, Level 15, State 1, Line 1Line 1:

Incorrect syntax near 'XML'.

|||

My query use SELECT FOR XML PATH. Its SQL Server 2005 feature.

For SQL Server 2000 you could use FOR XML RAW and two additional replaces:

Code Snippet

select

replace

(

replace

(

replace( (SELECT name as t FROM t2 as d where d.ID=m.ID FOR XML RAW('t')), '"/><t t="', char(10)+char(13))

,'<t t="',''

)

,'"/>',''

)

,ID

from t2 m group by ID

|||Hi,
Still its giving me the same error.?

Code Snippet

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'XML'.

|||

Sorry, but me solution doesn't work on SQL Server 2000. Because FOR XML is not valid in subselections

|||

Thanks for the replies Konstantin Kosinsky, but I'm also running SQL Server 2000.

Does anyone have any other ideas on how to achieve this please?

Cheers

|||

From what I can tell, all the easy solutions for this are in SQL2005. SQL2000 solutions are much messier. Try searching this forum for words like aggregate and concatenate. There are a few that might help, like this one:

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

Note Umachandar's solution I think should work on 2000. MRys', while much neater, relies on having 2005.

Good luck.|||

Cheers Cringing Dragon, great find. I used the post on the link you provided by Umachandar Jayachandran - MS.

The SQL of which is:

Code Snippet

select t3.id
, substring(
max(case t3.seq when 1 then ',' + t3.comment else '' end)
+ max(case t3.seq when 2 then ',' + t3.comment else '' end)
+ max(case t3.seq when 3 then ',' + t3.comment else '' end)
+ max(case t3.seq when 4 then ',' + t3.comment else '' end)
+ max(case t3.seq when 5 then ',' + t3.comment else '' end)
, 2, 8000) as comments
-- put as many MAX expressions as you expect items for each id
from (
select t1.id, t1.comment, count(*) as seq
from your_table as t1
join your_table as t2
on t2.id = t1.id and t2.comment <= t1.comment
group by t1.id, t1.comment
) as t3
group by t3.id;

Thank you all for your help.

Concatenate Rows

Hi

I have a table similar to the following:

Date ID Name Job Number JobType

12/12/2007 123456 Fred Smith 111111 Full Day

12/12/2007 654321 Bob Blue 222222 Half Day AM

12/12/2007 654321 Bob Blue 333333 Half Day PM

I need the following output:

Date ID Name Job Number JobType

12/12/2007 123456 Fred Smith 111111 Full Day

12/12/2007 654321 Bob Blue 222222 Half Day AM

12/12/2007 654321 Bob Blue 333333 Half Day PM

Now before you say the output is the same . It isn't! There are only 2 records in the output. The italic lines are one record, with a carriage return linefeed between each piece of data. So for job number the field is equal to 111111 + CHAR(10) + CHAR(13) + 222222

Could someone please point me in the right direction?

Cheers

You could to use SELECT FOR XML PAHT with empty tag:

Code Snippet

create table t2

(

Date datetime,

ID int,

Name varchar(20),

JobNumber varchar(20),

JobType varchar(20)

)

go

insert into t2 values('12/12/2007', 123456,'Fred Smith','111111','Full Day')

insert into t2 values('12/12/2007', 654321,'Bob Blue',' 222222','Half Day AM')

insert into t2 values('12/12/2007', 654321,'Bob Blue',' 333333','Half Day PM')

select

replace( (SELECT name + '##' FROM t2 as d where d.ID=m.ID FOR XML PATH('')), '##', char(10)+char(13) ) as CName

,ID from t2 m group by ID

|||Hi Kosinsky,
Your querry is not working in SQL200 is it for SQL 2005 or it will run properly in sql2000 also if not then wht will be the querry for sql2000,

I got the following error when i am trying to run your select querry in sql2000

Code Snippet

Server: Msg 170, Level 15, State 1, Line 1Line 1:

Incorrect syntax near 'XML'.

|||

My query use SELECT FOR XML PATH. Its SQL Server 2005 feature.

For SQL Server 2000 you could use FOR XML RAW and two additional replaces:

Code Snippet

select

replace

(

replace

(

replace( (SELECT name as t FROM t2 as d where d.ID=m.ID FOR XML RAW('t')), '"/><t t="', char(10)+char(13))

,'<t t="',''

)

,'"/>',''

)

,ID

from t2 m group by ID

|||Hi,
Still its giving me the same error.?

Code Snippet

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'XML'.

|||

Sorry, but me solution doesn't work on SQL Server 2000. Because FOR XML is not valid in subselections

|||

Thanks for the replies Konstantin Kosinsky, but I'm also running SQL Server 2000.

Does anyone have any other ideas on how to achieve this please?

Cheers

|||

From what I can tell, all the easy solutions for this are in SQL2005. SQL2000 solutions are much messier. Try searching this forum for words like aggregate and concatenate. There are a few that might help, like this one:

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

Note Umachandar's solution I think should work on 2000. MRys', while much neater, relies on having 2005.

Good luck.|||

Cheers Cringing Dragon, great find. I used the post on the link you provided by Umachandar Jayachandran - MS.

The SQL of which is:

Code Snippet

select t3.id
, substring(
max(case t3.seq when 1 then ',' + t3.comment else '' end)
+ max(case t3.seq when 2 then ',' + t3.comment else '' end)
+ max(case t3.seq when 3 then ',' + t3.comment else '' end)
+ max(case t3.seq when 4 then ',' + t3.comment else '' end)
+ max(case t3.seq when 5 then ',' + t3.comment else '' end)
, 2, 8000) as comments
-- put as many MAX expressions as you expect items for each id
from (
select t1.id, t1.comment, count(*) as seq
from your_table as t1
join your_table as t2
on t2.id = t1.id and t2.comment <= t1.comment
group by t1.id, t1.comment
) as t3
group by t3.id;

Thank you all for your help.

Concatenate Multiple Rows?

I can't figure out how to write an SQL query that concatenates one field from a list of records having other parameters in common. It is easier to show than explain. Take the data set:

Spec T_R Section
A008 23w 1
A008 23w 2
A008 23w 4

I need a query that returns a single record/row like this:

Spec T_R Section
A008 23w 1, 2, 4

Any help would be appreciated.I've had this problem more times than I can count. While I was writing my SQL Tutorial (http://www.bitesizeinc.net/index.php/sql.html), I ran across this function for MySQL :

group_concat(field)

Which concatenates the grouped results into a string. If you are using Oracle, you'll need a stored procedure...

-Chrissqlsql

Concatenate Columm Values from multiple Rows into a single col

Yes, the order is not guaranteed.
ML
http://milambda.blogspot.com/ML (ML@.discussions.microsoft.com) writes:
> Yes, the order is not guaranteed.
Not even that. You are not even guaranteed to get all rows. For 1, 2, 3, 4
you could get '1,2,3,4' or you could get only '4'.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||That would make the function completely useless - could you give an example,
please? I've tested it with a few typical set-ups and have always found it t
o
return expected results.
ML
http://milambda.blogspot.com/|||ML (ML@.discussions.microsoft.com) writes:
> That would make the function completely useless - could you give an
> example, please? I've tested it with a few typical set-ups and have
> always found it to return expected results.
Check out http://support.microsoft.com/default.aspx?scid=287515, and pay
particular attention to the first sentence under CAUSE.
Nevermind that the article then bend over backwards, to specify things that
may work. For me the conclusion is clear: don't rely on this.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I agree using functions in the ORDER BY clause in this case is a disaster
waiting to happen, but since my function does not use them at all, were you
able to reproduce the problem anyway?
If you're too busy to play with this, I absolutely understand. I'm just
trying to learn new things every day. I promise I'll stop a few days after
I'm dead. ;)
ML
http://milambda.blogspot.com/|||ML (ML@.discussions.microsoft.com) writes:
> I agree using functions in the ORDER BY clause in this case is a
> disaster waiting to happen, but since my function does not use them at
> all, were you able to reproduce the problem anyway?
My point is not that I can reproduce it here and now. My point is that
what works today, could break tomorrow.
For instance, there are people out there who have defined views in
SQL 2000 which goes:
SELECT TOP 100 PERCENT
..
ORDER BY
and they are happy because when they say:
SELECT * FROM view1
the see the data in order.
Then they move to SQL 2005 and get hit, because the optimizer is now
less likely to return the data in order. The truth was all the time
that without an ORDER BY, the order of the data is undefined.
See also
http://lab.msdn.microsoft.com/produ...b9-3dd863ae6b1c
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||This bug report clears up the matter greatly. Thank you very much. I intend
to include this example in my blog as a warning ASAP.
I've searched the web for this issue, but found no usable references. Thanks
again.
ML
http://milambda.blogspot.com/

concate comments from different rows

Posted - 01/08/2007 : 17:36:50

---

Here is my example.

data
ID CommentID Comments
1 1 'app'
1 2 'le'
2 1 'or'
2 2 'an'
2 3 'ge'
3 1 'banana'

results want to get
1 apple
2 orange
3 banana

I was thinking to using the PIVOT, but as the number of row is unknown, I think it can't be used.

any help is appreciate!

I would seriously consider not doing this in SQL. It will be much easier to do in the presentation layer (and you won't have any issues with the size of the data, either)

You could build a user defined function and cursor through the comments ordered by the commentID (assuming that is the order) also, but unless you actually need the results in another SQL statement, using the presentation layer would be the best way to go

|||

I understand that you are using SQL Server 2005..

Here CTE is best solution rather than UI..

In UI you have to Loop or need to use DataViews.. It is bit expensive than SQL query..

Try the following Query it will help you..

create table comments
(
Id int,
CommentId int,
Comments varchar(100)
)
Go
Insert into comments values (1,1,'app')
Insert into comments values (1,2,'le')
Insert into comments values (2,1,'or')
Insert into comments values (2,2,'an')
Insert into comments values (2,3,'ge')
Insert into comments values (3,1,'banana')
Go
WITH JoinComment (Id,CommentId,Comments,MaxId)
as
(
Select A.id,A.CommentId,convert(varchar(8000),Comments) Comments,Max(commentId) Over (partition By Id) as maxId From Comments a
Union All
Select A.Id, A.CommentId, A.Comments + B.Comments as Comments,A.CommentId
From
Comments A Inner Join JoinComment B
On (A.CommentId+1)=B.CommentId
And A.CommentId <> B.CommentId
and B.CommentId = B.maxId
And A.Id=B.Id
)
Select Id,CommentId,Comments from JoinComment
Where CommentId=MaxId And CommentId=1

|||SET
NOCOUNT ON

DECLARE
@.T AS TABLE(y nvarchar(20) NOT NULL PRIMARY KEY)

INSERT
INTO @.T SELECT DISTINCT CommentID FROM comments
DECLARE
@.T1 AS TABLE(num int NOT NULL PRIMARY KEY)

DECLARE @.i AS int

SET @.i=1
WHILE @.i <20

BEGIN

INSERT INTO @.T1 SELECT @.i

SET @.i=@.i+1

END

DECLARE @.cols AS nvarchar(MAX), @.cols2 AS nvarchar(MAX),@.y AS nvarchar(20)

SET @.y = (SELECT MIN(y) FROM @.T)

SET @.cols = N''
SET @.cols2 = N''

WHILE @.y IS NOT NULL

BEGIN

SET @.cols = @.cols + N',['+CAST(@.y AS nvarchar(20))+N']'
SET @.cols2 = @.cols2 + N'+ coalesce(['+CAST(@.y AS nvarchar(20))+N'],'''')'

SET @.y = (SELECT MIN(y) FROM @.T WHERE y > @.y)

END

SET @.cols = SUBSTRING(@.cols, 2, LEN(@.cols))
SET @.cols2 = SUBSTRING(@.cols2, 2, LEN(@.cols2)-1)
DECLARE @.sql AS nvarchar(MAX)

SET @.sql = N'SELECT ID' + N',(' +@.cols2 + N') AS newColumn FROM (SELECT ID, CommentID, Comments FROM comments) as t

PIVOT (min(comments) FOR CommentID IN(' + @.cols + N')) AS pvt'

EXEC sp_executesql @.sql|||

Hi Terrence,

Here is my solution.

I assume that the comments table named as "z"

Also note that I'm using a user defined function named dbo.Split() which you can find its code from

http://www.kodyaz.com/forums/489/ShowThread.aspx#489

declare @.s varchar(100), @.i tinyint, @.t tinyint

select @.t = 0, @.i = min(id), @.s = CAST(@.i as varchar(5)) + '-' from z

select

@.t = case when @.i = id then 0 else 1 end,

@.s = coalesce(@.s + case when @.t = 1 then ';' + CAST(id as varchar(5)) + '-' else '' end + comments , ''),

@.i = id

from z

order by id, commentid

-- select @.s

select

SUBSTRING(strval,0, CHARINDEX('-', strval)) AS Id,

SUBSTRING(strval, CHARINDEX('-', strval) + 1, LEN(strval) - 2) AS Comment

from dbo.Split(@.s,';')

Eralper

http://www.kodyaz.com

|||As Louis pointed out, you will be better off doing this on the client side. Any server solution will perform poorly and look complicated. Is there any reason why you are storing the comments as multiple rows? How do the comments gets split across rows? What happens if the comments get edited? Do you delete all the rows and reenter them? It is best you redesign the schema to use a simple comments column of varchar(8000), varchar(max), nvarchar(4000) or text as appropriate. Suggesting a SQL solution is easy actually but it is not the right way. As you suggested, you can use PIVOT in SQL Server 2005 without dynamic SQL by fixing the maximum number of comment fragments.|||

Here is my table schema

ApplicationComment Table

-AppId int (Key)

-CommentId int(Key)

-Comments

-ModifiedDate

-ModifiedBy

What I am doing is creating a stored procedure(sp) for a report using reporting services, so I think concate the comments in a sp (rather than the presentation layer) is more appropriate.

Here is the sample data

1,1, 'This is the first comments',.....

1,2, 'This is the second comments for the application Id 1',.....

1,3,'The application is closed',.....

In the front end, the modified date, modified by and and comments are shown in a datagrid.

01/01/2007 Tester1 'This is the first comments'

01/02/2007 Tester2 'This is the second comments for the application Id 1'

In the comments summary report, what I want is this

Application ID , Comments

1, This is the first comments

This is the second comments for the application Id 1

The application is closed

2, .....

Umachandar, I am more than happy to take any suggestion that you may have... Thanks.

|||

I don't know enough about Reporting Services to comment on it's features. But these type of reports are trivial to generate in Crystal Reports for example (one I am familiar with and used in the past). You can just issue a query like:

select a.AppId, a.Comments

from ApplicationComment as a

order a.AppId, a.CommentId

And suppress repeating values in the report. That is all there is to it. You can also do simple grouping to provide a header and several detail rows kind of report. Anyway, it seems like you should ask in the Reporting Services newsgroup about how to generate such a formatted report.

You can use SQL to generate the result set like:

select pa.AppId

, pa.[1] + coalesce(pa.[2], '') + coalesce(pa.[3], '')...... as Comments

from (select AppId, CommentId, Comments from ApplicationComment) as a

pivot (min(a.Comments) for a.CommentId in ([1], [2], [3], ....

/* fix some maximum number here. 100 or 1000 it doesn't matter*/

)) as pa

order by pa.AppId

Note that you may have to cast one of the expressions for the Comments column to varchar(max) or nvarchar(max) if the maximum length of the comments can exceed 8000 bytes. Also, the performance of the query will be poor due to the string concatenations and it will be directly proportional to the number of rows in the table & number of comments per id.

|||

Here is my solution using UDF as it looks more clearer for me, but as most of the expert explains don't do in the server side.

I finally put that in the presentation layer because of the poor perfermance.

create function dbo.uf_ConcateComments(

@.ApplicationId INT

)

returns varchar(max)

as

begin

declare

@.Return as VARCHAR(max),

@.newline as char(2)

set @.Return = ''

set @.newline = char(13) + char(10)

select

@.Return = @.Return + @.newline + @.newline +

cast(DATEPART(dd,t.CreatedDate)as varchar(2)) + '/' +

cast(DATEPART(mm,t.CreatedDate)as varchar(2)) + '/' +

cast(DATEPART(yyyy,t.CreatedDate)as varchar(4)) + ' ' +

t.CreatedBy + ': ' + t.Comments

from

dbo.ApplicationComment t

where t.ApplicationId = @.ApplicationId

order by

t.CommentID

return (@.Return)

end

GO

In a store procedure, do this.

SELECT

ApplicationId,

dbo.uf_ConcateComments(ApplicationId) as concateComments

from

Application x

ORDER BY

ApplicationId desc

|||

>> I finally put that in the presentation layer because of the poor perfermance.<<

That is the best way, in my opinion, as it is very natural for the presentation layer to pass through each row individually, since this is how you end up doing it anyhow (even if you let some object built into some library handle your data)

Concatanating 2 or more rows

Hi, Sounds simple but I can not figure this out.
I have 5 rows of data sharing a common id in my table.

ID NAME
22 Rick
22 John
22 Paul
22 Tom
22 Mary

The result I want on 1 line is:
Rick, John, Paul, Tom and Mary.

How can I make this so?

RickI am using CR XI, if this data is in details, go to details, select section expert, select format with multiple columns, you should see a layout tab pop up, select the size and direction you want your data to flow in, also, you may select format groups with multiple columns. If this is not what you were looking for... you can do this. Create a formula.
data1 &" "& data2 &" "& data3 &" "& data4 etc...|||Group on ID.
Create 3 formulas:
1) place in group header, suppress the formula
whileprintingrecords;
stringvar names := "";

2) place in details, suppress the section
whileprintingrecords;
stringvar names;
names := names & ", " & {table.field);

3) place in group footer
whileprintingrecords;
stringvar names;
mid(names, 2)|||One minor typo; the group footer formula should say
mid(names, 3)
or you'll have a leading space.

I've not addressed your requirement to replace the last comma with the word 'and', but you can probably work that one out yourself.

Concat tables into one row in view

If I have table1 and table2 with table2 having multiple rows tied to a
single row in table 1.

What I am trying to do is set up a view that has one row that shows
the following
table1.uniqueid, table1.name, table2.row1.detail, table2.row2.detail,
table2.row3.detail

I'd like to be able to do a select on the view and only come back with
one row per widget. If possible, I'd actually like to be able to
concat all the rows from table 2 into one column if that's possible.

table1.uniqueid, table1.name, (table2.row1.detail - table2.row2.detail
- table2.row3.detail), table1.dateCreated

thx
M@.M@. (mattcushing@.gmail.com) writes:

Quote:

Originally Posted by

If I have table1 and table2 with table2 having multiple rows tied to a
single row in table 1.
>
What I am trying to do is set up a view that has one row that shows
the following
table1.uniqueid, table1.name, table2.row1.detail, table2.row2.detail,
table2.row3.detail
>
I'd like to be able to do a select on the view and only come back with
one row per widget. If possible, I'd actually like to be able to
concat all the rows from table 2 into one column if that's possible.
>
table1.uniqueid, table1.name, (table2.row1.detail - table2.row2.detail
- table2.row3.detail), table1.dateCreated

SQL Server MVP Anith Sen has a couple of methods on
http://www.projectdmx.com/tsql/rowconcatenate.aspx.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsqlsql

Concat rows into string

I have a simple select statement - select groupname FROM Groups
This returns 30 rows. What I want is return everything as comma
seperated string like "group1, group2, group3..."
But I don't want to use function or cursor. Is there anyway? In on
SQL 2000.
thanksPlease have a look at this example:
http://p2p.wrox.com/topic.asp?TOPIC_ID=57982
Cheers,
Paul Ibison

Concat rows into string

I have a simple select statement - select groupname FROM Groups
This returns 30 rows. What I want is return everything as comma
seperated string like "group1, group2, group3..."
But I don't want to use function or cursor. Is there anyway? In on
SQL 2000.
thanksPlease have a look at this example:
http://p2p.wrox.com/topic.asp?TOPIC_ID=57982
Cheers,
Paul Ibison

Concat

Hi
I want to Concat Rows of a Column in a Table with One "Select" Order (I mean without use cursor)

Example:

Table1:
Column1
----
'a'
'b'
'c'

Result:
'abc'

Please answer me

thanks alotif you're using MySQL, use the GROUP_CONCAT function

if you're using Sybase ASE, use the LIST function

otherwise, a cursor is actually not a bad idea, because other database systems don't have a similar aggregate functionsqlsql

compute diffrence

Hi all
I have a table like this
Code Price Type
1 100 1
2 300 0
1 1500 0
3 50 0
4 100 1
1 50 1
I want to compute the Sum of diffrence price between those rows that have
diffrent Type group by their Code..
How can I do that'
thxselect
(
select sum(price) from tablex group by type
where type=1
) -
(
select sum(price) from tablex group by type
where type=2
)
as difference
"perspolis" wrote:

> Hi all
> I have a table like this
> Code Price Type
> 1 100 1
> 2 300 0
> 1 1500 0
> 3 50 0
> 4 100 1
> 1 50 1
> I want to compute the Sum of diffrence price between those rows that have
> diffrent Type group by their Code..
> How can I do that'
> thx
>
>|||Jose,
Your SQL statement will not compile because you use where after group
by.

> select
> (
> select sum(price) from tablex group by type
> where type=1
> ) -
> (
> select sum(price) from tablex group by type
> where type=2
> )
> as difference
Also, I think the original poster wants the sum of difference between
those rows that have different Type group by their code. Thank you.
"jose g. de jesus jr mcp, mcdba" wrote:
> select
> (
> select sum(price) from tablex group by type
> where type=1
> ) -
> (
> select sum(price) from tablex group by type
> where type=2
> )
> as difference
>
>
> "perspolis" wrote:
>|||sorry no sql here. the idea is there though use subquery
select
(
select sum(price) from tablex where type=1
group by type
) -
(
select sum(price) from tablex where type=2
group by type
)
as difference
"frank chang" wrote:
> Jose,
> Your SQL statement will not compile because you use where after grou
p
> by.
>
> Also, I think the original poster wants the sum of difference between
> those rows that have different Type group by their code. Thank you.
>
> "jose g. de jesus jr mcp, mcdba" wrote:
>|||no that's not right..
I want to group by Code not type
<jose g. de jesus jr mcp>; "mcdba"
<josegdejesusjrmcpmcdba@.discussions.microsoft.com> wrote in message
news:87147408-0BA3-4E4E-8A1F-A6E811A0882D@.microsoft.com...
> sorry no sql here. the idea is there though use subquery
> select
> (
> select sum(price) from tablex where type=1
> group by type
> ) -
> (
> select sum(price) from tablex where type=2
> group by type
> )
> as difference
>
>
> "frank chang" wrote:
>
group
have|||On Tue, 23 Aug 2005 15:35:04 +0430, perspolis wrote:

>Hi all
>I have a table like this
>Code Price Type
> 1 100 1
> 2 300 0
> 1 1500 0
> 3 50 0
> 4 100 1
> 1 50 1
>I want to compute the Sum of diffrence price between those rows that have
>diffrent Type group by their Code..
>How can I do that'
>thx
>
Hi perspolis,
Thanks for posting sample data. Unfortunately, I'm not sure what your
requirements are. Maybe you can improve my understanding by posting the
output you'd expect from the sample data above?
Also, posting the table structure as CREATE TABLE statement and the
sample data as INSERT statements would make it easier for me to test my
solutions.
Check out www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||The output is like this
Code Total
1 1350
2 300
3 50
4 -100
thanks
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:be5ng1htpq5m0gcgubq8ortulkvs6dcmq0@.
4ax.com...
> On Tue, 23 Aug 2005 15:35:04 +0430, perspolis wrote:
>
> Hi perspolis,
> Thanks for posting sample data. Unfortunately, I'm not sure what your
> requirements are. Maybe you can improve my understanding by posting the
> output you'd expect from the sample data above?
> Also, posting the table structure as CREATE TABLE statement and the
> sample data as INSERT statements would make it easier for me to test my
> solutions.
> Check out www.aspfaq.com/5006.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||can you pls enlightened me more
how did code 1 got 1350 and
how did 4 get -100
"perspolis" wrote:

> The output is like this
> Code Total
> 1 1350
> 2 300
> 3 50
> 4 -100
> thanks
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:be5ng1htpq5m0gcgubq8ortulkvs6dcmq0@.
4ax.com...
>
>|||to get this output for example for Code 1
Add Sum(Price) of those rows with Type=0 and Code=1 minus Sum( Price) of
those rows with Type=1 and Code=1 to get 1500-(100+50)= 1350 for
Code=1
"jose g. de jesus jr mcp, mcdba"
<josegdejesusjrmcpmcdba@.discussions.microsoft.com> wrote in message
news:4CF3C365-1AA9-4417-AF1F-2346915FE922@.microsoft.com...
> can you pls enlightened me more
> how did code 1 got 1350 and
> how did 4 get -100
>
> "perspolis" wrote:
>|||persoplis, This is a first cut but it shows the desired results
select a1.code, a1.sumprice - a2.sumprice
FROM (select t1.code, sum(t1.price) as sumprice from xyz t1
group by t1.code, t1.type ) AS a1
JOIN
(select t1.code, sum(t1.price) as sumprice from xyz t1
group by t1.code, t1.type ) AS a2
ON a1.code = a2.code and a1.sumprice > a2.sumprice
UNION
select a1.code, a1.sumprice
from (select t1.code, sum(t1.price) as sumprice from xyz t1
group by t1.code, t1.type ) AS a1
where (select count(*) from
(select t1.code, sum(t1.price) as sumprice from xyz t1
group by t1.code, t1.type ) AS a2
where a1.code = a2.code) = 1
This is the DDL I used:
Create Table XYZ
(
Code int,
Price int,
Type int
)
go
INSERT into XYZ values (1,100,1)
INSERT into XYZ values (2,300,0)
INSERT into XYZ values (1,1500,0)
INSERT into XYZ values (3,50,0)
INSERT into XYZ values (4,100,1)
INSERT into XYZ values (1,50,1)
INSERT into XYZ values (5,200,1)
INSERT into XYZ values (5,2500,0)
INSERT into XYZ values (5,250,1)
go
"perspolis" wrote:

> The output is like this
> Code Total
> 1 1350
> 2 300
> 3 50
> 4 -100
> thanks
> "Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:be5ng1htpq5m0gcgubq8ortulkvs6dcmq0@.
4ax.com...
>
>

complex tables - want to join to get one row of results from multiple rows

Hi there

This is a hard problem that I have - I have only been using sql for a couple of weeks and have gone past my ability level quickly! The real tables are complex but I will post a simple and a real version with the hope someone can help me.

Any help would be much appreciated - I would also be happy to pay someone to actually do it if it takes time to work out as I know that its hard when all your help is free :)
================================================
SIMPLE VERSION
Table 1 Dog breeds
dogbreedID, dogBreedName, colour
1,labrador,golden
2,beagle, tricolour
3,great dane, marle

Table 2 - maps criteria to dog breeds
dogbreedID, criteriaID, value, location
3,2,easy to train, c:/filepath2
1,1,good with children, c:/filepath
1,2,easy to train, c:/filepath2
2,1,good with children, c:/filepath
3,3,stranger friendly, c:/filepath3

So that leads to table 3 sitting behind the scenes not used in this query:
criteriaID, value, location
1,good with children, c:/filepath
2,easy to train, c:/filepath2
3,stranger friendly, c:/filepath3

I want a view that has the following:
dogbreedID, dogBreedName, colour, criteriaID1, value1, location1, criteriaID2, value2, location2, criteriaID3, value3, location3,criteriaID4, value4, location4

1,labrador,golden,1,good with children, c:/filepath,2,easy to train, c:/filepath2,NULL,NULL,NULL,NULL, NULL, NULL

2,beagle, tricolour,1,good with children,NULL, NULL, NULL,NULL, NULL, NULL,NULL, NULL, NULL

3,great dane, marle,NULL, NULL, NULL,2,easy to train, c:/filepath2,3,stranger friendly, c:/filepath3

================================================== =====
more complicated view - you can see each table is actually a combination of table values but I dont think that matters to this problem - the above example is fine but I am not very good with this so may have left somehting out that you can derive from the example below:
Table 1:
SELECT distinct dbo.BREED_dogBreeds.breedId, dbo.BREED_dogBreeds.breedName, dbo.BREED_dogBreeds.alternativeName, dbo.BREED_dogBreeds.shortDesc,
dbo.BREED_dogBreeds.katShortDesc, dbo.BREED_dogBreeds.longDesc, dbo.BREED_dogBreeds.katLongDesc,
dbo.BREED_dogBreeds.thingsToConsider, dbo.BREED_dogBreeds.temperament, dbo.BREED_dogBreeds.history, dbo.BREED_dogBreeds.feeding,
dbo.BREED_tblCountry.Country_Name, dbo.BREED_dogBreeds.colour, dbo.BREED_dogBreeds.breedProfileLink, dbo.BREED_Grooming.groomText,
dbo.BREED_GroomFrequencyValues.value, dbo.BREED_Training.intelligence, dbo.BREED_Training.trainingNotes, dbo.BREED_Training.exerciseText,
dbo.BREED_Training.exerciseTime, dbo.BREED_Training.timePerDay, dbo.BREED_Suitability.idealOwner, dbo.BREED_Size.size,
dbo.BREED_sizes.bheightmin, dbo.BREED_sizes.bheightmax, dbo.BREED_sizes.bweightmin, dbo.BREED_sizes.bweightmax,
dbo.BREED_sizes.dheightmin, dbo.BREED_sizes.dheightmax, dbo.BREED_sizes.dweightmin, dbo.BREED_sizes.dweightmax,
dbo.BREED_Sociability.compatibility FROM dbo.BREED_Sociability RIGHT OUTER JOIN
dbo.BREED_dogBreeds ON dbo.BREED_Sociability.sociabilityID = dbo.BREED_dogBreeds.sociabilityID LEFT OUTER JOIN
dbo.BREED_Size RIGHT OUTER JOIN
dbo.BREED_sizes ON dbo.BREED_Size.id = dbo.BREED_sizes.sizeid ON
dbo.BREED_dogBreeds.sizeId = dbo.BREED_sizes.breedSizeId LEFT OUTER JOIN
dbo.BREED_Suitability ON dbo.BREED_dogBreeds.suitabilityID = dbo.BREED_Suitability.suitabilityID LEFT OUTER JOIN
dbo.BREED_Training ON dbo.BREED_dogBreeds.trainID = dbo.BREED_Training.trainID LEFT OUTER JOIN
dbo.BREED_GroomFrequencyValues RIGHT OUTER JOIN
dbo.BREED_Grooming ON dbo.BREED_GroomFrequencyValues.gfvID = dbo.BREED_Grooming.gfvID ON
dbo.BREED_dogBreeds.groomID = dbo.BREED_Grooming.groomID LEFT OUTER JOIN
dbo.BREED_tblCountry ON dbo.BREED_dogBreeds.country = dbo.BREED_tblCountry.Country_ID

Table 2:
SELECT [breedId]
,[breedCriteriaID]
,[value]
,[icon]
FROM [v1vw1n_dogmatch].[dbo].[vbreedCriterias]
where levelID>=3
order by breedId, breedCriteriaID asc

TABLE 3
SELECT [breedCriteriaID]
,[criteriaValue]
,[icon]
FROM [v1vw1n_dogmatch].[dbo].[BREED_Criteria]

Table1
186Afghan HoundTazi, Baluchi HoundA strikingly beautiful dog with dignified poise.Afghans are kept primarily as show dogs and can also be used for lure coursing. They are extremely loving and loyal to their owners and are gentle souled and good with children. Afghans can make companion dogs but their considerable needs means that only devoted owners keep them. Aloof with strangers but affectionate and loyal to their owners. Can be very clown like at play and are very people oriented. Love children and being included in family life. Can become introverted if excluded from social situations whilst pups.An ancient breed, the afghan looks as classy as its pedigree. Afghans were used in Afghanistan to protect the flocks and would hunt and kill panthers, leopards and other large predators. The first dog to be shown was in the UK in 1907 having previously been banned from export. Afghans can be fussy with their food and it is better to instill good eating habits when they are pups and ideally treats should be avoided. AfghanistanAfghans are a grooming salons dream come true - they demand regular grooming and can quickly become knotted and tangled without it. DailyAll dogs are bright but Afghans may not quite earn the MENSA of the dog world.Afghans can be hard to train with lots of perseverance needed. Highly strung, stubborn and sensitive natured dog making them difficult to train. Training cannot be rushed and as they are sensitive souls and it is very important not treat them harshly. Sometimes difficult to housebreak. As puppies, Afghans often appear awkward, with uneven growth, gawkiness and loose limbs, and for this reason, exercise must be carefully monitored to avoid injury to their growing bones.00Suitable for the experienced, comitted dog owner with time on their hands and a great love for the breed.large63cm69cm23kg25kg68cm74cm25kg (55lb)28kg (62lb)They are hunting stock and love to chase anything that moves so perhaps not the ideal dog to share a home with cats and small animals!
187Bluetick CoonhoundNULLNot found in Australia, the Bluetick Coonhound is a friendly hound that makes an excellent tracking or scent dog.NULLThis breed originated in the states and has a smooth, dense, tri colour coat. The base colour is white with heavy ticking of black and tan markings over their chest, eyes, muzzle, lower legs and feet. This breed is recognised as a competitive, fearless, dedicated hunter. The Bluetick has a typical hound bawl and so is not the quietest of dogs.NULLThis dog needs a lot of exercise. They love to do jobs, and keep busy. They need to be exercised vigorously or they run the danger of becoming destructive and will howl excessivly.The Coonhound is deeply devoted, fearless, attentive and loyal. They make very good guardians and family companions. They are reserved with strangers, but are not aggressive towards them. They are not the best with other animals, and get along better with older, considerate children.The Bluetick originated in Louisiana at the beginning of the 20th century. They were developed from crosses between the English Coonhound, the Foxhound and the french Grand Bleu de Gascogne. Their tricoloured, blue-speckled coat sets them apart from other Coonhound breeds. Originally they were registered as a variant of the English Coonhound, but in 1946 they were given separate recognition as a distinct breed in their own right.Today the English Coonhound is sometimes referred to as the Redtick. The original, old-fashioned Bluetick dog, which was larger and slower than the modern type, began to loose ground because they did less well in the increasingly popular field competitions and night trials. The smaller, faster version began to eclipse them and became one of the most popular and numerous of all coonhound breeds. This upset the traditionalists, who preferred the old-style Big Blue , and some of them reacted by switching their allegiance to other large-bodied breeds, such as the Blue Gascon, and the Majestic.This breed is not fussy when it comes to their diet, but they have quite a healthy appetite.United States of AmericaNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULLNULL
188Australian Cattle DogCattledog, Queensland Heeler, Heeler, Blue Heeler, Red Heeler, BlueyA strong compact working dog, the Australian Cattle Dog is one of the most popular breeds of dog in Australia.A strong compact working dog, the Australian Cattle Dog is one of the most popular breeds of dog in Australia.The Australian Cattle Dog is a strong compact working dog with a combination of substance, power, balance and hard muscular condition that conveys the impression of great agility, strength and endurance with the ability and willingness to carry out his allotted task however arduous. His head is wedge shaped with a broad skull and muscular cheeks and oval shaped, dark brown eyes that express alertness and intelligence and have a warning or suspicious glint when approached by strangers. His ears are broad at base, muscular, pricked and moderately pointed. His rain resistant coat is a smooth double coat with a close, hard outer coat and a short dense undercoat AND comes in two colours: Blue: Blue, blue-mottled or blue speckled with tan markings. Red: An even red speckle all over, including the undercoat, with or without darker red markings on the head. The pups are born white, developing their colour gradually from approximately three weeks of age. The Australian Cattle Dog is a strong compact working dog with a combination of substance, power, balance and hard muscular condition that conveys the impression of great agility, strength and endurance with the ability and willingness to carry out his allotted task however arduous. His head is wedge shaped with a broad skull and muscular cheeks and oval shaped, dark brown eyes that express alertness and intelligence and have a warning or suspicious glint when approached by strangers. His ears are broad at base, muscular, pricked and moderately pointed. His rain resistant coat is a smooth double coat with a close, hard outer coat and a short dense undercoat AND comes in two colours: Blue: Blue, blue-mottled or blue speckled with tan markings. Red: An even red speckle all over, including the undercoat, with or without darker red markings on the head. The pups are born white, developing their colour gradually from approximately three weeks of age. The Australian Cattle Dog has a strong herding instinct and when playing may nip the heels of children.The Australian Cattle Dog needs a job, companionship and activity for the mind and body, all day, every day. Easily bored, he can become noisy and/or destructive. Renowned for his protectiveness and loyalty to master and property, he is very selective as to who is friend or foe. In 1840, Thomas Hall, a landowner in New South Wales, imported two smooth-haired blue merle Scotch Collies and crossed their progeny with the Dingo. The resulting litters became known as Hall's Heelers. The progeny were generally of Dingo type with the colour being either red or blue merle and were valued for their ability to handle wild cattle, stamina to travel great distances over all types of terrain, and their endurance in extremes of temperature. Later, Hall's Heelers were crossed with a Dalmatian, which changed the merle colour to red or blue speckle and instilled in the dogs a love of horses and protectiveness toward master and property. A further cross was made to the Kelpie to produce highly intelligent, controllable workers resembling thickset Dingoes and with peculiar markings known to no other dog. In 1903 a standard for the breed was drawn up and from these beginnings the Australian Cattle Dog has developed into one of the most popular breeds of dog in Australia today. AustraliaMinimal grooming although weekly brushing is required to remove dead hair. Does shed seasonally.Weekly - at homeHe is loving, playful, and eager to please his owner. Very quick to learn.Unless you can give him extensive exercise do not even consider owning an Australian Cattle Dog. He was bred to work all day in hard conditions and will become bored if not given sufficient exercise.602Single guy or gal, athletic, able to have the dog with them most of the time. Not a first time dog owner. Families with older children.medium43cm (17")48cm (19")16kg (35lb)20kg (44lb)46cm (18")51cm (20")16kg (35lb)20kg (44lbs)Not good with dogs of the same sex. OK with cats if raised with the cat since puppyhood. Not good with other small mammals.

TABLE 2
1861suits older children/Images/icons/older_children.gif
1862suits younger children/Images/icons/suits_young_children.gif
1863suits elderly/disabled. Not too boisterous/Images/icons/suits_elderly_disabled.gif
1866easy to transport/Images/icons/transport.gif
1868grooming needs/Images/icons/grooming_needs.gif
18610distress/destruction when left alone/Images/icons/distressed_when_alone.gif
18612Shedding / Hair loss/Images/icons/shedding_hairloss.gif
18615Energy Level/Images/icons/energy_levels.gif
18616Affection with family/Images/icons/affectionate.gif
18622may bite intruder/Images/icons/bite_intruder.gif
18623watchdog skills/Images/icons/watchdog_skills.gif
1871suits older children/Images/icons/older_children.gif
1876easy to transport/Images/icons/transport.gif
18710distress/destruction when left alone/Images/icons/distressed_when_alone.gif
18712Shedding / Hair loss/Images/icons/shedding_hairloss.gif
18713stranger friendly/Images/icons/stranger_friendly.gif
18715Energy Level/Images/icons/energy_levels.gif
18716Affection with family/Images/icons/affectionate.gif
18722may bite intruder/Images/icons/bite_intruder.gif
18723watchdog skills/Images/icons/watchdog_skills.gif
1881suits older children/Images/icons/older_children.gif
1883suits elderly/disabled. Not too boisterous/Images/icons/suits_elderly_disabled.gif
1884cattle friendly/Images/icons/cattle_friendly.gif
1886easy to transport/Images/icons/transport.gif
18810distress/destruction when left alone/Images/icons/distressed_when_alone.gif
18811trainability/Images/icons/trainable.gif
18812Shedding / Hair loss/Images/icons/shedding_hairloss.gif
18813stranger friendly/Images/icons/stranger_friendly.gif
18814How often this breed is found in the pound/Images/icons/found_in_pount.gif
18815Energy Level/Images/icons/energy_levels.gif
18816Affection with family/Images/icons/affectionate.gif
18819Availability/Images/icons/availability.gif
18820cat friendly/Images/icons/cat_friendly.gif
18822may bite intruder/Images/icons/bite_intruder.gif
18823watchdog skills/Images/icons/watchdog_skills.gif
1891suits older children/Images/icons/older_children.gif
1892suits younger children/Images/icons/suits_young_children.gif
1898grooming needs/Images/icons/grooming_needs.gif
18910distress/destruction when left alone/Images/icons/distressed_when_alone.gif
18912Shedding / Hair loss/Images/icons/shedding_hairloss.gif
18913stranger friendly/Images/icons/stranger_friendly.gif
18914How often this breed is found in the pound/Images/icons/found_in_pount.gif
18915Energy Level/Images/icons/energy_levels.gif
18916Affection with family/Images/icons/affectionate.gif
18919Availability/Images/icons/availability.gif
18923watchdog skills/Images/icons/watchdog_skills.gif
1901suits older children/Images/icons/older_children.gif
1903suits elderly/disabled. Not too boisterous/Images/icons/suits_elderly_disabled.gif
1906easy to transport/Images/icons/transport.gif
19010distress/destruction when left alone/Images/icons/distressed_when_alone.gif
19011trainability/Images/icons/trainable.gif
19013stranger friendly/Images/icons/stranger_friendly.gif
19014How often this breed is found in the pound/Images/icons/found_in_pount.gif
19015Energy Level/Images/icons/energy_levels.gif
19016Affection with family/Images/icons/affectionate.gif
19019Availability/Images/icons/availability.gif
19022may bite intruder/Images/icons/bite_intruder.gif
19023watchdog skills/Images/icons/watchdog_skills.gif
19024Gay Icon/Images/icons/gay_icon.gif
1921suits older children/Images/icons/older_children.gif
1922suits younger children/Images/icons/suits_young_children.gif
1923suits elderly/disabled. Not too boisterous/Images/icons/suits_elderly_disabled.gif
1924cattle friendly/Images/icons/cattle_friendly.gif
1925bunny/guinea pig friendly/Images/icons/bunny_friendly.gif
1926easy to transport/Images/icons/transport.gif
19210distress/destruction when left alone/Images/icons/distressed_when_alone.gif
19211trainability/Images/icons/trainable.gif
19212Shedding / Hair loss/Images/icons/shedding_hairloss.gif
19213stranger friendly/Images/icons/stranger_friendly.gif
19215Energy Level/Images/icons/energy_levels.gif
19216Affection with family/Images/icons/affectionate.gif
19217dog friendly/Images/icons/dog_friendly.gif
19219Availability/Images/icons/availability.gif
19220cat friendly/Images/icons/cat_friendly.gif
19223watchdog skills/Images/icons/watchdog_skills.gif

etcHi there

I got a response on another board. Basically I just used
SELECT dogBreedID,
crit1value = MIN(CASE criteriaID WHEN 1 THEN value END),
crit1location = MIN(CASE criteriaID WHEN 1 THEN location END),
crit2value = MIN(CASE criteriaID WHEN 2 THEN value END),
crit2location = MIN(CASE criteriaID WHEN 2 THEN location END),
...
FROM tbl
GROUP BY dogBreedID

and joined that to the first table.

Fantastic!

Complex report, I think

Hi,
I have a report that is basically a Matrix report that has totalling rows
throughout the vertical aspect of the report (not just at the end) and a
totalling columns at the end. Can Reporting Services deal with this using
the toolbox and so on, or do I need to get coding? I am pretty sure the
wizard aint going to help. I have split my data into a number of datasets
and hidden the column headings on all but the top Matrix, but then how do I
add the totalling rows? Especially as they need to be the sum of a number
of fields up the length of the column that aren't even contiguous. I
basically want to add totals at various places that I define and define
which values should be added. I know that there will always be the same row
and column headings popping up each time, so from that side there is no
problem.
Please give me some direction on this as I am sort of looking at this a bit
:-s at the moment.
TIA,
JarrydOn Jul 3, 5:23 am, "Jarryd" <jar...@.community.nospam> wrote:
> Hi,
> I have a report that is basically a Matrix report that has totalling rows
> throughout the vertical aspect of the report (not just at the end) and a
> totalling columns at the end. Can Reporting Services deal with this using
> the toolbox and so on, or do I need to get coding? I am pretty sure the
> wizard aint going to help. I have split my data into a number of datasets
> and hidden the column headings on all but the top Matrix, but then how do I
> add the totalling rows? Especially as they need to be the sum of a number
> of fields up the length of the column that aren't even contiguous. I
> basically want to add totals at various places that I define and define
> which values should be added. I know that there will always be the same row
> and column headings popping up each time, so from that side there is no
> problem.
> Please give me some direction on this as I am sort of looking at this a bit
> :-s at the moment.
> TIA,
> Jarryd
The most common way I have dealt w/this same issue is to use either
cursors or while loops to loop through the dataset in the stored
procedure/query that is sourcing the report (usually one cursor/while
loop per desired subtotal/etc). It can get a bit messy; however, it
does get the job done. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi,
Yeah I suppose that is the way. Just kind of a bit long winded and I was
just hoping I could get the basic Matrix and use tools to add things like
totaling rows and columns in with a bit more control and flexability as what
is available. Cos what I am going to have to do now means that RS isn't
really going to do much more than add some colour to cells and gridlines.
TIA,
Jarryd
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1183468763.165468.188890@.m36g2000hse.googlegroups.com...
> On Jul 3, 5:23 am, "Jarryd" <jar...@.community.nospam> wrote:
>> Hi,
>> I have a report that is basically a Matrix report that has totalling rows
>> throughout the vertical aspect of the report (not just at the end) and a
>> totalling columns at the end. Can Reporting Services deal with this
>> using
>> the toolbox and so on, or do I need to get coding? I am pretty sure the
>> wizard aint going to help. I have split my data into a number of
>> datasets
>> and hidden the column headings on all but the top Matrix, but then how do
>> I
>> add the totalling rows? Especially as they need to be the sum of a
>> number
>> of fields up the length of the column that aren't even contiguous. I
>> basically want to add totals at various places that I define and define
>> which values should be added. I know that there will always be the same
>> row
>> and column headings popping up each time, so from that side there is no
>> problem.
>> Please give me some direction on this as I am sort of looking at this a
>> bit
>> :-s at the moment.
>> TIA,
>> Jarryd
>
> The most common way I have dealt w/this same issue is to use either
> cursors or while loops to loop through the dataset in the stored
> procedure/query that is sourcing the report (usually one cursor/while
> loop per desired subtotal/etc). It can get a bit messy; however, it
> does get the job done. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||Doesn't help you now but RS 2008 will have a lot more functionality around
both tables and matrixes.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jarryd" <jarryd@.community.nospam> wrote in message
news:%23LyQVYYvHHA.4012@.TK2MSFTNGP03.phx.gbl...
> Hi,
> Yeah I suppose that is the way. Just kind of a bit long winded and I was
> just hoping I could get the basic Matrix and use tools to add things like
> totaling rows and columns in with a bit more control and flexability as
> what is available. Cos what I am going to have to do now means that RS
> isn't really going to do much more than add some colour to cells and
> gridlines.
> TIA,
> Jarryd
> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
> news:1183468763.165468.188890@.m36g2000hse.googlegroups.com...
>> On Jul 3, 5:23 am, "Jarryd" <jar...@.community.nospam> wrote:
>> Hi,
>> I have a report that is basically a Matrix report that has totalling
>> rows
>> throughout the vertical aspect of the report (not just at the end) and a
>> totalling columns at the end. Can Reporting Services deal with this
>> using
>> the toolbox and so on, or do I need to get coding? I am pretty sure the
>> wizard aint going to help. I have split my data into a number of
>> datasets
>> and hidden the column headings on all but the top Matrix, but then how
>> do I
>> add the totalling rows? Especially as they need to be the sum of a
>> number
>> of fields up the length of the column that aren't even contiguous. I
>> basically want to add totals at various places that I define and define
>> which values should be added. I know that there will always be the same
>> row
>> and column headings popping up each time, so from that side there is no
>> problem.
>> Please give me some direction on this as I am sort of looking at this a
>> bit
>> :-s at the moment.
>> TIA,
>> Jarryd
>>
>> The most common way I have dealt w/this same issue is to use either
>> cursors or while loops to loop through the dataset in the stored
>> procedure/query that is sourcing the report (usually one cursor/while
>> loop per desired subtotal/etc). It can get a bit messy; however, it
>> does get the job done. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>|||Fair enough, Rome wasn't built in a day.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:uNDwjfYvHHA.4552@.TK2MSFTNGP03.phx.gbl...
> Doesn't help you now but RS 2008 will have a lot more functionality around
> both tables and matrixes.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jarryd" <jarryd@.community.nospam> wrote in message
> news:%23LyQVYYvHHA.4012@.TK2MSFTNGP03.phx.gbl...
>> Hi,
>> Yeah I suppose that is the way. Just kind of a bit long winded and I was
>> just hoping I could get the basic Matrix and use tools to add things like
>> totaling rows and columns in with a bit more control and flexability as
>> what is available. Cos what I am going to have to do now means that RS
>> isn't really going to do much more than add some colour to cells and
>> gridlines.
>> TIA,
>> Jarryd
>> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
>> news:1183468763.165468.188890@.m36g2000hse.googlegroups.com...
>> On Jul 3, 5:23 am, "Jarryd" <jar...@.community.nospam> wrote:
>> Hi,
>> I have a report that is basically a Matrix report that has totalling
>> rows
>> throughout the vertical aspect of the report (not just at the end) and
>> a
>> totalling columns at the end. Can Reporting Services deal with this
>> using
>> the toolbox and so on, or do I need to get coding? I am pretty sure
>> the
>> wizard aint going to help. I have split my data into a number of
>> datasets
>> and hidden the column headings on all but the top Matrix, but then how
>> do I
>> add the totalling rows? Especially as they need to be the sum of a
>> number
>> of fields up the length of the column that aren't even contiguous. I
>> basically want to add totals at various places that I define and define
>> which values should be added. I know that there will always be the
>> same row
>> and column headings popping up each time, so from that side there is no
>> problem.
>> Please give me some direction on this as I am sort of looking at this a
>> bit
>> :-s at the moment.
>> TIA,
>> Jarryd
>>
>> The most common way I have dealt w/this same issue is to use either
>> cursors or while loops to loop through the dataset in the stored
>> procedure/query that is sourcing the report (usually one cursor/while
>> loop per desired subtotal/etc). It can get a bit messy; however, it
>> does get the job done. Hope this helps.
>> Regards,
>> Enrique Martinez
>> Sr. Software Consultant
>>
>

Compensating Changes - Deletes Good Rows From a Table?

We have some tables set up doing merge replication, and just came across
http://support.microsoft.com/default...&Product=sql2k
Is this gist of this, that when a compensating change condition exists, that
potentially good rows that cannot be replicated out, are instead DELETED
from the source table?
Is it logged somewhere when this has happened, or is there a way to monitor
it otherwise?
Thanks!
They are logged as conflicts but remain in the publisher/subscriber.
So if you insert a row with the same pk value in the publisher and
subscriber, when the sync happens the rows remain in the publisher and
subscriber.
If you update a row on the subscriber and delete it on the publisher, the
row remains updated on the subscriber and is not deleted there and remains
deleted on the publisher.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Art Vandelay" <artvandelay92k@.hotmail.com> wrote in message
news:OR2q7S6%23FHA.264@.tk2msftngp13.phx.gbl...
> We have some tables set up doing merge replication, and just came across
> http://support.microsoft.com/default...&Product=sql2k
> Is this gist of this, that when a compensating change condition exists,
> that potentially good rows that cannot be replicated out, are instead
> DELETED from the source table?
> Is it logged somewhere when this has happened, or is there a way to
> monitor it otherwise?
> Thanks!
>
>
>