Crazy CrossTab problems
 

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?

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

Re: Crazy CrossTab problems
 

Muke, seems you didn't normalize your database design!!

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?

Re: Crazy CrossTab problems
 

What's machine1 and machine2 ?? and what '0' in the first row in output?

Re: Crazy CrossTab problems
 

Hi,

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

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

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. =)

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?

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.

Re: Crazy CrossTab problems
 

Here the solution that I got from another forum:

SELECT ROW_NUMBER() OVER (PARTITION BY workID ORDER BY machineID) AS Seq,* INTO #Temp

FROM Table



DECLARE @MachineList varchar(1000),@Sql varchar(max)



SELECT @MachineList= LEFT(ml.MacList,LEN(ml.MacList)-1)

FROM (SELECT DISTINCT CAST(Seq AS varchar(10)) + ','

      FROM #Temp

      FOR XML PATH('')

     )ml(MacList)



SET @Sql='SELECT *

FROM (SELECT *

FROM #Temp

)t

PIVOT (SUM(hours) FOR Seq IN (['+ REPLACE(@MachineList,',','],[') + ']))p'

EXEC(@Sql)

DROP TABLE #Temp