Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2003-12-10
12
Medium Priority
?
268 Views
Last Modified: 2008-03-06
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
0
Comment
Question by:musclejack
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 9910682
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 9910801
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
 

Author Comment

by:musclejack
ID: 9910928
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:musclejack
ID: 9910965
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 9910968
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 9910977
No problem for the additional field

Back in a few secs

Does the second query work ?

Hilaire
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 9911009
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
 

Author Comment

by:musclejack
ID: 9911066
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
 

Author Comment

by:musclejack
ID: 9911095
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
 
LVL 26

Expert Comment

by:Hilaire
ID: 9911115
For the ordering part

change
order by parent
into
order by parent, child

Hilaire
0
 

Author Comment

by:musclejack
ID: 9911123
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
 
LVL 26

Accepted Solution

by:
Hilaire earned 2000 total points
ID: 9911152
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

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question