How to show Column wise data\ - 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 2000 Forums

SQL Server Development (2000)

How to show Column wise data\

Author

Topic

wagau999
Starting Member

5 Posts

Posted - 2008-11-06 : 08:19:49

Hi All,
i am using SQL Server 2000 and T-sql
I have Table Named as tbl_AgentDownTime and the columns are as follows
LoginID , Start_Date, End_Date , Total Time
I have a report which will show me infomation like the following

Log_ID St_Dt End_Dt Total Time
1 12-04-07 00:10:00 12-04-07 00:20:00 10
1 12-04-10 01:10:10 12-04-10 00:20:00 30
2 12-04-07 00:10:00 12-04-07 00:20:00 10
2 12-04-07 02:10:00 12-04-07 00:20:00 10
2 12-04-07 03:10:00 12-04-07 00:20:00 10
3 12-04-07 04:10:00 12-04-07 00:20:00 10

Now i would like to see the data like this in a linear fashion:
If Login ID 1 Has two rows then it should show me like this
Log_ID ST_Dt1 EndDt1 St_Dt2
1 12-04-07 00:10:00 12-04-07 00:20:00 12-04-10 01:10:10
EndDt2 Total_Time
12-04-10 00:20:00 40

If Login ID 2 Has 3 rows then it should show 9 Columns

However differnt Loginid will have different Start_Date and End_Date of data in it so some may have 5 rows othere could have ten?
is this possiable ?

Thank you
Gaurav

"A successful man is one who can lay a firm foundation with the bricks others have thrown at him."

wagau999
Starting Member

5 Posts

Posted - 2008-11-06 : 08:52:33

Can anybody help me out

"A successful man is one who can lay a firm foundation with the bricks others have thrown at him."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 09:14:47

you need to make use of dynamic sql here if the number of records can vary for a Login_ID. Can you atleast be sure of maximum number of records that can exist for login_ID?

wagau999
Starting Member

5 Posts

Posted - 2008-11-06 : 09:20:18

No it can be upto maximum 100 for a day.

Gaurav

"A successful man is one who can lay a firm foundation with the bricks others have thrown at him."

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-06 : 10:09:47

Try this out:-

DECLARE @MaxRec int,@Cnt int,@Sql varchar(8000)
SELECT @MaxRec=MAX(RecCount),@Cnt=1
FROM (SELECT COUNT(*) AS RecCount
      YourTable
      GROUP BY Log_ID)t


WHILE @Cnt<=@MaxRec
BEGIN
SET @Sql=COALESCE(@sql,'')+',MAX(CASE WHEN Seq= '+ @Cnt + ' THEN St_Dt ELSE NULL END) AS St_Dt'+ @Cnt + ',MAX(CASE WHEN Seq= '+ @Cnt + ' THEN End_Dt ELSE NULL END) AS End_Dt' + @Cnt

SET @Cnt=@Cnt+1
END

SET @Sql='SELECT Log_ID,Total_Time'+ @Sql + ' FROM (SELECT Log_ID,
St_Dt,
End_Dt,
Total_Time,
(SELECT COUNT(*) FROM YourTable WHERE Log_ID=t.Log_ID AND St_Dt<=t.St_Dt) AS Seq
FROM YourTable t)r'

EXEC (@Sql)

wagau999
Starting Member

5 Posts

Posted - 2008-11-07 : 04:26:32

Hi Vikas,
I ve developed the solution. I will also try ur solution later on.
What i ve' developed is this.

declare @StartDate DateTime, @EndDate DateTime
select @StartDate = @DateStart, @EndDate = @DateEnd

declare @r as bigint, @i as bigint

select * into #tbl_AgentDnTime from tbl_AgentDnTime
where (Login_Time between @DateStart and @EndDate) and Logout_Time <= @EndDate

select @i=1, @r=Max(Total) from ( select LoginID, Count(LoginID) Total from #tbl_AgentDnTime group by LoginID ) a
--select @r

select LoginID, Sum(Total_Time) TotalTime into #Report1 from #tbl_AgentDnTime group by LoginID --order by 1

while @i <= @r
begin
exec ('alter table #Report1 add Login_Time'+@i+' datetime ')
exec ('alter table #Report1 add Logout_Time'+@i+' datetime ')

exec (' update #Report1 set Login_Time'+@i+'=MinDate, Logout_Time'+@i+'=MaxDate
from #Report1 R join ( select LoginID, Min(Login_Time) MinDate, Min(Logout_Time) MaxDate
from #tbl_AgentDnTime group by LoginID) A
on R.LoginID= A.LoginID ')

delete #tbl_AgentDnTime
from ( select LoginID, Min(Login_Time) StartDate --, Min(Dt_End) EndDate
from #tbl_AgentDnTime A group by LoginID) A
where #tbl_AgentDnTime.LoginID = A.LoginId and #tbl_AgentDnTime.Login_Time = A.StartDate

select @i = @i + 1
end

select * from #Report1
drop table #Report1
drop table #tbl_AgentDnTime

"A successful man is one who can lay a firm foundation with the bricks others have thrown at him."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources