Dynamic Pivot - 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)

Dynamic Pivot

Author

Topic

S_Lakshmi
Starting Member

22 Posts

Posted - 2008-12-02 : 19:59:01

Hello friends,

Here is the situation i have. I have a table with sample data



Create Table UserComments( SchId int Not null identity,Usr_Name varchar(25), Dateof Datetime, Comments Varchar(200))

Insert into UserComments (Usr_Name,Dateof,Comments  )
Select 'A', GETDATE(), 'Test0' union all 
Select 'B', GETDATE()+1, 'Test1' union all 
Select 'C', GETDATE()+2, 'Test2' union all 
Select 'D', GETDATE()+3, 'Test3' union all 
Select 'E', GETDATE()+4, 'Test4'

I/P

Begdate (DateOF)
EndDate (DateOf)
UserName

Expected O/P
Usr_Name, [dateof],[dateof],[dateof],[dateof],.......................[dynamic]]
A Comments
B Comments

I am sitting this issue for last two days. Can any one help me to get rid of this issue Please..?

Thanks
Lakshmi

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-12-02 : 20:02:19

Please explain your expected output as it isn't clear without a description.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-12-02 : 20:05:43

See madhi's blog for dynamic pivot:

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

S_Lakshmi
Starting Member

22 Posts

Posted - 2008-12-02 : 20:09:10

Thanks Tara,

Here is the expected output.

Username [2008-12-02] [2008-12-03] [2008-12-04] [2008-12-05] [2008-12-06]
A Test0
B Test1
C Test2

The dates will be dynamic.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 23:51:33

[code]DECLARE @DateList varchar(max),@Sql varchar(max)

SELECT @DateList= LEFT(dl.datelist,LEN(dl.datelist)-1)
FROM (SELECT DISTINCT convert(varchar(11),DateOf,103) + ','
FROM Table
FOR XML PATH(''))dl(datelist)

SET @Sql='select *
from (select SchId,Amt,convert(varchar(11),DateOf,103) as ddate from @test)t
pivot (max(Comments) for ddate in (['+ REPLACE(@DateList,',','],[') +']))p'
EXEC (@Sql)[/code]

S_Lakshmi
Starting Member

22 Posts

Posted - 2008-12-03 : 10:15:56

It is awesome Visakh16. Thanks. This is wat I expected.

Thanks for all of you Once again to learn the Dynamic Pivot.

Lakshmi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 10:22:18

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources