Hi.
I want to update table1 from table2.
table1
ID  D1  D2  D3
1   
2
3

table2
ID  D
1    X
1    Y
2    Z

After the update I want table1 to look like this:
table1
ID  D1  D2  D3
1    X    Y
2    Z
3

Is't possible to do?
Thanks for any help.
Carl

It's not nice but it achevies what you asked...

UPDATE a SET
a.D1 = Pivot.D1,
a.D2 = Pivot.D2
FROM Table1 a
INNER JOIN
(SELECT ID, CASE  WHEN  d in ('X','Z') THEN D ELSE null END AS D1,
  CASE D WHEN 'Y' THEN D ELSE null END AS D2
  FROM table2
  GROUP BY ID,d) as Pivot
ON
a.ID = Pivot.ID

Show quoteHide quote

Thanks for your help.
Unfortunately I dont know what coulmn D contains.
In another word I can not compare with X, Y, Z...

Show quoteHide quote

On Fri, 20 Jan 2006 05:01:02 -0800, Carl wrote:

Show quoteHide quoteHi Carl,

Possible? Yes. Adviseable? No. Table2 seems to be normalized; table1
seems to hold a repeating group.

If this is for reporting, try to do it on the client side. If that's not
possible, then do it in a reporting procedure. Google for "crosstab
query" to find some common solutions for this.

And if you really have to do this in an update, then try something like
the below:

UPDATE table1
SET    D1 = (SELECT D
             FROM   table2 AS t2a
             WHERE  t2a.ID = table1.ID
             AND   (SELECT COUNT(*)
                    FROM   table2 AS t2b
                    WHERE  t2b.ID = t2a.ID
                    AND    t2b.D <= t2a.D) = 1)
     , D2 = (SELECT D
             FROM   table2 AS t2a
             WHERE  t2a.ID = table1.ID
             AND   (SELECT COUNT(*)
                    FROM   table2 AS t2b
                    WHERE  t2b.ID = t2a.ID
                    AND    t2b.D <= t2a.D) = 2)
     , D3 = (SELECT D
             FROM   table2 AS t2a
             WHERE  t2a.ID = table1.ID
             AND   (SELECT COUNT(*)
                    FROM   table2 AS t2b
                    WHERE  t2b.ID = t2a.ID
                    AND    t2b.D <= t2a.D) = 3)

On Mon, 23 Jan 2006 23:43:00 +0100, Hugo Kornelis wrote:

(snip)
Show quoteHide quoteForgot to add the usual disclaimer:

Untested - see www.aspfaq.com/5006 if you prefer a tested reply.

Hi Hugo.
Thanks for your help, I appreciate it.
Your solution solved my problem.
Carl

reverse like?
Can't we use variables in OPENQUERY, FREETEXT("@searchstring")?
inserting single precision data into sql server float column
SQL dilema
CTE vs. Table Variable (Paging)