Crazy CrossTab problems

[muek]

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?

[kanaku]

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

[Ramy Mahrous]

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

[muek]

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?

[Ramy Mahrous]

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

[muek]

Hi,

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

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

[Ramy Mahrous]

As I said, you've lack in your design, I am sorry but your database design is wrong. It need some normalization

[kanaku]

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

[muek]

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?

As I said, you've lack in your design, I am sorry but your database design is wrong. It need some normalization

[Ramy Mahrous]

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.

[muek]

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