Change rows to columns specifically in sql 2005

you can't do it unless there is a maximum number of distinct values for each date

Thats my question...
Is there any possibility...?

Theres a PIVOT operation
But i dont think that would work
as i cant supply the datevalues
to work with, as all are fetched
at runtime

pivot will work only if you know in advance which values you will receive

Well in the query am selecting the date START_DATETIME
But it doesnt matter that can be avoided for the purpose

yea thats what i said as well .
For pivot i dont know date in advance.
I think it may be possible...
But really difficult...

You can use a mixture of a dynamic query and over statement to get it done.

The main thought is this...will have to convert to dynamic to complete the solution, but here is the jist.

(the part that you will need dynamic is the listing of possible row numbers unless you know the maximum per day and it is finite as asked by momi.  Then you can list our manually as I have done.

SELECT *
FROM (
SELECT  T.DURATION AS [VALUE]
, row_number() OVER (PARTITION BY DATEADD(D, 0, DATEDIFF(D, 0, START_DATETIME)) ORDER BY START_DATETIME) AS RowNumber
, DATEADD(D, 0, DATEDIFF(D, 0, START_DATETIME)) AS [DATE]
FROM [TRAINING-SESSION] AS T JOIN [SCHEDULE-ACT] AS S 
ON T.I_SCHEDULE_ACT = S.I_SCHEDULE_ACT 
WHERE START_DATETIME between @pistartdate and @pienddate 
) 
PIVOT (SUM([VALUE]) FOR RowNumber IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)) p

Select allOpen in new window

just for ref to do it in dynamic sql with distinct values you can check this post

https://www.experts-exchange.com/questions/23801801/Pivot-or-Crosstab.html

Well mwvisa1,that sounds too good.
I will check your solution and get
back to you soon...

Am started checking ur post...
Obviously what i found was
PIVOT (SUM([VALUE]) FOR RowNumber IN (1, 2)) p
---This is not working... Instead I hav to give it like
PIVOT (SUM([VALUE]) FOR RowNumber IN ([1], [2])) p

So am curious to know whether the dynamic supply
of rownumber is gonna work... What is with the bracket?
 
Am relatively new to 'pivot' and 'over' Functions..

am tired.. couldn't get it done..

Yes, it will.  Some column names just need to be qualified as such since they could be values whereas SQL knows ONE is a column in that context since there are no single quotes.  I just forgot that in my posting.  Think it is already shown as such in sm394's link, but when you retrieve the dynamic list of values from rowNumber, you concatenate not only with a comma (,) but with square brackets [1].

i.e.
SELECT @sql = IsNull(@sql + ',', '') + '[' + rowNum + ']'
...

And in case it is confusion because I chose to use the word ONE to illustrate my point, I was indicating difference in a text column name versus number column name like 1.

I am posting what i have done... Check the snippet
This works fine for me. And the solution is ok, as i know
that the rownumber would be below 10, always....
But that kind of a prediction is not good So i worked out
as follows.
declare @i as Int
declare @a as nvarchar(max)
declare @maxRowNumber as Int
SELECT  @maxRowNumber =MAX(RowNumber) FROM vw_DurationByDateOrdered
set @a='0'
set @i=1
while(@i<@maxRowNumber)
begin
set @a = @a + ',[' +cast(@i as nvarchar(2))+ ']'
set @i= @i+1
end

But applying this in conjunction with what sm394 says
gives me error..  as follows



declare @SQL as nvarchar(max)
SET @SQL=
'SELECT *
FROM (SELECT  T.DURATION AS [VALUE]
, row_number() OVER (PARTITION BY DATEADD(D, 0, DATEDIFF(D, 0, START_DATETIME))
ORDER BY START_DATETIME) AS RowNumber
, DATEADD(D, 0, DATEDIFF(D, 0, START_DATETIME)) AS [DATE]
FROM [TRAINING-SESSION] AS T JOIN [SCHEDULE-ACT] AS S
ON T.I_SCHEDULE_ACT = S.I_SCHEDULE_ACT
) AS derived
PIVOT (SUM([VALUE])FOR RowNumber IN ('+@a+')) AS p'

EXEC sp_executesql @SQL



So i think am doing something wrong

SELECT *
FROM (SELECT  T.DURATION AS [VALUE]
, row_number() OVER (PARTITION BY DATEADD(D, 0, DATEDIFF(D, 0, START_DATETIME)) ORDER BY START_DATETIME) AS RowNumber
, DATEADD(D, 0, DATEDIFF(D, 0, START_DATETIME)) AS [DATE]
FROM [TRAINING-SESSION] AS T JOIN [SCHEDULE-ACT] AS S
ON T.I_SCHEDULE_ACT = S.I_SCHEDULE_ACT
 
) derived
PIVOT (SUM([VALUE]) FOR RowNumber IN ([1], [2], [3], [4], [5], [6], [7], [8], [9])) p
SELECT  MAX(RowNumber) FROM vw_DurationByDateOrdered

Select allOpen in new window

I will take a look, but what error are you getting in case it is not something obvious?

I will check your solution after after 2 days and vl reply..
Currently am away from office and the work was done at office.
When used the while loop, i got @a as
[0],[1],[2]  and was correct....
But with these values supplied dynamically as
PIVOT (SUM([VALUE])FOR RowNumber IN ('+@a+')), given
me error. But if i use the above statement as
PIVOT (SUM([VALUE])FOR RowNumber IN ([0],[1],[2])),
the answer is what am expecting.. Anyway i dont have
the facility to check this with my p.c currently, and i vl get
back to u soon, once i start working with ur solution

I didnt use the last solution due to limited time..
anyway i used with predicted rownumbers...