Week Wise Distribution

Question

Post reply

Week Wise Distribution

ashoktheagarajan

SSC Enthusiast

Points: 124
More actions
December 9, 2014 at 4:06 am

#312964

My Table looks like this
NumberProductNameWeekTTR
XX1AW362.366666
XX2AW3627.616666
XX3BW3757.183333
XX4AW371.583333
XX5CW3872.566666
I am trying for the data to come up as following on a report
ProductName W36 W37 W38
A 2 1 0
B 0 1 0
C 0 0 1
Any suggestions are appreciated.......

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

First of all, welcome to the forums!

Let's start by creating some consumable DDL and data.

create table test (

Number char(3),

ProductName char(1),

Week char(3),

TTR Numeric(18, 6));

insert into test(Number, ProductName, Week, TTR)

values('XX1', 'A', 'W36', 2.366666),

('XX2', 'A', 'W36', 27.616666),

('XX3', 'B', 'W37', 57.183333),

('XX4', 'A', 'W37', 1.583333),

('XX5', 'C', 'W38', 72.566666);

There are several ways to get the results you're after, but here's a simple one:

select ProductName,

W36 = SUM(case when week = 'W36' then 1 else 0 end),

W37 = SUM(case when week = 'W37' then 1 else 0 end),

W38 = SUM(case when week = 'W38' then 1 else 0 end)

from test

group by ProductName

order by ProductName;

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

ashoktheagarajan SSC Enthusiast Points: 124 More actions · Answer 2

Hi Ed,

Thanks for the reply. I am sorry as my question was not completely clear.

The Week's mentioned needs to be dynamic and not a fixed input as the weeks may change based on the date range.

Is that possible ?

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 3

ashoktheagarajan (12/9/2014)
Hi Ed,
Thanks for the reply. I am sorry as my question was not completely clear.
The Week's mentioned needs to be dynamic and not a fixed input as the weeks may change based on the date range.
Is that possible ?

Yes it is... please see the following article.

http://www.sqlservercentral.com/articles/Crosstab/65048/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Wagner SSC Guru Points: 287026 More actions · Answer 4

Jeff Moden (12/9/2014)
Yes it is... please see the following article.
http://www.sqlservercentral.com/articles/Crosstab/65048/

That's the perfect article and is exactly where I'd point you.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions