943,875 Members | Top Members by Rank

Web Development > Databases > MS SQL > Crazy CrossTab problems
Ad:
  • MS SQL Discussion Thread
  • Unsolved
  • Views: 1288
  • MS SQL RSS
You are currently viewing page 1 of this multi-page discussion thread
Permalink
Dec 29th, 2008
0

Crazy CrossTab problems

Expand Post »
Hi there,
I'm completely lost.

I have 3 tables:
- work (workID, description)
- machine (machineID, name)
- work_machine (machineID, workID, hours)

What I want is to obtain a result similar to:

work, machine1, machine2
A , 1 ,
B , 3 , 5

The problem is that the number of works and the number of machines is unknown.

Can anyone help?
Reputation Points: 10
Solved Threads: 0
Newbie Poster
muek is offline Offline
9 posts
since Dec 2008
Permalink
Dec 30th, 2008
0

Re: Crazy CrossTab problems

I think what you can do on your work_machine table is have references to the workid and machineid...

work_machineID | workID | machineID
1 | A | 1
2 | B | 3
3 | B | 5

Then just select from this table where workID = 'A';
Last edited by kanaku; Dec 30th, 2008 at 1:43 am. Reason: wrong label =(
Reputation Points: 70
Solved Threads: 15
Posting Whiz
kanaku is offline Offline
378 posts
since Jan 2007
Permalink
Dec 30th, 2008
0

Re: Crazy CrossTab problems

Muke, seems you didn't normalize your database design!!
Featured Poster
Reputation Points: 480
Solved Threads: 276
Postaholic
Ramy Mahrous is offline Offline
2,189 posts
since Aug 2006
Permalink
Dec 30th, 2008
0

Re: Crazy CrossTab problems

Ok, I think that I haven't explain myself.

In table "work_machine" I have:
workID | machineID | hours
A | M1 | 1
B | M2 | 3
B | M1 | 5

Now I would like to know how to make the query to obtain the data in the format:

work | machine1 | machine2
A | 1 | 0
B | 3 | 5

Any idea?
Reputation Points: 10
Solved Threads: 0
Newbie Poster
muek is offline Offline
9 posts
since Dec 2008
Permalink
Dec 30th, 2008
0

Re: Crazy CrossTab problems

What's machine1 and machine2 ?? and what '0' in the first row in output?
Featured Poster
Reputation Points: 480
Solved Threads: 276
Postaholic
Ramy Mahrous is offline Offline
2,189 posts
since Aug 2006
Permalink
Dec 30th, 2008
0

Re: Crazy CrossTab problems

Hi,

machine1 and machine2 are rows from machine table
The 0 represents that machine2 don't do any "A" work

Click to Expand / Collapse  Quote originally posted by RamyMahrous ...
What's machine1 and machine2 ?? and what '0' in the first row in output?
Reputation Points: 10
Solved Threads: 0
Newbie Poster
muek is offline Offline
9 posts
since Dec 2008
Permalink
Dec 30th, 2008
0

Re: Crazy CrossTab problems

As I said, you've lack in your design, I am sorry but your database design is wrong. It need some normalization
Featured Poster
Reputation Points: 480
Solved Threads: 276
Postaholic
Ramy Mahrous is offline Offline
2,189 posts
since Aug 2006
Permalink
Dec 30th, 2008
0

Re: Crazy CrossTab problems

Hi again! Maybe you can try reading this: some-thread-on-sums.

Or this one @ daniweb.

Or ask RamyMahrous to describe the proper table structure. =)
Reputation Points: 70
Solved Threads: 15
Posting Whiz
kanaku is offline Offline
378 posts
since Jan 2007
Permalink
Dec 30th, 2008
0

Re: Crazy CrossTab problems

Hi RamyMahrus,

my DB design is the following:
I have 3 tables:
- work (workID, description)
- machine (machineID, name)
- work_machine (machineID, workID, hours)

Can you tell me what's wrong?


Click to Expand / Collapse  Quote originally posted by RamyMahrous ...
As I said, you've lack in your design, I am sorry but your database design is wrong. It need some normalization
Reputation Points: 10
Solved Threads: 0
Newbie Poster
muek is offline Offline
9 posts
since Dec 2008
Permalink
Dec 30th, 2008
0

Re: Crazy CrossTab problems

That's correct give me real sample data to all these tables + output you need not table just in english text what you need to extract from these tables.
Featured Poster
Reputation Points: 480
Solved Threads: 276
Postaholic
Ramy Mahrous is offline Offline
2,189 posts
since Aug 2006

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in MS SQL Forum Timeline: Need help in getting Average using SQL Cross Tab
Next Thread in MS SQL Forum Timeline: Get last month from timestamp





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC