Possible Cross tab type query - 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)

Possible Cross tab type query

Author

Topic

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-12-16 : 01:41:34

I think this one will be a challenge even for the experts out there.

i have a simple table (see image - top half)
Script for recreating table is below.

What I'd like to do is a kind of cross tab where the activities for each user appear under each day and in the top row for each day (rather than having lots of gaps as in the normal cross tab query where it has one row for reference.
Hope this makes sense.


create table T ( 
    ondate datetime , 
    userid varchar(10) null ,
    Appt_Ref varchar(30) null 
);

insert into T values ('2010-12-13 00:00:00:000' ,'IAIN','Go to Shops' );
insert into T values ('2010-12-13 00:00:00:000' ,'ROB','Wash car' );
insert into T values ('2010-12-13 00:00:00:000' ,'IAIN','Buy Stamps' );
insert into T values ('2010-12-14 00:00:00:000' ,'IAIN','Clean Windows' );
insert into T values ('2010-12-14 00:00:00:000' ,'IAIN','Fix Shed Door' );
insert into T values ('2010-12-14 00:00:00:000' ,'ROB','Re-install Windows' );
insert into T values ('2010-12-15 00:00:00:000' ,'IAIN','Write letter' );
insert into T values ('2010-12-16 00:00:00:000' ,'IAIN','Visit Grandma' );
insert into T values ('2010-12-17 00:00:00:000' ,'IAIN','Marinade lamb' );
insert into T values ('2010-12-17 00:00:00:000' ,'IAIN','Buy tickets for x' );
insert into T values ('2010-12-17 00:00:00:000' ,'IAIN','Make Xmas Cards' );
insert into T values ('2010-12-17 00:00:00:000' ,'ROB','Watch Movie' );
insert into T values ('2010-12-17 00:00:00:000' ,'ROB','Order new coat' );

select * from t

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-12-16 : 15:44:44

Please someone reply - even if its to say "no it cannot be done"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-12-16 : 16:05:03

You're better off doing this in a report layer. This is the closest I've gotten:

select userid, rn, [2010-12-13],[2010-12-14],[2010-12-15],[2010-12-16],[2010-12-17] from
(select convert(char(10), ondate, 120) ondate, userid , Appt_Ref, row_number() over (PARTITION BY ondate, userid ORDER BY Appt_Ref) rn from T) a
pivot (max(Appt_ref) for ondate in([2010-12-13],[2010-12-14],[2010-12-15],[2010-12-16],[2010-12-17])) b
order by userid

If you need the date headings to be dynamic, it's going to be difficult to do in SQL Server. You can try this:

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

icw
Constraint Violating Yak Guru

378 Posts

Posted - 2010-12-16 : 22:32:56

Thanks a lot

I think what you have may work.
I'll look into it.
Thanks again

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources