March 27, 2012 at 1:56 pm
Hello -
I have a table with a bunch of sales orders from different channels (Example: Retail stores, online orders, other). I want to the output of my query to look like this:
Product Name | Retail Order Count | Online Order Count | Other Order Count
I know that I could do this:
SELECT Product_Name, Channel, count(*) FROM dbo.Table_Name GROUP BY Product_Name, Channel
But then I get a different row for each channel type. Instead, I want the channel types to be the column headings. What is the best way to do something like this?
Thanks!
March 27, 2012 at 2:04 pm
Can you provide some dummy data and how you would like the results to look 🙂 thanks, from what i read of your post to me sounds like you would want use a pivot table.
***The first step is always the hardest *******
March 27, 2012 at 2:08 pm
Triality (3/27/2012)
Hello -I have a table with a bunch of sales orders from different channels (Example: Retail stores, online orders, other). I want to the output of my query to look like this:
Product Name | Retail Order Count | Online Order Count | Other Order Count
I know that I could do this:
SELECT Product_Name, Channel, count(*) FROM dbo.Table_Name GROUP BY Product_Name, Channel
But then I get a different row for each channel type. Instead, I want the channel types to be the column headings. What is the best way to do something like this?
Thanks!
With the ddl and sample data we could work up tested sql. right now from the vague question i can give the vague answer of using cross tabs or pivot (your choice and with out sample data im not sure which will be best). Please see the link in my signature for the proper forum etiquette to post a question
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
March 27, 2012 at 2:09 pm
Hey,
There is a couple of ways you can do this that I can immediately think of.
> With the current output that you have, you can Transpose the row data to columns using either:
> CROSS TAB query
> PIVOT query
> You can use a case statement for each column (essentially the same as CROSS TAB but you dont have your intermediate table :)), such as:
SUM(CASE WHEN Channel = 'Retail' THEN 1 ELSE 0 END) as Retail Order Count
and so on for the rest of the columns
Hope it helps!
/>L
-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].
I believe in Codd
... and Thinknook is my Chamber of Understanding
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply