Combining rows in a table(again)

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

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

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

Is there a better way?Your resultset doesn't make much sense. Can you explain it?|||Your resultset doesn't make much sense. Can you explain it?It's a "PeopleSoft" join.

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

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

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

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

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

-PatP

Combining Peer-to-Peer Transactional Replication with Standard Transactional Replication

Hello,

I'm interested in combining the Peer-to-Peer Transactional Replication and Standard Transactional Replication to provide a scale out solution of SQL Server 2005. The condition is as follows:

We may have 10 SQL Server 2005 (1 Publisher + 9 Subscriber) running transactional replication in the production environment and allow updates in subscribers. To offload the loading of the publisher, we plan to have 2 Publisher (PubNode1 and PubNode2) using Peer-to-Peer Transaction Replication and the rest 8 subscribers will be divided into 2 groups. The subscribers 1-4 (SubNode1, SubNode2, SubNode3, and SubNode4) will be set to be standard transactional replication subscribers of PubNode1, and the rest 4 subscribers (SubNode5, ..., SubNode8) will be set to be standard transactional replication subscribers of PubNode2.

Is it possible to setup above 2 Publisher + 8 Subscriber topology?
Also, could we set the 8 subscribers with updatable subscriptions to achieve each node is updatable?

We do not plan to set all the 10 nodes using Peer-to-Peer Transactional Replication as it is necessary to make sure n*(n-1)/2 (i.e. 45) peer-to-peer connections is reliable. It seems that the maintenance cost is high if the servers are not in a LAN and the topology is very high coupling. So we prefer to divide the 10 nodes into 2 groups and reduce the cost of each node to maintain the connections to all other sites.

That's the scenario.

Any feedback is welcome and appreciated.

Thanks,
Terence

Peer-to-peer would be ideal for your scenario if all your subscribers work primarily on partition data, meaning the chance of two subscribers access the same piece of data is usually pretty low and stable network connection.

If you foresee lots of coflicts situation where different subscribers will access the same data, then you may want to consider merge replication. Merge replication handles conflicts better and does not require dedicated live connection from node to node.

Gary

|||If our network is not stable, we would prefer to use Merge Replication.
However, if there are at least 10 sites for replication, then is each site need to have 9 connections to the other sites?
That's our main concern.
Thanks a lot.|||

I believe the changes are replicated to publisher first then to other subscribers.

http://msdn2.microsoft.com/en-us/library/ms152746.aspx

Therefore, each site only needs to maintain one connection (to the publisher).

Gary

Combining Peer-to-Peer Transactional Replication with Standard Transactional Replication

Hello,

I'm interested in combining the Peer-to-Peer Transactional Replication and Standard Transactional Replication to provide a scale out solution of SQL Server 2005. The condition is as follows:

We may have 10 SQL Server 2005 (1 Publisher + 9 Subscriber) running transactional replication in the production environment and allow updates in subscribers. To offload the loading of the publisher, we plan to have 2 Publisher (PubNode1 and PubNode2) using Peer-to-Peer Transaction Replication and the rest 8 subscribers will be divided into 2 groups. The subscribers 1-4 (SubNode1, SubNode2, SubNode3, and SubNode4) will be set to be standard transactional replication subscribers of PubNode1, and the rest 4 subscribers (SubNode5, ..., SubNode8) will be set to be standard transactional replication subscribers of PubNode2.

Is it possible to setup above 2 Publisher + 8 Subscriber topology?
Also, could we set the 8 subscribers with updatable subscriptions to achieve each node is updatable?

We do not plan to set all the 10 nodes using Peer-to-Peer Transactional Replication as it is necessary to make sure n*(n-1)/2 (i.e. 45) peer-to-peer connections is reliable. It seems that the maintenance cost is high if the servers are not in a LAN and the topology is very high coupling. So we prefer to divide the 10 nodes into 2 groups and reduce the cost of each node to maintain the connections to all other sites.

That's the scenario.

Any feedback is welcome and appreciated.

Thanks,
Terence

Peer-to-peer would be ideal for your scenario if all your subscribers work primarily on partition data, meaning the chance of two subscribers access the same piece of data is usually pretty low and stable network connection.

If you foresee lots of coflicts situation where different subscribers will access the same data, then you may want to consider merge replication. Merge replication handles conflicts better and does not require dedicated live connection from node to node.

Gary

|||If our network is not stable, we would prefer to use Merge Replication.
However, if there are at least 10 sites for replication, then is each site need to have 9 connections to the other sites?
That's our main concern.
Thanks a lot.|||

I believe the changes are replicated to publisher first then to other subscribers.

http://msdn2.microsoft.com/en-us/library/ms152746.aspx

Therefore, each site only needs to maintain one connection (to the publisher).

Gary

Combining information from multiple records into one

Hi,

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

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

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

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

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

Thanks.
RMG

P.S. ID is not my primary key and doesn't have to be uniqueYou can use a simple while loop to iterate over the record and insert the records into an output table. If the values for the combination of columns in your source set changes, then insert a new row into the table, and if the values are identical, then append the value from the other column to the corresponding column's value in the new table.

create a table to represent the output
select source data from the database

while (more records)
{

if the current column combination is different
to the previous one, insert a new row
into the output table.

if the current column combination is equal
to the previous one, append the value of
columnX to the corresponding value of the
current row in the output table.

move to next record;

}

display the output to the user, for to not do so,
would defeat the purpose of writing the function.

Send an email to your director of IT asking why
such a function was even requested ;)

Regards,|||This looks completely logically to me. But it would be very helpfull if you could translate some of this logic into SQL for me. Because that is where I have the problem, not so much the necessary steps I need to follow.

And I am afraid I have to ask the last question to myself. As I am the one who wants to concatenate the information and put it in another table. And the answer is quite simple: This way I don't have to copy and paste information from thousands of records by hand. ;)|||Three options.

Robert's way.
UDF to which you pass the data that uniquely identifies your output row and that returns string of delimited values.
If there are a finite (and manageable) number of values for Route then a CASE statement (note- this will give you n columns for Route where n is the number of unique values).|||I was going to suggest the third option and provide an example using the CASE statement, but I wasn't certain if the number of rows to be transformed, and thus the number of columns required to produce, would be of a small enough size to make this approach practical. I believe you would need to code one case statement per possible column. For this to work you will need to assign some kind of unique numbering to the rows, so that each case statement to represent a column, knows what row to extract. Also, you will need to collapse the rows after applying the concatenation of your new columns, populated by the CASE statement, in order to remove the gaps.

Taking these factors into consideration, I felt that the iterative approach I described earlier would provide you with the simplest and quickest implementation.

Though of course, they are only the considerations that I was able to ascertain from reading your post.|||Also, you will need to collapse the rows after applying the concatenation of your new columns, populated by the CASE statement, in order to remove the gaps.Nah - just use MAX() - I think the BoL 2000 CASE entry demonstrates exactly this. Thinking about it, I guess one might use PIVOT in 2005 in lieu of the CASE statements.|||Wouldn't you still need to collapse the rows after having projected the MAX() values of each column, for example by using the GROUP BY.|||Well yes.

SELECT col_1
, col_2
, something = MAX(CASE WHEN [route] = 'something' THEN [route] END)
, anotherThing = MAX(CASE WHEN [route] = 'anotherThing' THEN [route] END)
, andSoOn = MAX(CASE WHEN [route] = 'andSoOn' THEN [route] END)
FROM dbo.mytable
GROUP BY col_1
, col_2|||I didn't intend to be pedantic, I just thought the poster should be aware that the result set would need to be "flattened".|||Ah beg your pardon - I wasn't sure whether or not you were asking questions on your behalf or the OPs. I thought your range extended well beyond this :).|||Select Col1+col2 As Merged_col
From ...

?|||Select Col1+col2 As Merged_col
From ...

?That handles Column 1 in the OPs desired results but look more carefully at column 2 - that's the tricky bit :)|||I think you will be able to get the result set you want doing something like this.

CREATE PROC sp_TrickySelect
AS
DECLARE @.str varchar(8000), @.tot int, @.l int, @.search varchar(1000), @.select varchar(8000), @.count int, @.l2 int, @.str_part varchar(1000)

SELECT ([ID]+'/'[DayNr]+'/'+[Transportation]) AS [Column1], [TransOrder], [Route]
INTO ##tmpTbl1
FROM ....

SELECT DISTINCT([Column1])
INTO ##tmpTbl2
FROM ##tmpTbl1

SET @.tot = SELECT COUNT(*) FROM ##tmpTbl2
SET @.l = 1

WHILE (@.l<=@.tot)
BEGIN
SET @.search = SELECT TOP 1 [Column1] FROM ##tmpTbl2
SET @.count = SELECT COUNT([Route]) FROM ##tmpTbl1 WHERE [Column1]=@.search
SET @.l2 = 1
WHILE (@.l2<=@.count)
BEGIN
SELECT @.str_part = SELECT TOP 1 [Route] FROM ##tmpTbl1 WHERE [Column1]=@.search ORDER BY [TransOrder] ASC
SELECT @.str = @.str+' '+@.str_part
DELETE ##tmpTbl1 WHERE [Column1]=@.search AND [TransOrder]=(SELECT MIN [TransOrder] FROM ##tmpTbl1 WHERE [Column1]=@.search)
SET @.l2 = @.l2 +1
END
INSERT INTO tmpTbl ([Column1], [Column2]) VALUES (@.search,@.str)
DELETE FROM ##tmpTbl2 WHERE [Column1]=@.search
SET @.l = @.l+1
END

SELECT [Column1], [Column2] FROM tmpTbl

NB: I wrote this directly on this website and I didn't test it.

Hope it can helps you out.

Regards

Or Tho|||If you use the Code from my previous post you gonna have to create a table tmpTable at the begining of the proc and drop it at the end OR create it and TRUNCATE it at the begining of the proc...|||I am compelled to vehemently advise against using a solution similar to the one described above. Of course, these views are intended to discuss the merits of the solution, and in no way are to be interpreted as being a reference to the author.

Ortho,

While your approach may indeed work, in comparing it with other solutions available, namely those developed in SQL, it is unnecessarily complex and bloated.

Not only is the length of your solution a reason to outright dismiss it, but it also consumes a large number of resources and the use of programming constructs that are disproportionate to the complexity of the problem. These include temp tables, while loops, conditional constructs, string manipulation functions, and finally, individual SQL statements.

Your approach is almost exclusively procedural in nature and as such, should be considered only, and only when ,a more elegant and often performance friendly set based solution is not available. Often an experienced SQL Developer can develop a solution using many times fewer resources and lines of code than what would otherwise b developed by a procedural coder. This is not to say that one skill set is more valuable than the other, but instead it serves to highlight the difference present in the mindsets of these two developers, and how their differing perspectives are suited for specific kinds of problems.

Generally procedural coders find it immensely difficult to develop efficient code for the manipulation and retrieval of data, in other words, developing code that works with data. I know that myself, I find it often difficult to see the benefits in OO programming for anything related to data. It's just a different way of viewing a problem.

As you can see by comparing your solution to that posted earlier in this thread, the number of explicitly programmed steps is much greater in your solution. This can increase the risk of errors being introduced during development and maintenance of the code, which is obviously, is a risk that developers and managers should strive to minimize.

Finally, your solution ignores the intent of the family of languages (unfortunately I cannot remember the exact term at the time of writing), of which SQL is indeed widely known, in developing a level of abstraction between the intent of a function and the internal representation of how it will be performed. In other words, the goal of SQL and other similar languages, is to focus on expressing the problem in terms of what needs to be done and not so much on how to do it.

To apply these principles to the problem posted by the original poster, we can see that an SQL solution can be developed in only a fraction of the lines of code and with no explicit declarations of variables or inclusion of procedural programming constructs.

Regards,|||r123456, I know my solution isn't the best but it's the only way I found to get the result set he wants...

Anyways thanks for the advise!

Peace

Or|||As I said, the intent of the post was to make the original poster aware that in that instance, a procedural approach was not the optimal solution, and instead a set based solution would be more appropriate.

column update function

This is probably a common problem with a standard design pattern, but
I'm having trouble finding the solution.

I have a table with a lot of columns, for this example I'll just use
three but in reality its more like 20.

Create Table myTable (int col_one primary key, int col_two,
varchar(20) col_three) etc...

I want to write a sproc that allows updating of this column. Say I
have a sproc

create sproc myUpdate int @.col_one, int @.col_two, varchar(20)
col_three

as

update myTable col_two = @.col_two, col_three = @.col_three
where col_one = @.col_one

then if I only want to update col_two I have to pass in the current
value of col_three so that it remains the same, which seems pretty
inefficient. so I could change it to:

as

update myTable col_two = coalasce(@.col_two, col_two)
, col_three = coalasce(@.col_three, col_three)
where col_one = @.col_one

and then if I wanted to leave col_three the way it is then I could
just do

exec myUpdate 1, 2, NULL

the only problem here is that what if the value of col_three is
currently 3, and I want to set it to NULL? Under the current method,
setting someting to NULL is impossible

finally, I'd like to use parameter naming in my exec calls. that way
I can just say someting like

exec myUpdate 1, col_three=3

this would update col_three to 3 and leave the rest of the fields
untouched. you can see how handy this would be if you just want to
change a few of the fields in a table with a large number of columns.

I'm sure this has been done before, can somebody point me in the right
direction?

Thanks,

Benben (santoshamb@.yahoo.com) writes:
> update myTable col_two = coalasce(@.col_two, col_two)
> , col_three = coalasce(@.col_three, col_three)
> where col_one = @.col_one
> and then if I wanted to leave col_three the way it is then I could
> just do
> exec myUpdate 1, 2, NULL
> the only problem here is that what if the value of col_three is
> currently 3, and I want to set it to NULL? Under the current method,
> setting someting to NULL is impossible
> finally, I'd like to use parameter naming in my exec calls. that way
> I can just say someting like
> exec myUpdate 1, col_three=3
> this would update col_three to 3 and leave the rest of the fields
> untouched. you can see how handy this would be if you just want to
> change a few of the fields in a table with a large number of columns.

T-SQL is not a language that lends itself to this sort of thing. There
is no way to tell whether a parameter was passed explicitly or not. You
can of course test for NULL, but it may have been an explicit NULL.

One alternative would be to have extra marker variables to tell whether
a parameter applies or not. It quickly gets bulky. It can be reduced to
a single parameter which is a bitmask, but that is cryptic and error-prone.

What we do in our update procedures is to pass all column values. But
then we typically have read all to the GUI and now we are sending them
back. If some operation updates only affects a few columns, that is
typically an individual UPDATE statement in a different procedure.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Column Splitting

Hi Everyone,

I've been given the painstaking project of splitting a single column into multiple columns and rows. I have a solution set up in which I will be posting further down the post but I want to see if there is a much more efficient solution to this.

sample data:
create table tbl_list
(pk_int_itmid int(5) Primary Key,
vchar_desk vchar(300));

create table tbl_test1
(fk_int_itmid int(5) references tbl_list(pk_int_itmid),
vchar_itm varchar(60));

insert into tbl_list values
(1, 'this item');

insert into tbl_list values
(2, 'that item');

insert into tbl_list values
(3, 'those items');

insert into tbl_test1 values
(1, 'A, B - C, D, E - F, G, H - I');

insert into tbl_test1 values
(2, 'J, K - L, M, N - O');

insert into tbl_test1 values
(3, 'P, Q - R');

into this table:
create table tbl_output
(fk_int_itmid int(5) references tbl_list(pk_int_itmid),
vchar_itmA varchar(60),
vchar_itmB varchar(60),
vchar_itmC varchar(60));

Output in comma delimited form:
'1', 'A', 'B', 'C'
'1', 'D', 'E', 'F'
'1', 'G', 'H', 'I'
'2', 'J', 'K', 'L'
'2', 'M', 'N', 'O'
'3', 'P', 'Q', 'R'

my current solution:
create view vw_itm_a as
select fk_int_itmid,
substring(vchar_itm, 0, charindex('-',vchar_itm)) as vchar_itmA,
substring(vchar_itm, charindex('-',vchar_itm)+1 , charindex(',',vchar_itm)-charindex('-',vchar_itm)) as vchar_itmB,
substring(vchar_itm, charindex(',',vchar_itm)+1) as vchar_itmC
from tbl_test1
where charindex(',',vchar_itm) >1
Go

create view vw_itm_b as
select fk_int_itmid,
substring(vchar_itm, 0, charindex('-',vchar_itm)) as vchar_itmA,
substring(vchar_itm, charindex('-',vchar_itm)+1 , charindex(',',vchar_itm)-charindex('-',vchar_itm)) as vchar_itmB,
substring(vchar_itm, charindex(',',vchar_itm)+1) as vchar_itmC
from vw_itm_a
where charindex(',',vchar_itmC) >1;
Go

create view vw_itm_c as
select fk_int_itmid,
substring(vchar_itmC, 0, charindex('-',vchar_itmC)) as vchar_itmA,
substring(vchar_itmC, charindex('-',vchar_itmC)+1 , charindex(',',vchar_itmC)-charindex('-',vchar_itmC)) as vchar_itmB,
substring(vchar_itmC, charindex(',',vchar_itmC)+1) as vchar_itmC
from vw_itm_b
where charindex(',',vchar_itmC) >1;
Go;

create view vw_itm_d as
select fk_int_itmid, vchar_itmA, vchar_itmB,
substring(substring(vchar_itm, charindex(',',vchar_itm)+1), 0, charindex(',',vchar_itm)) as vchar_itmC
from vw_itm_a ia union vw_itm_b ib on ia.fk_int_itmid = ib.fk_int_itmid
Go;

create view vw_itm_e as
select fk_int_itmid, vchar_itmA, vchar_itmB,
substring(substring(vchar_itm, charindex(',',vchar_itm)+1), 0, charindex(',',vchar_itm)) as vchar_itmC
from vw_itm_c ia union vw_itm_b ib on ia.fk_int_itmid = ib.fk_int_itmid
Go;

create view vw_itm as
select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC
from vw_itm_a
where fk_int_itmid not in (select fk_int_itmid from vw_itm_b)
union
select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC
from vw_itm_d
union
select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC
from vw_itm_b
where fk_int_itmid not in (select fk_int_itmid from vw_itm_c)
union
select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC
from vw_itm_e
union
select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC
from vw_itm_c
Go;

select fk_int_itmid, vchar_itmA, vchar_itmC, vchar_itmC
into tbl_output
from vw_itm

Is there a much more efficient manner of handling this column splitting?

Thanks
DCyou have my sincere condolences

i would do this with application programming, not sql