Convert a column values to column names SQL server

问题: I have a table with below schema. Would like to get results as in expected result in SQL server. Pivot does not work as Value column is string. Please suggest efficient way...

问题:

I have a table with below schema. Would like to get results as in expected result in SQL server. Pivot does not work as Value column is string. Please suggest efficient way to achieve this. Thanks!

Table:

 Dim Key(String) , Dim Value (String)
   AB                XY1 
   AB                XY2
   CD                XY3
   CD                XY4

Expected Result

New columns=>  AB      CD
Row values =>  XY1     XY3
               XY2     XY4

回答1:

You want row_number() :

select max(case when key = 'ab' then Value end) as ab, 
       max(case when key = 'cd' then Value end) as cd
from (select t.*, 
             row_number() over (partition by key order by Value) as seq
      from table t
     ) t
group by seq;

However, your pivot version should also work with row_number().

EDIT :

select tt.*
from (select t.*, 
             row_number() over (partition by [key] order by Value) as seq
      from table t
     ) as t pivot 
     ( max(value) for [key] in ([ab], [cd]) 
     ) tt;

回答2:

Self join should work. However you did not provide the complete business requirements for the SQL.

Which result do you expect?

New columns=>  AB      CD
Row values =>  XY1     XY3
               XY2     XY4

Or,

New columns=>  AB      CD
Row values =>  XY1     XY4
               XY2     XY3

Or,

New columns=>  AB      CD
Row values =>  XY1     
               XY2     
                       XY3
                       XY4 

Your SQL would look like this,

select ab.value as "AB", cd.value as "CD"
from (select * from t where key='ab') ab
    ,(select * from t where key='cd') cd

But the above SQL gives you this result,

New columns=>  AB      CD
Row values =>  XY1    XY3 
               XY2    XY3 
               XY1    XY4  
               XY2    XY4          

You should make a little modification the SQL to your needs. Hope it help!

  • 发表于 2019-02-20 18:39
  • 阅读 ( 196 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除