"Leszek Gruszka" <le************@kana.com.pl> wrote in message
news:41**********************@news.newsgroups.ws.. .
One more thing - Data (only last data for each station) will be as
parametr too.
My query for it is:
Select * FROM KanaSecRep AS jeden
WHERE data = (SELECT DISTINCT MAX(data) FROM KanaSecRep AS dwa
WHERE jeden.station = dwa.station)
Order by Data DESC
So my first table after this SELECT will looks something like that:
Data | Station | AuditBase | NoCDROM
---------------------------------------------------
23/03/2003| Station1 | 0 | 1
23/03/2003| Station2 | 1 | 1
23/03/2003| Station3 | 0 | 1
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
I had a look at your data, but it look like a case where TSQL is not the
best solution (see code below). You need to do rows to columns (stations)
and also columns to rows (AuditBase, NoCDROM) in the same result set, and
the solution must be dyamic because you don't know the number of stations in
advance.
This is possible, using cursors and temp tables, but personally I wouldn't
try it - any TSQL code will be awkward, and also very difficult to maintain.
If you need to change the way the output is displayed, or add more columns,
then it will take more work to do that.
Since the real question is formatting the data, I would use a reporting tool
if you have it (Crystal Reports, Business Objects etc.). If not, then using
VB, C#, Perl etc. will be a lot clearer and simpler to maintain - those
languages support looping, which makes it relatively easy to iterate over
something like an ADO RecordSet object.
Finally, please do not post table data in the format above - this means that
someone else has to write their own CREATE TABLE and INSERT statements:
http://www.aspfaq.com/etiquette.asp?id=5006
Simon
create table leszek (
data datetime not null,
station int not null,
auditbase int not null,
nocdrom int not null
)
go
insert into leszek select '19990121', 1, 1, 1
insert into leszek select '19990121', 2, 0, 0
insert into leszek select '19990121', 3, 1, 1
insert into leszek select '20020222', 1, 1, 0
insert into leszek select '20020222', 2, 1, 0
insert into leszek select '20020222', 3, 1, 0
insert into leszek select '20030323', 1, 0, 1
insert into leszek select '20030323', 2, 1, 1
insert into leszek select '20030323', 3, 0, 1
go
/* The following query would need to be completely dynamic in order to
** provide a solution to your question. Building this dynamically in TSQL
** would be awkward, and difficult to extend. It would be better to return
** a simpler result set, then format it in a client application.
*/
select * from (
select
data as 'Data',
'AuditBase' as 'Parameter',
max(case when station = 1 then auditbase else null end) as 'station1',
max(case when station = 2 then auditbase else null end) as 'station2',
max(case when station = 3 then auditbase else null end) as 'station3'
from leszek
group by data
union all
select
data,
'NoCDROM',
max(case when station = 1 then nocdrom else null end) as 'station1',
max(case when station = 2 then nocdrom else null end) as 'station2',
max(case when station = 3 then nocdrom else null end) as 'station3'
from leszek
group by data
) dt
order by 1, 2
