musclejack
asked on
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
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
or this one
select coalesce(product,product_t
group by product_type, product
with rollup
Hilaire
select coalesce(product,product_t
group by product_type, product
with rollup
Hilaire
ASKER
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
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
ASKER
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
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
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
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
No problem for the additional field
Back in a few secs
Does the second query work ?
Hilaire
Back in a few secs
Does the second query work ?
Hilaire
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
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
ASKER
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
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
ASKER
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
....
for example:
child
child
parent
child
child
parent
....
For the ordering part
change
order by parent
into
order by parent, child
Hilaire
change
order by parent
into
order by parent, child
Hilaire
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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