Mutliple rows into columns

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

  • SQL complicated recursive CTE
  • C# : Application doesn't access database in client pc
  • Sorting based on next and previous records in SQL
  • SSIS (ASCII needed): “Code page is 1252 and is required to be 20127”
  • install SQL server setup has encoutered the error on Win 7 :
  • SQL Server Scripts 2012 Project into Team Foundation Server 2012
  • 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
    

    3 Solutions collect form web for “Mutliple rows into columns”

    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
    

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

    Are you trying to Pivot the data?

    Check out the PIVOT keyword.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.