Many Detail Record to One

I have records in a table that have a format like the following
Id / Code / Amount
ex.
1 / PDM / 50.00
1 / BIN / 75.00
1 / REN / 30.00
The records have the same id but different codes - the codes will never be
anything different than what is listed - so I could add a where clause for
each code.
I would like to select the three records and combine them into one. So that
it looks like the following:
ID / PDMAMT / BINAMT / RENAMT
1 / 50.00 / 75.00 / 30.00
I have looked at subqueries and the exists operator but I can't seem to find
what I am looking for.
Thanks for the helphttp://www.aspfaq.com/2462
http://www.aspfaq.com/
(Reverse address to reply.)
"Heather" <Heather@.discussions.microsoft.com> wrote in message
news:EA6225CF-BF9C-4DB5-AB3C-1D31104E12E7@.microsoft.com...
> I have records in a table that have a format like the following
> Id / Code / Amount
> ex.
> 1 / PDM / 50.00
> 1 / BIN / 75.00
> 1 / REN / 30.00
> The records have the same id but different codes - the codes will never be
> anything different than what is listed - so I could add a where clause for
> each code.
> I would like to select the three records and combine them into one. So
that
> it looks like the following:
> ID / PDMAMT / BINAMT / RENAMT
> 1 / 50.00 / 75.00 / 30.00
> I have looked at subqueries and the exists operator but I can't seem to
find
> what I am looking for.
> Thanks for the help|||Heather,
This is called a cross-tab (a.k.a. pivot), and in your case you could do it
this way:
SELECT id,
SUM(CASE WHEN Code = 'PDM' THEN Amount ELSE 0 END) AS PDMAMT,
SUM(CASE WHEN Code = 'BIN' THEN Amount ELSE 0 END) AS BINAMT,
SUM(CASE WHEN Code = 'REN' THEN Amount ELSE 0 END) AS RENAMT
FROM YourTable
GROUP BY id
"Heather" <Heather@.discussions.microsoft.com> wrote in message
news:EA6225CF-BF9C-4DB5-AB3C-1D31104E12E7@.microsoft.com...
> I have records in a table that have a format like the following
> Id / Code / Amount
> ex.
> 1 / PDM / 50.00
> 1 / BIN / 75.00
> 1 / REN / 30.00
> The records have the same id but different codes - the codes will never be
> anything different than what is listed - so I could add a where clause for
> each code.
> I would like to select the three records and combine them into one. So
that
> it looks like the following:
> ID / PDMAMT / BINAMT / RENAMT
> 1 / 50.00 / 75.00 / 30.00
> I have looked at subqueries and the exists operator but I can't seem to
find
> what I am looking for.
> Thanks for the help