Display Column values as Rows - 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

Display Column values as Rows

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2010-08-02 : 07:47:58

Dear Friends,

I have the select statement output format in below format
Suppose i am selecting March 2010 employee attendance means

EMP.NO AbsentDate
10210 05-Mar-2010
10222 08-Mar-2010
10210 18-Mar-2010

But what i want to do is. when i give the input as March 2010, i want to get the results in below format, please find the Expected output

EMP.NO 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 ... upto 31
10210 P P P P A P P P P P P P P P P P P A P
10222 P P P P P P P A P P P P P P P P P P P

Here P - Present , A - Absent

Could you please help me to get the expected output, I tried some basic PIVOT, But i could not get my expected result.

Thanks and looking forward to hear from you.

michael.appleton
Posting Yak Master

160 Posts

Posted - 2010-08-02 : 08:34:29

This should be done in the application, not using SQL.

dhinasql
Posting Yak Master

195 Posts

Posted - 2010-08-02 : 08:38:07

I want to generate a Excel report from the output of SELECT statement, And i do not want to do with application.

Is it possible to get my expected output with SQL Stored procedures.

Looking forward to hear from you guys.........

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-02 : 08:44:21

try this:


create table emp
(e_id int
,date datetime)

insert into emp
select 10210, '2010/03/05' union all
select 10210, '2010/03/08' union all
select 10211, '2010/03/02' union all
select 10213, '2010/03/04' 

create table date
(id int identity(1,1)
,date datetime)

insert into date
select '2010/03/01' union all
select '2010/03/02' union all
select '2010/03/03' union all
select '2010/03/04' union all
select '2010/03/05' union all
select '2010/03/06' union all
select '2010/03/07' union all
select '2010/03/08' 


select e,[1],[2],[3],[4],[5],[6],[7],
from
(select   e.e_id as emp
		,day(d.date)as days
		,e.e_id as e
		from date as d left join emp as e on d.date = e.date
) as p
PIVOT
(
  count(emp) for days in ([1],[2],[3],[4],[5],[6],[7],) 

)as pivotTBL
where pivotTBL.e is not null


drop table emp
drop table date

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-02 : 09:04:24

sorry for eight-balls :) it should be [ 8 ] :)

dhinasql
Posting Yak Master

195 Posts

Posted - 2010-08-03 : 02:25:05

Thanks lot, it helped me to get expected output.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-03 : 02:27:15

i think you need to have dynamic pivot as number of days wont be same for all months

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dhinasql
Posting Yak Master

195 Posts

Posted - 2010-08-03 : 02:40:30

Yes Vikash.

If you have simplified and dynamic pivot query it will be help me lot...

Thanks and looking forward to hear from you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-03 : 02:47:54

see the below link

make a try and if you face issue, we will help you out

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources