| Author |
 Topic  |
|
|
yoyosh
Starting Member
27 Posts |
 Posted - 02/27/2013 : 07:19:05
|
I would like to convert 1:N relation into comma-separated value in one column. Suppose we have the following tables:
A:
pk|col2
_______
1 | (empty string)
2 | (empty string)
B:
pk|fk|col3
__________
1 |1 | a
2 |1 | b
3 |2 | c
I want to update col2 in table A in the following way:
A:
pk|col2
_______
1 | a,b
2 | c
I tried something like that:
UPDATE A SET col2 += col3
from A
INNER JOIN B on A.pk = B.fk
Thank you for help in advance |
|
|
James K
Flowing Fount of Yak Knowledge
1530 Posts |
Posted - 02/27/2013 : 07:51:36
|
UPDATE A SET
col2 = STUFF(b.col3concat,1,1,'')
FROM
A
CROSS APPLY
(
SELECT ',' + b.col3
FROM B
WHERE b.fk = a.pk
FOR XML PATH('')
) c(col3concat); |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 02/27/2013 : 22:59:43
|
quote:
Originally posted by James K
UPDATE A SET
col2 = STUFF(bc.col3concat,1,1,'')
FROM
A
CROSS APPLY
(
SELECT ',' + b.col3
FROM B
WHERE b.fk = a.pk
FOR XML PATH('')
) c(col3concat);
Fixed typo
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
yoyosh
Starting Member
27 Posts |
Posted - 02/28/2013 : 03:19:16
|
quote:
Originally posted by James K
UPDATE A SET
col2 = STUFF(b.col3concat,1,1,'')
FROM
A
CROSS APPLY
(
SELECT ',' + b.col3
FROM B
WHERE b.fk = a.pk
FOR XML PATH('')
) c(col3concat);
Thank you for response.
Could you please explain:
c(col3concat) ?
What is 'c' here?
Also second question: is XML PATH necessary in this query? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 02/28/2013 : 04:01:41
|
quote:
Originally posted by yoyosh
quote:
Originally posted by James K
UPDATE A SET
col2 = STUFF(b.col3concat,1,1,'')
FROM
A
CROSS APPLY
(
SELECT ',' + b.col3
FROM B
WHERE b.fk = a.pk
FOR XML PATH('')
) c(col3concat);
Thank you for response.
Could you please explain:
c(col3concat) ?
What is 'c' here?
Also second question: is XML PATH necessary in this query?
c is short name for the table called table alias
FOR XML PATH is that which causes the concatenation of the string
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
yoyosh
Starting Member
27 Posts |
Posted - 02/28/2013 : 05:30:53
|
Why is alias followed by (...)?
c(col3concat) |
|
|
|
James K
Flowing Fount of Yak Knowledge
1530 Posts |
Posted - 02/28/2013 : 05:40:14
|
It is telling SQL Server that what comes out from the subquery is a virtual table, and that I want to name that virtual table as "c", and that there will be one column in that table, and that I want to name that column as col3concat. I could name it anything I want - for example:UPDATE A SET
col2 = STUFF(foo.bar,1,1,'')
FROM
A
CROSS APPLY
(
SELECT ',' + b.col3
FROM B
WHERE b.fk = a.pk
FOR XML PATH('')
) AS foo(bar);Why foo and bar? I have no idea! People seem to like foo and bar for things for which they cannot come up with reasonable names. I am sure there is a very good reason for it, but I don't know what that is. |
|
|
|
Lamprey
Flowing Fount of Yak Knowledge
3833 Posts |
|
|
James K
Flowing Fount of Yak Knowledge
1530 Posts |
|
|
yoyosh
Starting Member
27 Posts |
Posted - 03/04/2013 : 03:50:37
|
| Thanks |
|
|
| |
Topic |
|
Update with inner join problem
