Matrix - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Matrix

Author

Topic

muek
Starting Member

16 Posts

Posted - 2008-12-29 : 19:58:45

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?

muek
Starting Member

16 Posts

Posted - 2008-12-29 : 20:41:36

Some friends talk about crosstabs, but I think that don't work with "unknown machine number"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 00:34:15

try this method then

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

muek
Starting Member

16 Posts

Posted - 2008-12-30 : 09:56:39

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?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 11:38:00

[code]
SELECT *
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY workID ORDER BY machineID) AS Seq,*
FROM Table
)t
PIVOT (SUM(hours) FOR Seq IN ([1],[2]))p
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-30 : 11:51:19

[code]
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
[/code]

muek
Starting Member

16 Posts

Posted - 2008-12-30 : 18:24:24

Hi visakh16,

I thought that I knew SQL, don't I was wrong.

Thank you so much
Your 2º solution solve my problem

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 02:52:14

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources