移调列值成排SQL
问题描述:
我有一个展示的项目负责人(RES_)如下表:移调列值成排SQL
ID NUMBER BK no_ Res
LUC00003 BK001 CLARETH
LUC00003 BK001 MARC
LUC00009 BK001 CLARETH
LUC00009 BK001 MICHAEL
我想在一行中显示RES_而不是列,为了得到每一行对应一个ID,如下图所示:
ID NUMBER BK no_ Res 1 Res2
LUC00003 BK001 CLARETH MARC
LUC00009 BK001 CLARETH MICHAEL
这里下面的查询:
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_]
答
使用条件aggregatio N:
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演示:http://rextester.com/DFCC86645
回报:
+-----------+-------+---------+---------+
| id_number | bk_no | Res1 | Res2 |
+-----------+-------+---------+---------+
| LUC00003 | BK001 | CLARETH | MARC |
| LUC00009 | BK001 | CLARETH | MICHAEL |
+-----------+-------+---------+---------+
动态pivot()
版本:
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;
回报:
+-----------------------------------------------------------------+
| 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 |
+-----------+-------+---------+---------+
,如果你想这个解决方案,为任意数量的RES_价值的工作,你不能动态地添加colums。如果您知道Res_将会有一个或两个值,则可以创建两列,如果未找到任何结果,那么可能为空。然而,你可以将所有结果连接成一个coluimn,例如:http://*.com/questions/194852/concatenate-many-rows-into-a-single-text-string – mortb
你需要“Res 1”和“Res 2”作为附加值PER行,之后您可以使用[PIVOT](https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot) - 函数 – swe
也许这个问题可以帮助你,它的一个可能的重复:http://*.com/questions/41768834/sql-pivot-with-multiple-values – swe