Need to retrieve data with different inputs

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

General SQL Server Forums

New to SQL Server Programming

Need to retrieve data with different inputs

Author

Topic

arun07siva
Starting Member

3 Posts

Posted - 2011-12-22 : 01:44:15

Hi,
I have a table like this

ID col1 date
1 123 15-Jun-2010
1 456 16-Jun-2010
1 789 15-Jun-2011
1 098 16-Jun-2011

and i need an output like this

Date Col1 Date Col1
15-Jun-2010 123 15-Jun-2011 789
16-Jun-2010 456 16-Jun-2011 098

Basically i need to compare the value of Col1 during a certain period in 2010 and 2011.

arun07siva
Starting Member

3 Posts

Posted - 2011-12-22 : 01:46:16

here ID and date is the primary key. Kindly help me as this will solve a big prob in my application.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-22 : 01:53:46

[code]
SELECT MAX(CASE WHEN Rnk=1 THEN [Date] END) AS [Date1],
SELECT MAX(CASE WHEN Rnk=1 THEN [Col1] END) AS [Col11],
SELECT MAX(CASE WHEN Rnk=2 THEN [Date] END) AS [Date2],
SELECT MAX(CASE WHEN Rnk=2 THEN [Col1] END) AS [Col12],
..
FROM
(
SELECT DENSE_RANK() OVER (PARTITION BY Rn ORDER BY YEAR(date)) AS Rnk,*
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY ID,YEAR(DAte) ORDER BY Date) AS Rn,*
FROM table
)t
)r
GROUP BY Rn
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arun07siva
Starting Member

3 Posts

Posted - 2011-12-22 : 02:27:32

Thanks for the help Visakh. Let me try this and will let you know.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-22 : 03:59:52

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources