column prob - 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

General SQL Server Forums

New to SQL Server Programming

column prob

Author

Topic

see199
Starting Member

21 Posts

Posted - 2007-06-14 : 00:12:54

repairJob(repairJobID, description)
->('overhaul','blablabla...')
->('gearbox','blablabla...')

sparePart(sparePartID, description ,cost)
->('sp1', 'oil+bolt+...', 100)
->('sp2', 'bolt, clamp...', 200)
->('sp3', 'protector, cover..', 500)

repair(repairID, repairJob, sparePartID, cost, workShopID)
->('r001', 'overhaul', 'sp1', 100, 'w001')
->('r002', 'overhaul', 'sp2', 200, 'w001')
->('r003', 'gearbox', 'sp3', 500, 'w002')
->('r004', 'gearbox', 'sp2', 200, 'w002')

output:

Is it possible to make it?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-14 : 00:15:55

yes. Pivot / crosstab table

use the same method as the last thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=84934

see199
Starting Member

21 Posts

Posted - 2007-06-14 : 02:36:56

is it this code?
USE Northwind
GO

CREATE TABLE Pivot
( Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
GO

SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO

from my understanding, this is the static column, what if i wan to make it dynamic?
which means if the user enter new sparePart, it'll automatically included in the sql.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-14 : 02:40:17

See this: [url]http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-14 : 02:43:35

quote:
this is the static column, what if i wan to make it dynamic?

You mean the number of columns may change at runtime ?

see199
Starting Member

21 Posts

Posted - 2007-06-14 : 02:53:47

quote:
Originally posted by harsh_athalye

See this: [url]http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

OMG~ i cant understand

see199
Starting Member

21 Posts

Posted - 2007-06-14 : 02:54:52

quote:
Originally posted by khtan

quote:
this is the static column, what if i wan to make it dynamic?

You mean the number of columns may change at runtime ?

KH

yup. and i saw an example in the post b4 urs. but i hardly understand

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-14 : 02:58:07

You mean this http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp ?
This is for SQL 2005. It is using the new PIVOT function in 2005. Are you using SQL 2000 or 2005 ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-06-14 : 02:59:25

http://weblogs.sqlteam.com/jeffs/articles/5120.aspx
http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables
http://www.sqlteam.com/article/dynamic-crosstabs

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

see199
Starting Member

21 Posts

Posted - 2007-06-14 : 03:01:09

quote:
Originally posted by khtan

You mean this http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp ?
This is for SQL 2005. It is using the new PIVOT function in 2005. Are you using SQL 2000 or 2005 ?

KH

yup.. that one. i'm using m/s sql server 2005

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-14 : 08:51:34

quote:
Originally posted by see199

quote:
Originally posted by harsh_athalye

See this: [url]http://www.sqlservercentral.com/columnists/plarsson/pivottableformicrosoftsqlserver.asp[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

OMG~ i cant understand

Then read about Cross-tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources