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 help
http://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

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

manipulate matrix/group column heading for subtotal column

Hello all!

I'm using a matrix with a subtotal. The subtotal shows prior to the detail (first column). For the heading I have

= Fields!category.Value + " HeadCounts"

(where category is the grouping for the matrix)

Which is fine for the detail columns. But the subtotal column repeats the value for the first detail column heading and it is inappropriate. How do I identify this column and replace the heading when it is the subtotal column?

I hope I stated that clearly.

You can use the InScope() function to find out if you are in a subtotal and display different content. http://msdn2.microsoft.com/en-us/library/ms156490.aspx|||

I had two fields under the group/category. Each calculating an aggregate on different fields. I had placed the field/column headings at that level which included the category 'marker'. I found that if I put the category ('marker') at the group level on the heading and removed it at this point that it filled it appropriately. (beginner error... my apologies)

I tried using 'InScope' as you suggested and found that at that level it reported all heading at the same 'level'... I had to ask level as I couldn't manage to ask the appropriate InScope question.

I do appreciate your assitance. Thank you.

b