Dynamic Column Header

Question

Post reply

Dynamic Column Header

prakashdotc

Ten Centuries

Points: 1165
More actions
December 21, 2008 at 10:11 pm

#94618

Hi All,
I already have a query which retrieve data based on various dates, now i need is the Column header with those 'Date'.
Dynamic Dates as Column Header.
Is there any way to do this SQL Server 2005.
Thanks in Advance.
Prakash.C

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

prakashdotc (12/21/2008)
Is there any way to do this SQL Server 2005.

Yes.

If you could supply us with some details or examples of what you want, we could demonstrate it for you.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

krayknot SSCertifiable Points: 7998 More actions · Answer 2

prakashdotc (12/21/2008)
Hi All,
I already have a query which retrieve data based on various dates, now i need is the Column header with those 'Date'.
Dynamic Dates as Column Header.
Is there any way to do this SQL Server 2005.
Thanks in Advance.
Prakash.C

Thsi is a sample only

declare @colname varchar(100)

declare @rowcolname varchar(1000)

set @rowcolname = ''

declare col CURSOR FOR

select id from actionstate

open col

fetch next from col into @colname

while @@fetch_status = 0

begin

set @rowcolname = @rowcolname + '[' + @colname + '] int,'

fetch next from col into @colname

end

close col

deallocate col

print('Create TAble tablename('+ @rowcolname + ')')

exec('Create TAble tablename('+ @rowcolname + ')')

select * from tablename

drop tablename

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

prakashdotc Ten Centuries Points: 1165 More actions · Answer 3

Hi All,

Here's my description about my Query

i Have a Table with Columns as ProdId, Value, Date

Users will enter some value for each product on various date

The Task is to Retrieve the last 5 days record, with 5 Date's as it's header like

--------------------------------------------------------------------------------------------

ProductId - Date 1 - Date 2 - Date 3 - Date 4 - Date 5

--------------------------------------------------------------------------------------------

1 - 234 - 989 - 876 - 7686 - 9878

2 - 34 - 99 - 876 - 66 - 87

--------------------------------------------------------------------------------------------

Thanks

Prakash.C

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 4

prakashdotc (12/22/2008)
Hi All,
Here's my description about my Query
i Have a Table with Columns as ProdId, Value, Date
Users will enter some value for each product on various date
The Task is to Retrieve the last 5 days record, with 5 Date's as it's header like
--------------------------------------------------------------------------------------------
ProductId - Date 1 - Date 2 - Date 3 - Date 4 - Date 5
--------------------------------------------------------------------------------------------
1 - 234 - 989 - 876 - 7686 - 9878
2 - 34 - 99 - 876 - 66 - 87
--------------------------------------------------------------------------------------------
Thanks
Prakash.C

In SQL 2005, use PIVOT function you get expected result, for more info see SQL BOL.

prakashdotc Ten Centuries Points: 1165 More actions · Answer 5

The Problem is not retreving records in this format, but the Column Header must be Dynamic Date

--------------------------------------------------------------------------------------------

ProductId - Date 1 - Date 2 - Date 3 - Date 4 - Date 5

--------------------------------------------------------------------------------------------

1 - 234 - 989 - 876 - 7686 - 9878

2 - 34 - 99 - 876 - 66 - 87

--------------------------------------------------------------------------------------------

like this

--------------------------------------------------------------------------------------------

ProductId - 12-12-2008 - 13-12-2008 - 14-12-2008 - 15-12-2008 - 16-12-2008

--------------------------------------------------------------------------------------------

1 - 234 - 989 - 876 - 7686 - 9878

2 - 34 - 99 - 876 - 66 - 87

--------------------------------------------------------------------------------------------

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 6

Hi,

You can create dynamic query for PIVOT and then run it using sp_executesql.

Best Regards

Nitin

http://www.enlinkURL.com

Regards,
Nitin

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 7

prakashdotc (12/22/2008)
The Problem is not retreving records in this format, but the Column Header must be Dynamic Date
--------------------------------------------------------------------------------------------
ProductId - Date 1 - Date 2 - Date 3 - Date 4 - Date 5
--------------------------------------------------------------------------------------------
1 - 234 - 989 - 876 - 7686 - 9878
2 - 34 - 99 - 876 - 66 - 87
--------------------------------------------------------------------------------------------
like this
--------------------------------------------------------------------------------------------
ProductId - 12-12-2008 - 13-12-2008 - 14-12-2008 - 15-12-2008 - 16-12-2008
--------------------------------------------------------------------------------------------
1 - 234 - 989 - 876 - 7686 - 9878
2 - 34 - 99 - 876 - 66 - 87
--------------------------------------------------------------------------------------------

Try like the following code:

use AdventureWorks

go

declare@FromDatedatetime

,@Todatedatetime

,@COLNamevarchar (max)

,@SQLvarchar (max)

select@FromDate= '2002-12-01'

,@ToDate= '2004-01-30'

,@COLName= ''

,@SQL= ''

while@FromDate <= @ToDate

begin

if(@COLName = '')

begin

set@COLName= '[' + convert (varchar (10), @FromDate, 121) + ']'

end

else

begin

set@COLName= @COLName + ', [' + convert (varchar (10), @FromDate, 121) + ']'

end

set@FromDate = dateadd (d, 1, @FromDate)

end

print@COLName

select@SQL= 'select VendorID, ' + @COLName +

' From (select PurchaseOrderID, OrderDate, VendorID from Purchasing.PurchaseOrderHeader) as P

pivot

(count (PurchaseOrderID)

for OrderDate in (' + @COLName + ')

) as pvt

order by VendorID

'

print@SQL

exec(@SQL)

go

Note: This work on SQL 2005 and above versions.

BI_NewBie SSCrazy Points: 2261 More actions · Answer 8

Hi,

I have similar issue.

I have managed to create pivot in SQL 2005 for date column header but problem is while exporting the data into some flat file i am not able to retrieve Column header. infact i cant map them since the column are dynamic.

Kindly help me in this regard,

Deepika