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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
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

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
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


HilaireCommented:
No problem for the additional field

Back in a few secs

Does the second query work ?

Hilaire
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

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
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

....
HilaireCommented:
For the ordering part

change
order by parent
into
order by parent, child

Hilaire
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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.