Prev: Paging in sql 2000(Query Help)
Next: Header in Job Step Output file
From: Aalamo on 16 Jun 2010 12:31
Hi - I hope that you can help me.

I need to populate a column in my SQL table with information from
other columns, where there is a specific match, and don't know how to
do it. I'll keep the example small. If I had the following data:

CostCentre Account Owner CoOwner
1111 A Lee Lee;John
1111 B John Lee;John
2222 A Mark NULL
2222 B Mark NULL
3333 A Bill NULL
etc, etc

I basically need to add code in a stored procedure that will insert
the information in the CoOwner column, which is initially all NULL.
So, if a CostCentre has more than one owner in the various accounts,
then add the various Owners, separeted by ; in the CoOwner column. If
the Cost Centre has in total one Owner (because the owner is the same
in all accounts) then the CoOwner stays NULL.

Is this possible? How can I do it?

Thank you very much.

Leo
From: Erland Sommarskog on 16 Jun 2010 18:14
Aalamo (leoinlios(a)gmail.com) writes:
> I need to populate a column in my SQL table with information from
> other columns, where there is a specific match, and don't know how to
> do it. I'll keep the example small. If I had the following data:
>
> CostCentre Account Owner CoOwner
> 1111 A Lee Lee;John
> 1111 B John Lee;John
> 2222 A Mark NULL
> 2222 B Mark NULL
> 3333 A Bill NULL
> etc, etc
>
> I basically need to add code in a stored procedure that will insert
> the information in the CoOwner column, which is initially all NULL.
> So, if a CostCentre has more than one owner in the various accounts,
> then add the various Owners, separeted by ; in the CoOwner column. If
> the Cost Centre has in total one Owner (because the owner is the same
> in all accounts) then the CoOwner stays NULL.
>
> Is this possible? How can I do it?

I need to dash, so this is a quick answer. On
http://www.projectdmx.com/tsql/rowconcatenate.aspx you can see how
to do row concatenation. To filter out the rows in need of an update
you can do:

SELECT CostCentre, COUNT(DISTINCT Owner)
FROM tbl
GROUP BY CostCentre
HAVING COUNT(DISTINCT Owner) > 1


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Leo on 17 Jun 2010 11:13
Thank you Erland. I can see how your code shows me the affected rows, very good. I am still struggling to get the Update statement to work in conjunction with your code, but I need to keep trying by reading more examples in the link you provided. Thanks. Leo

---
frmsrcurl: http://msgroups.net/microsoft.public.sqlserver.programming/Populate-matching-Data-from-another-column-into-one
From: Erland Sommarskog on 17 Jun 2010 18:16
Leo (user(a)msgroups.net/) writes:
> Thank you Erland. I can see how your code shows me the affected rows,
> very good. I am still struggling to get the Update statement to work in
> conjunction with your code, but I need to keep trying by reading more
> examples in the link you provided. Thanks. Leo

OK, I had a little more time tonight, and I composed this solution:

CREATE TABLE Leo (costcentre char(4) NOT NULL,
account char(1) NOT NULL,
owner varchar(20) NOT NULL,
coowner varchar(100) NULL,
PRIMARY KEY (costcentre, account))
go
INSERT Leo (costcentre, account, owner)
SELECT '1111', 'A', 'Lee' union all
SELECT '1111', 'B', 'John' union all
SELECT '2222', 'A', 'Mark' union all
SELECT '2222', 'B', 'Mark' union all
SELECT '3333', 'A', 'Bill'
go
UPDATE Leo
SET coowner = substring(ca.list, 1, len(ca.list) - 1)
FROM Leo a
CROSS APPLY (SELECT DISTINCT owner + ';' AS [text()]
FROM Leo b
WHERE a.costcentre = b.costcentre
FOR XML PATH('')) AS ca(list)
WHERE len(ca.list) - len(replace(ca.list, ';', '')) > 1
go
SELECT * FROM Leo
go
DROP TABLE Leo


As you see I don't use the expression with HAVING that I posted last
night.

A quick explanation:

CROSS APPLY is kind of a correlated JOIN operator, and is run once
for each row in the outer table (Leo instance a).

The FOR XML PATH query produces a list of all owners seprated by semi-
colon, with each owner appearing exactly once.

In the WHERE clause I compare the length of the list with the length
of the list without the semicolumns. If the difference is > 1, there is
more than one semi-colon in the list, and thus more than one owner.

In the SET clause I strip the last semicolon.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Leo on 18 Jun 2010 06:22
Erland - you are amazing. I really appreciate your help, and extra effort in explaining the difficult parts. You clearly know your stuff well. Many Thanks.

---
frmsrcurl: http://msgroups.net/microsoft.public.sqlserver.programming/Populate-matching-Data-from-another-column-into-one
 | 
Pages: 1
Prev: Paging in sql 2000(Query Help)
Next: Header in Job Step Output file