Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
y2n
Starting Member
5 Posts |
Posted - 2011-04-07 : 18:13:20
|
I am trying to update a table (T1) that has an ID column and then several columns that flatten out the data from a related table (T2) by putting the data into a different column based on a value in one of the fields in T2. Each column in T1 (A, B and C) can be overloaded by multiple values from t2 (one from each record that matches the code).I can do this in MS Access using the SQL Server tables, but the stored proc in SQL is giving me strange results. Only one column ends up being updated and all the others are remaining, or being reset to, NULL.I am truly boggled by this right now. I have tried several permutations of this, including using IF ... THEN syntax, and get the same type of behaviour.I am inlculding all code necessary to create and test, so please help.CREATE TABLE Table_1(ID int, A varchar(5),B varchar(5), C varchar(5))INSERT INTO Table_1 VALUES (1, NULL, NULL, NULL)INSERT INTO Table_1 VALUES (2, NULL, NULL, NULL)INSERT INTO Table_1 VALUES (3, NULL, NULL, NULL)CREATE TABLE Table_2(ID int, CODE varchar(5), EMAIL varchar(5))INSERT INTO Table_2 VALUES (1, 'A','1A')INSERT INTO Table_2 VALUES (1, 'B','1B')INSERT INTO Table_2 VALUES (1, 'C','1C')INSERT INTO Table_2 VALUES (2, 'A','2A')INSERT INTO Table_2 VALUES (2, 'B','2B')INSERT INTO Table_2 VALUES (2, 'B','2B2')INSERT INTO Table_2 VALUES (3, 'A','3A')INSERT INTO Table_2 VALUES (3, 'C','3C')INSERT INTO Table_2 VALUES (3, 'C','3C2')-- The Update that I am trying to runUPDATE t1SET A = CASE WHEN (t2.code LIKE 'A' AND t1.a IS NULL) THEN t2.email WHEN (t2.code LIKE 'A' AND NOT t1.a IS NULL) THEN t1.a + ',' + t2.email ELSE t1.a END ,B = CASE WHEN (t2.code LIKE 'B' AND t1.b IS NULL) THEN t2.email WHEN (t2.code LIKE 'B' AND NOT t1.b IS NULL) THEN t1.b + ',' + t2.email ELSE t1.b END ,C = CASE WHEN (t2.code LIKE 'C' AND t1.c IS NULL) THEN t2.email WHEN (t2.code LIKE 'C' AND NOT t1.c IS NULL) THEN t1.c + ',' + t2.email ELSE t1.c END--SELECT *FROM Table_1 t1 INNER JOIN Table_2 t2 ON t2.ID = t1.id/*Expected Results in Table 11 1A 1B 1C2 2A 2B,2B2 NULL3 3A NULL 3C,3C2Actual Results1 1A NULL NUll2 2A NULL NULL3 3A NULL NULL*//* -- Code that works in MS Access using the same SQL Server tables.UPDATE Table_1 AS t1 INNER JOIN Table_2 AS t2 ON t1.ID = t2.ID SET t1.A = IIf([t2].[code] Like 'A',IIf(IsNull([t1].[a]),[t2].[email],[t1].[a] & ',' & [t2].[email]),[t1].[a]), t1.B = IIf([t2].[code] Like 'B',IIf(IsNull([t1].[b]),[t2].[email],[t1].[b] & ',' & [t2].[email]),[t1].[b]), t1.C = IIf([t2].[code] Like 'C',IIf(IsNull([t1].[c]),[t2].[email],[t1].[c] & ',' & [t2].[email]),[t1].[c]);*/ |
|
y2n
Starting Member
5 Posts |
Posted - 2011-04-08 : 13:01:22
|
No responses yet? I see that a few people have viewed this post, but nobody has replied. Do you need more info? I know there is a simple solution, as I am sure I have done this before, but I just can't see the forest for the trees right now. |
|
|
slimt_slimt
Aged Yak Warrior
746 Posts |
Posted - 2011-04-09 : 12:13:59
|
Hi,if i understand correctly you need something like this:select * from table_2pivot( max(email) for code in ([A],[B],[C])) as p |
|
|
y2n
Starting Member
5 Posts |
Posted - 2011-04-11 : 12:02:26
|
That is close, but it only returns one email value (the max one). What I need to do is concatenate all the values from that field for each code. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-11 : 14:54:08
|
Here is my adapation of slimt_slimt's code.SELECT * FROM ( SELECT distinct a.Id, a.Code, b.EmailList FROM Table_2 a CROSS APPLY ( SELECT STUFF(( SELECT ',' + EMAIL AS [text()] FROM Table_2 b WHERE a.Id = b.Id AND a.Code = b.CODE FOR XML PATH(''), TYPE ).value('.','varchar(max)'),1,1,'') AS EmailList ) b)cPIVOT(MAX(EmailList) FOR Code IN ([A],[B],[C])) P |
|
|
y2n
Starting Member
5 Posts |
Posted - 2011-04-11 : 20:05:14
|
@sunitabeck and @slimt, thanks for for the help. I tweaked the code to actually update Table_1 with those values. Here is the final result:UPDATE Table_1SET a = p.a, b = p.b, c = p.c--SELECT * FROM( SELECT distinct a.Id, a.Code, b.EmailList FROM Table_2 a CROSS APPLY (SELECT STUFF ( ( SELECT ',' + EMAIL AS [text()] FROM Table_2 b WHERE a.Id = b.Id AND a.Code = b.CODE FOR XML PATH(''), TYPE ).value('.','varchar(max)'),1,1,'' ) AS EmailList ) b) cPIVOT(MAX(EmailList) FOR Code IN ([A],[B],[C])) PJOIN Table_1 ON p.id = Table_1.id |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-04-11 : 20:20:53
|
I had forgotten that you were trying to update the table rather than just select the results. But with your changes it looks perfect!! |
|
|
|
|
|
|
|