Change rows to columns specifically in sql 2005

Hai,
Take a look at my query result ...
Date Value
d1    1.1
d1     2.7
d1    3.1
d2    4.6
d2    2.2

.... Here d1, d2 are date fields and value is double

My query goes like this


SELECT  T.DURATION AS VALUE ,(DATEADD(D, 0, DATEDIFF(D, 0, START_DATETIME))) AS DATE,START_DATETIME from [TRAINING-SESSION] AS T JOIN [SCHEDULE-ACT] AS S
            ON T.I_SCHEDULE_ACT = S.I_SCHEDULE_ACT
            START_DATETIME between @pistartdate and @pienddate

But The result i want is of the form

d1  1.1  2.7  3.1
d2  4.6  2.2  

This means those values with similar dates are grouped to become
one row...
How could i achieve this ... Thanks In advance... Am a newcomer to
this site.. and am running out of points So am awarding lesser points
eventhough i know its worth more....            
LVL 1
urvinayanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
you can't do it unless there is a maximum number of distinct values for each date
0
urvinayanAuthor Commented:
Thats my question...
Is there any possibility...?
0
urvinayanAuthor Commented:
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

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

momi_sabagCommented:
pivot will work only if you know in advance which values you will receive
0
urvinayanAuthor Commented:
Well in the query am selecting the date START_DATETIME
But it doesnt matter that can be avoided for the purpose
0
urvinayanAuthor Commented:
yea thats what i said as well .
For pivot i dont know date in advance.
I think it may be possible...
But really difficult...
0
Kevin CrossChief Technology OfficerCommented:
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

Open in new window

0
sm394Commented:

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

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_23801801.html
0
Kevin CrossChief Technology OfficerCommented:
urvinayan, sm394 gave an example of putting together dynamic SQL statement which from what I glanced has all the concepts I was meaning in my comment, so that should help you understand what I mean there.  In my original code, I forgot to add the alias for the derived table:

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
) derived
PIVOT (SUM([VALUE]) FOR RowNumber IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)) p


To further help the solution, if you can break this appart and have a VIEW that would work much easier for you.

You could take the whole derived table code and put in a view.

i.e. vw_DurationByDateOrdered
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

Then in your dynamic sql creation, you could --
SELECT MAX(RowNumber)  FROM vw_DurationByDateOrdered;

And then in a loop from 1 - max, create a string of the row number values as shown in sm394's link.

Hope that helps.

Regards,
Kevin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
urvinayanAuthor Commented:
Well mwvisa1,that sounds too good.
I will check your solution and get
back to you soon...
0
urvinayanAuthor Commented:
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..


0
urvinayanAuthor Commented:
am tired.. couldn't get it done..
0
Kevin CrossChief Technology OfficerCommented:
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 + ']'
...
0
Kevin CrossChief Technology OfficerCommented:
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.
0
urvinayanAuthor Commented:
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

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
I will take a look, but what error are you getting in case it is not something obvious?
0
Kevin CrossChief Technology OfficerCommented:
You would probably want to run while loop for <= max AND your column 0 would need to be [0] for same reasons as the other numbers.

Take a look here though as using view makes this simplier and appears you implemented view as suggested.
declare @a as nvarchar(max)
declare @maxRowNumber as Int
 
-- think this will work / but have to group row numbers of same value together to get distinct
SELECT  @a = ISNULL(@a + ',', '[0],') + '[' + cast(RowNumber as varchar(10)) + ']' 
FROM vw_DurationByDateOrdered
GROUP BY RowNumber
ORDER BY RowNumber
 
declare @SQL as nvarchar(max)
SET @SQL=
'SELECT *
FROM vw_DurationByDateOrdered
PIVOT (SUM([VALUE])FOR RowNumber IN ('+@a+')) AS p'
 
EXEC sp_executesql @SQL
-- can ensure your sql is coming out correctly
--PRINT(@SQL)
-- can also just run this way
--EXECUTE(@SQL)

Open in new window

0
urvinayanAuthor Commented:
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

0
urvinayanAuthor Commented:
I didnt use the last solution due to limited time..
anyway i used with predicted rownumbers...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.