Show data from same table in different column but in same row in MSSQL

Question

Let I have tables as enter image description here

What I want to do is to select data like

OUTPUT{ID,FirstName,LastName,SkillONE,SkillOneExp,SkillTwo,SkillTwoExp,SkillThree,SkillThreeExp}

here SkillOne will show most experienced skill (like java, C++), SkillOneExp is the year of exp of that skill and so on. Will show only 3 like this. For each employee there will be only one row.

I tried as

select 
e.ID
,e.FirstName
,e.LastName
,es.experience
,CAST
(
    CASE
        WHEN tf.rownumber = 1
        THEN tf.skill_description 
    END

    AS varchar(50)
)as SKILLONE
,CAST
(
    CASE
        WHEN tf.rownumber = 1
        THEN tf.experience 
    END

    AS varchar(50)
)as SKILLONE_EXP
,CAST
(
    CASE
        WHEN tf.rownumber = 2
        THEN tf.skill_description 
    END

    AS varchar(50)
)as SKILTWO
,CAST
(
    CASE
        WHEN tf.rownumber = 2
        THEN tf.experience 
    END

    AS varchar(50)
)as SKILLTWO_EXP
from
Employee e
JOIN
EmployeeSkill es
on es.ID=e.ID
JOIN 
skills s
on s.skill_ID=es.skill_ID

JOIN
(
 select * from 
 (  
    (select 
        ROW_NUMBER() OVER (ORDER BY es2.experience ASC) AS rownumber,
        es2.ID
        ,es2.skill_ID
        ,es2.experience
        ,s2.skill_description
        from
        EmployeeSkill es2
        JOIN
        skills s2
        on
        s2.skill_ID=es2.skill_ID

    ) 
) as tf2 
)as tf 
on tf.id= e.id

By using this I am not getting proper result. I am using MS SQL 2012.

Why don't you do this pivoting in the client application? Presumably it has to loop through the data anyway, it is going to be much more efficient to have it decide at display time whether to put the next value on the same row or the next row... — Aaron Bertrand, Nov 21 '13 at 15:55
you can use the sql server pivot: http://stackoverflow.com/questions/20075279/sql-same-column-different-rows-to-same-row-different-columns/20075723#20075723 — Brett Schneider, Nov 21 '13 at 16:00

score 0 · Answer 1 · answered Nov 21 '13 at 21:32

SELECT
  emp.ID,
  emp.FirstName,
  emp.LastName,
  MIN(CASE i WHEN 1 THEN ski.skill_description END) AS SkillOne     ,
  MIN(CASE i WHEN 1 THEN ski.experience        END) AS SkillOneExp  ,
  MIN(CASE i WHEN 2 THEN ski.skill_description END) AS SkillTwo     ,
  MIN(CASE i WHEN 2 THEN ski.experience        END) AS SkillTwoExp  ,
  MIN(CASE i WHEN 3 THEN ski.skill_description END) AS SkillThree   ,
  MIN(CASE i WHEN 3 THEN ski.experience        END) AS SkillThreeExp
FROM
  Employee AS emp
CROSS APPLY (
  SELECT TOP 3
    ROW_NUMBER() OVER(ORDER BY experience DESC) i,
    CAST(skill_description AS varchar(50),
    CAST(experience        AS varchar(50)
  FROM EmployeeSkill t1
  INNER JOIN skills t2
    ON (t1.skill_ID = t2.skill_ID)
  ORDER BY experience DESC
) AS ski
GROUP BY
  emp.ID,
  emp.FirstName,
  emp.LastName

Thanks a lot @anon for your query, Sorry to say that this query has error ( after CAST need ")" ) and does not work properly But I got idea to solve this problem from your query — razibdeb, Nov 22 '13 at 07:08

score 0 · Accepted Answer · answered Nov 22 '13 at 07:13

by editing the query by @anon I solved my problem. thanks a lot to him.

SELECT
emp.ID,
emp.FirstName,
emp.LastName,
MIN(CASE i WHEN 1 THEN ski.skill_description END) AS SkillOne     ,
MIN(CASE i WHEN 1 THEN ski.experience        END) AS SkillOneExp  ,
MIN(CASE i WHEN 2 THEN ski.skill_description END) AS SkillTwo     ,
MIN(CASE i WHEN 2 THEN ski.experience        END) AS SkillTwoExp  ,
MIN(CASE i WHEN 3 THEN ski.skill_description END) AS SkillThree   ,
MIN(CASE i WHEN 3 THEN ski.experience        END) AS SkillThreeExp
FROM
Employee AS emp
CROSS APPLY (
  SELECT TOP 3
  ROW_NUMBER() OVER(ORDER BY experience DESC) i,
  CAST(skill_description AS varchar(50) ) AS skill_description,
  CAST(experience        AS varchar(50) ) AS experience
FROM EmployeeSkill t1
INNER JOIN skills t2
ON (t1.skill_ID = t2.skill_ID) AND t1.ID=emp.ID
ORDER BY experience DESC
) AS ski
GROUP BY
emp.ID,
emp.FirstName,
emp.LastName

Show data from same table in different column but in same row in MSSQL

2 Answers2