Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamic Column Header Expand / Collapse
Author
Message
Posted Sunday, December 21, 2008 10:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 7, 2011 10:00 PM
Points: 19, Visits: 66
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
Post #623711
Posted Sunday, December 21, 2008 10:32 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
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.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #623716
Posted Sunday, December 21, 2008 10:55 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 7, 2011 1:41 AM
Points: 346, Visits: 534
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
Post #623723
Posted Monday, December 22, 2008 1:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 7, 2011 10:00 PM
Points: 19, Visits: 66
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
Post #623769
Posted Monday, December 22, 2008 1:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 13, 2014 5:49 AM
Points: 1,391, Visits: 608
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.



Post #623771
Posted Monday, December 22, 2008 2:53 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, February 7, 2011 10:00 PM
Points: 19, Visits: 66
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
--------------------------------------------------------------------------------------------
Post #623790
Posted Monday, December 22, 2008 3:09 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, August 11, 2014 7:59 AM
Points: 879, Visits: 284
Hi,

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

Best Regards
Nitin

http://www.enlinkURL.com


Regards,
Nitin

Post #623797
Posted Monday, December 22, 2008 3:25 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 13, 2014 5:49 AM
Points: 1,391, Visits: 608
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 @FromDate datetime
, @Todate datetime
, @COLName varchar (max)
, @SQL varchar (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.



Post #623806
Posted Friday, October 4, 2013 5:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 31, 2014 4:49 AM
Points: 9, Visits: 41
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
Post #1501530
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse