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.
| Author |
Topic |
|
dass05555
Yak Posting Veteran
55 Posts |
 Posted - 2008-10-31 : 04:46:44
|
| Hi guys,I'm having the record set as shown below.----------------------------------------------------order# | id | date | text----------------------------------------------------20202032 1 2008-10-03 15:54:21.000 text 120202032 2 2008-10-03 15:54:02.000 text 220202032 3 2008-10-09 17:39:51.000 text 3I want the same recordset as mentioned below,---------------------------------------------------------------------order# date1 text1 date2 text2 date3 text3 ----------------------------------------------------20202032 2008-10-03 15:54:21.000 text 1 2008-10-03 15:54:02.000 text 2 2008-10-09 17:39:51.000 text 3Your help will be appreciated,Thanks in advance...cool..., |
|
|
PeterNeo
Constraint Violating Yak Guru
357 Posts |
Posted - 2008-10-31 : 04:54:09
|
Read about PIVOT in BOL or do search in the forums"There is only one difference between a dream and an aim. A dream requires soundless sleep to see, whereas an aim requires sleepless efforts to achieve..!!" |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-31 : 04:54:10
|
| [code]SELECT CASE WHEN Seq=1 THEN order# ELSE '' END,MAX(CASE WHEN Seq=1 THEN date ELSE NULL END) AS date1,MAX(CASE WHEN Seq=1 THEN text ELSE NULL END) AS text1,MAX(CASE WHEN Seq=2 THEN date ELSE NULL END) AS date2,...MAX(CASE WHEN Seq=3 THEN text ELSE NULL END) AS text3FROM(SELECT ROW_NUMBER() OVER(PARTITION BY order# ORDER BY id) AS Seq,*FROM Table)tGROUP BY order#[/code] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-31 : 04:55:19
|
| Also if you're using report no need of CASE WHEN for first column (order#). then return as it is and use Hide Duplicates property available in reports. |
|
|
|
dass05555
Yak Posting Veteran
55 Posts |
Posted - 2008-10-31 : 05:02:03
|
| Hi visakh,Some of the order# have id like (4,5,6) the id column will change dynamically for each order. from this i want to take recent 3 day notes for each order.cool..., |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-31 : 05:46:00
|
| 3 day notes? do you mean latest three records as per id value? |
|
|
|
dass05555
Yak Posting Veteran
55 Posts |
Posted - 2008-10-31 : 06:15:58
|
| Ya i want the last 3 day notes..cool..., |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-10-31 : 06:56:23
|
| [code]SELECT CASE WHEN Seq=1 THEN order# ELSE '' END,MAX(CASE WHEN Seq=1 THEN date ELSE NULL END) AS date1,MAX(CASE WHEN Seq=1 THEN text ELSE NULL END) AS text1,MAX(CASE WHEN Seq=2 THEN date ELSE NULL END) AS date2,...MAX(CASE WHEN Seq=3 THEN text ELSE NULL END) AS text3FROM(SELECT ROW_NUMBER() OVER(PARTITION BY order# ORDER BY id DESC) AS Seq,*FROM Table)tGROUP BY order#[/code] |
|
|
|
|
|
|
|
|
Rows into columns