问题:
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!