?
Solved

Change rows to columns specifically in sql 2005

Posted on 2008-11-06
19
Medium Priority
?
581 Views
Last Modified: 2012-05-05
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....            
0
Comment
Question by:urvinayan
  • 10
  • 6
  • 2
  • +1
19 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22894089
you can't do it unless there is a maximum number of distinct values for each date
0
 
LVL 1

Author Comment

by:urvinayan
ID: 22894203
Thats my question...
Is there any possibility...?
0
 
LVL 1

Author Comment

by:urvinayan
ID: 22894233
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 37

Expert Comment

by:momi_sabag
ID: 22894240
pivot will work only if you know in advance which values you will receive
0
 
LVL 1

Author Comment

by:urvinayan
ID: 22894267
Well in the query am selecting the date START_DATETIME
But it doesnt matter that can be avoided for the purpose
0
 
LVL 1

Author Comment

by:urvinayan
ID: 22894300
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22894515
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
 
LVL 13

Expert Comment

by:sm394
ID: 22895036

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
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 800 total points
ID: 22896076
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
 
LVL 1

Author Comment

by:urvinayan
ID: 22901953
Well mwvisa1,that sounds too good.
I will check your solution and get
back to you soon...
0
 
LVL 1

Author Comment

by:urvinayan
ID: 22902156
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
 
LVL 1

Author Comment

by:urvinayan
ID: 22902363
am tired.. couldn't get it done..
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22903413
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22903417
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
 
LVL 1

Author Comment

by:urvinayan
ID: 22903496
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22903578
I will take a look, but what error are you getting in case it is not something obvious?
0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 800 total points
ID: 22903641
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
 
LVL 1

Author Comment

by:urvinayan
ID: 22910473
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
 
LVL 1

Author Closing Comment

by:urvinayan
ID: 31513867
I didnt use the last solution due to limited time..
anyway i used with predicted rownumbers...
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Loops Section Overview
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question