Publish results of cross tabulation to web using sp

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 2000 Forums

SQL Server Development (2000)

Publish results of cross tabulation to web using sp

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-08-12 : 07:57:12

gf4 writes "Two problems.

1st Problem

Tried to use crosstab, but could not get it to work for my trial. Crosstab works for pubs example though using QA.

I have data in three tables. Schemes (tbl_schemes) have tasks (tbl_tasks), which are started completed on a particular date (an event in tbl_events, which links tasks to schemes). Most schemes have most tasks but not all the time.

Want to display date started or completed for each task (nullspace when no event for task) of a scheme. Ideally want completed dates where exist and start dates in lieu.

So want to end up with

scheme description task1 task2 task3
s 1 build road 11/02/03 14/02/03 17/02/03
s 2 build bridge 10/02/03 23/03/03 01/04/03

By using case, ended up with

SELECT schemeid, schemedes,
MAX(CASE taskid
WHEN 1 THEN eventdate -- tried converting w/o success)
ELSE 0
END) as [site visit], -- i.e. task_des where taskid = 1
MAX(CASE taskid
WHEN 2 THEN eventdate
ELSE 0
END) as [site notes] -- i.e. task_des where taskid = 2

-- case for taskid goes up to 10 or more

FROM dbo.vw_crosstab
GROUP BY schemeid
ORDER BY schemeid

where vw_crosstab is

SELECT
dbo.tbl_schemes.schemeid,
dbo.tbl_schemesdes,
dbo.tbl_tasks.taskid,
dbo.tbl_tasks.task_des,
dbo.tbl_events.eventdate
FROM
dbo.tbl_events INNER JOIN dbo.tbl_tasks
ON
dbo.tbl_events.taskid = dbo.tbl_tasks.taskid
INNER JOIN
dbo.tbl_schemes
ON
dbo.tbl_events.schemeid = dbo.tbl_schemes.schemeid

But convert of date produces error (could not convert to int) or produces 38765 or produces date time (just want the date).

Also having hard time believing that I cannot automate this some how but am not a SQL rocket scientist.

2nd Problem

Data is already in xls spreadsheet and want to reverse pivot to export to sql tables but not certain how to extrapolate reverse pivot to this more complicated requirement.

w2k sp3? sql 2000 sp3

Please email me if you come up with something relatively simple to understand."

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-08-12 : 09:00:37

1) ...convert(varchar(10), eventdate, 101)...ELSE NULL END

2)
create table myTable(
scheme varchar(length of scheme data)
, description varchar(50) -- how long string needed
, taskid int -- assuming it is integer varchar(length) if character
, eventdate smalldatetime
)
insert into myTable (scheme, description, taskid, eventdate)
select scheme, description, taskid, eventdate
from
(
select scheme, description, taskid = <value for taskid1>, taskid1
from crosstab
union all
select scheme, description, <value for taskid2>, taskid2
from crosstab
union all...

...) x

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources