January 30, 2012 at 4:59 pm
hi
i have a table Product like this CustomerId,Product,quantity
1 red 10
2 blue 11
3 pink 13
1 red 1
2 blue 4
3 pink 5
Write a select statement that will aggregate the red, blue, and pink for
each customer. The output should look like the following:
customerid red blue pink
1 11 10 3
2 20 23 25
3 32 21 20
How to do it?
January 30, 2012 at 5:05 pm
You might want to to try the CrossTab approach as described in the related link in my signature.
PS: Since it sounds like homework I think it's more helpful to provide some reading material instead of just a coded solution.... 😉
January 31, 2012 at 4:00 am
shah.simmy (1/30/2012)
hii have a table Product like this CustomerId,Product,quantity
1 red 10
2 blue 11
3 pink 13
1 red 1
2 blue 4
3 pink 5
Write a select statement that will aggregate the red, blue, and pink for
each customer. The output should look like the following:
customerid red blue pink
1 11 10 3
2 20 23 25
3 32 21 20
How to do it?
Your input and output data do not match. Is there some aspect of the question you haven't explained, or was it just laziness? Don't feel you have to obfuscate the sample data for my sake - I'm happy to help with this sort of question, it's up to you whether you learn anything from it or just copy-n-paste.
DECLARE @product AS TABLE
(
ProductId integer NOT NULL,
Colour varchar(5) NOT NULL,
Quantity integer NOT NULL
)
INSERT @product
(ProductId, Colour, Quantity)
VALUES
(1, 'red', 10),
(2, 'blue', 11),
(3, 'pink', 13),
(1, 'red', 1),
(2, 'blue', 4),
(3, 'pink', 5);
SELECT
*
FROM @product AS p
PIVOT (SUM(p.Quantity) FOR Colour IN (red,blue,pink)) AS pvt
ORDER BY
pvt.ProductId;
January 31, 2012 at 8:17 am
Thanks everyone,i got solution by cross tab.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply