pivot - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

pivot

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-09-24 : 17:02:57

Hi,
I would like to write a sql query in sql server 2005 to derive a table called tblResult from tblData as follows:
I think I should use unpivot.

tblData
Notice the dates are fields

surname 26 Dec 27 Dec 28 Dec 02 Jan 04 Jan 05 Jan
Brown 100.2 12.65 43.76 3.54 98.12 56.76
Jackson 32.21 98.34 45.54 2.65 65.11 78.86
Peterson 32.23 65.34 88.22 8.34 12.22 87.55

This is what I would like to end up with

tblResult

Surname Date price1 Price2
Brown 26 Dec 100.2 100.2
Jackson 26 Dec 32.21 32.21
Peterson 26 Dec 32.23 32.23
Brown 27 Dec 12.65 12.65
Jackson 27 Dec 98.34 98.34
Peterson 27 Dec 65.34 65.34
Brown 28 Dec 43.76 43.76
Jackson 28 Dec 45.54 45.54
Peterson 28 Dec 88.22 88.22
.
.
.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-24 : 17:11:10

SELECT surname, theDate, theValue, theValue FROM (
SELECT d.surname, d.theDate, d.theValue, ROW_NUMBER() OVER (PARTITION BY d.surname ORDER BY d.theValue) AS RecID
FROM (
SELECT surname, '26 Dec' AS theDate, [26 Dec] FROM tblData UNION ALL
SELECT surname, '27 Dec', [27 Dec] FROM tblData UNION ALL
SELECT surname, '28 Dec', [28 Dec] FROM tblData UNION ALL
SELECT surname, '02 Jan', [02 Jan] FROM tblData UNION ALL
SELECT surname, '04 Jan', [04 Jan] FROM tblData UNION ALL
SELECT surname, '05 Jan', [05 Jan] FROM tblData
) AS d
) AS e WHERE RecID = 1

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-25 : 04:20:48

How about Normalization?

Madhivanan

Failing to plan is Planning to fail

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-09-25 : 04:21:47

Hi,
I am not sure if this will be a good solution for me because the actual file that I am trying to do the pivot on is from an excel file.
So the excel file has fields such as:
surname 28-Dec 29-Dec 02-Jan 03-Jan ...

Please note the date field goes all lthe way to 14-Sep 2007
So as you can see the date fields are quite alot.

Issues that I am facing:

When importing the excel file data into sql server table, I get the dcolumns which are dates as F2, F3, F4... fields in sql server imported table.
Other issue is:
do I have to write all the date filed columns in the pivot query?

Thanks

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-09-25 : 04:22:37

quote:
Originally posted by madhivanan

How about Normalization?

Madhivanan

Failing to plan is Planning to fail

The source is from an excel file which is described in my previous post.
Thanks

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-09-25 : 06:49:14

Yes, I have been reading documentation but can not figure this out.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-25 : 07:06:22

quote:
Originally posted by arkiboys

I would like to write a sql query in sql server 2005 to derive a table called tblResult from tblData as follows:
I think I should use unpivot.

Excel is not mentioned.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-25 : 07:27:59

quote:
Originally posted by Peso

quote:
Originally posted by arkiboys

I would like to write a sql query in sql server 2005 to derive a table called tblResult from tblData as follows:
I think I should use unpivot.
Excel is not mentioned.

E 12°55'05.25"
N 56°04'39.16"

by previous reply OP means the previous reply made before replying to my question

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources