Transpose column value into row SQL

Question

I have the following table showing the people in charge of the project ( Res_):

ID NUMBER    BK no_    Res 
LUC00003     BK001     CLARETH
LUC00003     BK001     MARC
LUC00009     BK001     CLARETH
LUC00009    BK001      MICHAEL

I would like to display the Res_ in row rather then columns, in order to get one row for each ID, like below:

ID NUMBER    BK no_    Res 1     Res2
LUC00003     BK001     CLARETH   MARC
LUC00009     BK001     CLARETH   MICHAEL

The query here below:

SELECT 
  C.[ID NUMBER], 
  CA.[BK No_],
  B.[Res] 
FROM [IDList] C
LEFT JOIN [BK list]  CA ON C.[ID NUMBER] = CA.[ID NUMBER]
LEFT JOIN [Res List] B ON B.[ID NUMBER]= C.[ID NUMBER] AND B.[BK No_]=CA.[BK No_]

You can't add colums dynamically if you want this solution to work for any number of Res_ values. If you know there will be one or two values for Res_ you can create two columns which might be null if no results are found. You can however concat all results into one coluimn like: stackoverflow.com/questions/194852/… — mortb
– mortb, Commented Apr 27, 2017 at 10:37
you need "Res 1" and "Res 2" as additional Values PER row, after that you can use the PIVOT-Function — swe
– swe, Commented Apr 27, 2017 at 10:38
maybe this question helps you, its a possible duplicate: stackoverflow.com/questions/41768834/… — swe
– swe, Commented Apr 27, 2017 at 10:40

SqlZim · Accepted Answer · 2017-04-27 12:23:29Z

Using conditional aggregation:

select 
    id_number
  , bk_no
  , Res1 = max(case when rn = 1 then Res end)
  , Res2 = max(case when rn = 2 then Res end)
from (
  select *
    , rn = row_number() over (partition by id_number, bk_no order by res)
  from t
) sub
group by id_number, bk_no

rextester demo: http://rextester.com/DFCC86645

returns:

+-----------+-------+---------+---------+
| id_number | bk_no |  Res1   |  Res2   |
+-----------+-------+---------+---------+
| LUC00003  | BK001 | CLARETH | MARC    |
| LUC00009  | BK001 | CLARETH | MICHAEL |
+-----------+-------+---------+---------+

dynamic pivot() version:

declare @cols nvarchar(max);
declare @sql  nvarchar(max);

  select @cols = stuff((
    select distinct 
      ',' + quotename('Res'
          +convert(varchar(10),row_number() over (
              partition by id_number, bk_no 
              order by     res 
          ))
          )
      from t 
      for xml path (''), type).value('.','nvarchar(max)')
    ,1,1,'');

select @sql = '
 select id_number, bk_no, ' + @cols + '
  from  (
    select 
        id_number
      , bk_no
      , Res
      , rn=''Res'' + convert(varchar(10),row_number() over (
              partition by id_number, bk_no 
              order by     res 
          ))
      from t
      ) as a
 pivot (max([Res]) for [rn] in (' + @cols + ') ) p';
 select @sql as CodeGenerated;
 exec sp_executesql @sql;

returns:

+-----------------------------------------------------------------+
|                          CodeGenerated                          |
+-----------------------------------------------------------------+
|      select id_number, bk_no, [Res1],[Res2]                     |
|       from  (                                                   |
|         select                                                  |
|             id_number                                           |
|           , bk_no                                               |
|           , Res                                                 |
|           , rn='Res' + convert(varchar(10),row_number() over ( |
|                   partition by id_number, bk_no                 |
|                   order by     res                              |
|               ))                                                |
|           from t                                                |
|           ) as a                                                |
|      pivot (max([Res]) for [rn] in ([Res1],[Res2]) ) p          |
+-----------------------------------------------------------------+

+-----------+-------+---------+---------+
| id_number | bk_no |  Res1   |  Res2   |
+-----------+-------+---------+---------+
| LUC00003  | BK001 | CLARETH | MARC    |
| LUC00009  | BK001 | CLARETH | MICHAEL |
+-----------+-------+---------+---------+

Collectives™ on Stack Overflow

Transpose column value into row SQL

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related