Using Data shaping in ADO or Cn it be done using T-SQL?

I have a view that has 4 fields (customer, product, product type, date - all the fields are not unique and can be both parent or child) For example:

customer   product   product type   date
ABC          BEEF       FOOD             1/1/03
ABC          SHIRT     CLOTHES        2/6/03
BGH          APPLE     FOOD             7/7/03
BGH          BEEF       FOOD             7/7/03
CCC          SHIRT     CLOTHES        5/5/03

Can you show me how to use data shaping to select product type, SUM(CASE WHEN InsertDate BETWEEN DATEPART(year, GETDATE()) AND GETDATE()  THEN 1 ELSE 0 END) AS YearCount as parent and select  product,  SUM(CASE WHEN InsertDate BETWEEN DATEPART(year, GETDATE()) AND GETDATE()  THEN 1 ELSE 0 END) AS YearCount as child.

The result should looks something like this:

FOOD       3                 --------------- Parent
BEEF        2                
APPLE      1

CLOTHES                    ----------------Parent
SHIRT      2
musclejackAsked:
Who is Participating?
 
HilaireCommented:
To display childs that have no records, we need to know the
possible childs for a parent

is this information stored somewhere else ?

if the information is not available in the view (I mean, if parent-child couples are not in the view, where can I get it from ?),
I guess there's a table that stores this information somewhere.

Can you give me more details ?

Thks

Hilaire
0
 
HilaireCommented:
Hi, you could try

select coalesce(child, parent), YearCount from
(
select  product_type as parent, null as child ,SUM(CASE WHEN InsertDate BETWEEN DATEPART(year, GETDATE()) AND GETDATE()  THEN 1 ELSE 0 END) AS YearCount
group by product_type
union
select  product_type as parent, product as child ,SUM(CASE WHEN InsertDate BETWEEN DATEPART(year, GETDATE()) AND GETDATE()  THEN 1 ELSE 0 END) AS YearCount
group by product_type, product
)  a
order by parent

Hilaire
0
 
HilaireCommented:
or this one

select  coalesce(product,product_type) ,SUM(CASE WHEN InsertDate BETWEEN DATEPART(year, GETDATE()) AND GETDATE()  THEN 1 ELSE 0 END) AS YearCount
group by product_type, product
with rollup

Hilaire
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
musclejackAuthor Commented:
Hi Hilaire,

The first one works, but I also want to show the child that has no record -

for example -

customer   product   product type   date
ABC          BEEF       FOOD             1/1/03
ABC          SHIRT     CLOTHES        2/6/03
BGH          APPLE     FOOD             7/7/03
BGH          BEEF       FOOD             7/7/03
CCC          SHIRT     CLOTHES        5/5/03
CDC          BEEF       CLOTHES        8/8/03

BEEF           3                 --------------- Parent
FOOD          2                
CLOTHES     1

APPLE         1                 --------------- Parent
FOOD          1                
CLOTHES     0

THX

0
 
musclejackAuthor Commented:
Hi Hilaire,

Also, how to add a additional column to display which are parent, which are children?

For example:

Parent   BEEF           3                
Child     FOOD          2                
Child     CLOTHES     1

Parent   APPLE         1                
Child     FOOD          1                
Child     CLOTHES     0
0
 
HilaireCommented:
Your last exemple is a little confusing,
to be honest I don't understand it at all

BEEF           3                 --------------- Parent
FOOD          2                
CLOTHES     1

APPLE         1                 --------------- Parent
FOOD          1                
CLOTHES     0

??

Could you be more precise ?

Thks

Hilaire


0
 
HilaireCommented:
No problem for the additional field

Back in a few secs

Does the second query work ?

Hilaire
0
 
HilaireCommented:
The first column would be

select type, coalesce(child, parent), YearCount from
(
select  'Parent' as type, product_type as parent, null as child ,SUM(CASE WHEN InsertDate BETWEEN DATEPART(year, GETDATE()) AND GETDATE()  THEN 1 ELSE 0 END) AS YearCount
from <YourView>
group by product_type
union
select  'Child' as type, product_type as parent, product as child ,SUM(CASE WHEN InsertDate BETWEEN DATEPART(year, GETDATE()) AND GETDATE()  THEN 1 ELSE 0 END) AS YearCount
from <YourView>
group by product_type, product
)  a
order by parent

0
 
musclejackAuthor Commented:
Hi Hilaire,

one last question - how to display the child that has no record -

for example

customer   product   product type   date
ABC          BOOK      SPORTS        1/1/03
ABC          BOOK      HISTORY       2/6/03
BGH          BOOK      BUSINESS      7/7/03
BGH          CD          BUSINESS      7/7/03
CCC          DVD        SPORTS        5/5/03
CDC          DVD        HISTORY        8/8/03

Result should look something like this:

parent   BOOK      3
child     SPORTS   1
child     HISTORY  1
child     BUSINESS 1

parent   CD            1
child      SPORTs    0
child      HISTORY   0
child      BUSINESS 1

thX
0
 
musclejackAuthor Commented:
Also, when i run the query, it show the child records first then the parent, how to reverse that?

for example:

child
child
parent

child
child
parent

....
0
 
HilaireCommented:
For the ordering part

change
order by parent
into
order by parent, child

Hilaire
0
 
musclejackAuthor Commented:
how about to display the childs that has 0 record?

one last question - how to display the child that has no record -

for example

customer   product   product type   date
ABC          BOOK      SPORTS        1/1/03
ABC          BOOK      HISTORY       2/6/03
BGH          BOOK      BUSINESS      7/7/03
BGH          CD          BUSINESS      7/7/03
CCC          DVD        SPORTS        5/5/03
CDC          DVD        HISTORY        8/8/03

Result should look something like this:

parent   BOOK      3
child     SPORTS   1
child     HISTORY  1
child     BUSINESS 1

parent   CD            1
child      SPORTs    0
child      HISTORY   0
child      BUSINESS 1

thX  - the 500 pts are yours
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.