Multiple rows into columns

Question

I have a table called work_type and the data looks like:

Desc               L_type      Ch_ID      Mod_Date
Std Process        11000       53901      2012-02-25 19:28:51.000
Not Req            16000       53901      2012-02-26 20:44:47.000
max sess           19000       53901      2012-02-25 19:44:05.000
max sess regist    19000       53901      2012-02-25 19:46:05.000

When L_type has multiple rows(for Ex 19000 in above data) then need most recent one based on Mod_Date

I want output like:

Te_pl      In_pl               Vn_pl         Ch_ID
Not Req    max sess regist     Std process   53901

I wrote a query like this but it is not what i want:

Select Case when L_type = 11000 then Desc end as Vn_pl,
Case when L_type = 16000 then Desc end as Te_pl,
Case when L_type = 11000 then Desc end as In_pl,
Ch_ID
from dbo.Work_type

possible duplicate of [Combine multiple results in a subquery into a single comma-separated value](http://stackoverflow.com/questions/111341/combine-multiple-results-in-a-subquery-into-a-single-comma-separated-value) — dani herrera, Mar 13 '12 at 15:13
@danihp I don't think the request is to concatenate any values together. — Aaron Bertrand, Mar 13 '12 at 15:15

Dibstar · Accepted Answer · 2012-03-13T15:25:47.983

2

If you only ever expect to need three columns then this should do the trick - if not then i would suggest looking dynamic pivoting instead:

;WITH data AS
(
SELECT 
[desc]
,l_type
,ch_id
,mod_date
,DENSE_RANK() OVER (PARTITION BY ch_id,l_type ORDER BY mod_date DESC) AS row
FROM dbo.Work_type
)
,data2 AS
(
SELECT * 
,row_number() OVER (PARTITION BY ch_id ORDER BY mod_date DESC) AS row1
from data
WHERE row = 1
)
SELECT 
MAX(Case when row1 = 1 THEN [desc] END) AS te_pl
,MAX(Case when row1 = 2 THEN [desc] END) AS in_pl
,MAX(Case when row1 = 3 THEN [desc] END) AS vn_pl
,CH_id
FROM data2
GROUP BY CH_ID

edited Mar 13 '12 at 15:25

answered Mar 13 '12 at 15:18

Dibstar

2,334
2
24
38

Excellent This is what I want – peter Mar 13 '12 at 15:36
nice! can you help wtih this please http://stackoverflow.com/questions/9687622/sql-server-exporting-data-from-a-view – Alex Gordon Mar 13 '12 at 16:41

score 0 · Answer 2 · answered Mar 13 '12 at 15:14

0

Here is an XML based way to transpose an entire table: here

answered Mar 13 '12 at 15:14

smcg

3,195
2
30
40

score 0 · Answer 3 · answered Mar 13 '12 at 15:22

0

Are you trying to Pivot the data?

Check out the PIVOT keyword.

answered Mar 13 '12 at 15:22

Matthew Whited

22,160
4
52
69

Multiple rows into columns

3 Answers3