Display Data in tabular - 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 2008 Forums

Transact-SQL (2008)

Display Data in tabular

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2013-10-09 : 02:25:11

Hi All,

I have table with data:

User Description StartDate EndDate
A AAA 01/Oct/2010 8:00:00 03/Oct/2010 18:00:00
B BBB 01/Oct/2010 13:00:00 01/Oct/2010 18:00:00
C CCC 03/Oct/2010 13:00:00 03/Oct/2010 13:00:00
D DDD 01/Oct/2010 08:00:00 02/Oct/2010 12:00:00
E EEE 02/Oct/2010 08:00:00 02/Oct/2010 12:00:00

Output needed:

User 01/Oct/2010 02/Oct/2010 03/Oct/2010
AM PM AM PM AM PM
----------------------------------------------------------
A AAA AAA AAA AAA AAA AAA
B - BBB - - - -
C - - - - - CCC
D DDD DDD DDD - - -
E - - EEE - - -

Please advise how can i wrote a stored procedure to output the data as tabular above?

Thank you.

Regards,
Micheale

micnie_2020
Posting Yak Master

232 Posts

Posted - 2013-10-09 : 03:50:02

I tried this method:

I replace User to be ID.
Description to be Text.

Declare @date int
Declare @id int
Declare @Curid int
Declare @Curids int
Declare @tbl table
(
id int,
[text] nvarchar(max),
date_ datetime
)

SELECT @id=id,@date =(DATEDIFF(hour, SDt,EDt)/24)+1 from temp2

while(@date != 0 )
Begin
insert into @tbl
SELECT id,[text],cast(dateadd(day,@date,SDt) as Date) from temp2

set @date = @date -1
END

SELECT * from @tbl
order by date_

However, it's can't show all the looping date. Only partial Date show.

http://oi39.tinypic.com/ebbiol.jpg

Refer to image above.
eg: ID 828 should loop 5 times, but it's only loop for 3 times.

Please advise.

Thank you.

Regards,
Micheale

micnie_2020
Posting Yak Master

232 Posts

Posted - 2013-10-09 : 04:13:48

Solved it!

Here you go:
Declare @date int
Declare @id int
Declare @Curid int
Declare @Curids int
Declare @tbl table
(
id int,
[text] nvarchar(max),
date_ datetime
)

DECLARE db_cursor CURSOR FOR
SELECT id from temp2

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @date =(DATEDIFF(hour, SDt,EDt)/24)+1 from temp2 where id in (@id)

while(@date != 0 )
Begin
insert into @tbl
SELECT id,[text],cast(dateadd(day,@date,SDt-1) as Date) from temp2 where id=@id
set @date = @date -1
End

FETCH NEXT FROM db_cursor INTO @id
END

CLOSE db_cursor
DEALLOCATE db_cursor

SELECT * from @tbl
order by date_

Thank you.

Regards,
Micheale

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-09 : 06:48:02

Here's a set based alternative


--sample table for illustration
create table tst
(
UserName varchar(2),
[Description] varchar(10),
StartDate datetime,
EndDate datetime
)
insert tst
values('A','AAA','01/Oct/2010 8:00:00','03/Oct/2010 18:00:00'),
('B','BBB','01/Oct/2010 13:00:00','01/Oct/2010 18:00:00'),
('C','CCC','03/Oct/2010 13:00:00','03/Oct/2010 13:00:00'),
('D','DDD','01/Oct/2010 08:00','02/Oct/2010 12:00:00'),
('E','EEE','02/Oct/2010 08:00:00','02/Oct/2010 12:00:00')


select p.UserName,DATEADD(dd,DATEDIFF(dd,-number,StartDate),0) AS DateVal ,DATEADD(dd,DATEDIFF(dd,-number,StartDate),Start) AS STart,DATEADD(dd,DATEDIFF(dd,-number,StartDate),[End]) AS [ENd],Slot
INTO temp
from tst p
cross join master..spt_values v
cross join (SELECT 'AM' AS Slot,'08:00' AS STart,'12:00' AS [ENd]
             UNION ALL
			 SELECT 'PM' AS Slot,'13:00' AS STart,'18:00' AS [ENd]
			 )t
			 WHERE v.number BETWEEN 0 AND DATEDIFF(dd,startdate,enddate)
			 and v.type='p'
DECLARE @DateList varchar(5000)=''
DECLARE @SQL varchar(max)
SET @DateList=  STUFF((SELECT DISTINCT ',['+ CONVERT(varchar(11),DateVal,121) + Slot + ']'
FROM temp
ORDER BY ',['+ CONVERT(varchar(11),DateVal,121) + Slot + ']' FOR XML PATH('')),1,1,'')
               
  --SELECT @DateList               


		SET @SQl='SELECT UserName,COALESCE(' + REPLACE(@DateList,',',',''---''),COALESCE(') + ',''---'')
		FROM
		(
		SELECT m.UserName,CONVERT(varchar(11),DateVal,121) + Slot AS DateSlot,COALESCE(n.[Description],''---'') AS Description
FROM temp m
			 LEFT JOIN tst n
			 ON n.UserName=m.UserName
			 AND (m.STart BETWEEN n.StartDate AND n.EndDate
			 OR m.[ENd] BETWEEN n.StartDate AND n.EndDate)
			 )t
			 PIVOT (MAX(Description) FOR DateSlot IN (' + @DateList + '))p'
	
	
			 EXEC(@SQL)

--destroy tables after use
DROP TABLE temp
DROP TABLE tst

output
----------------------------------------------------------------------------------------------------------------
UserName	2010-10-01 AM	2010-10-01 PM	2010-10-02 AM	2010-10-02 PM	2010-10-03 AM	2010-10-03 PM
-----------------------------------------------------------------------------------------------------------------
A	        AAA	        AAA	        AAA	        AAA	        AAA	        AAA
B	        ---	        BBB	        ---	        ---	        ---	        ---
C	        ---	        ---	        ---	        ---	        ---	        CCC
D	        DDD	        DDD	        DDD	        ---	        ---	        ---
E	        ---	        ---	        EEE	        ---	        ---	        ---

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources