Sql sererver(2008R2)支持问题 [英] Sql sererver (2008R2) pivot question
问题描述
我有一张桌子,我正试图转动。感兴趣的列是
MRN(表示数字序列的nvarchar)
DX(表示代码的nvarchar)
我想转动数据,以便我有一个mrn和mrn的所有dx代码(每mrn最多可以有20个dx代码)。
我的问题是我的结果集返回dx代码的所有空值(据了解,如果源数据中存在MRN,则有1个或更多dx代码与它相关联。)
我缺少什么?
我尝试过:
; cte as
(
SELECT [MRN] as MRNPivot
,MRN
,[DX]
FROM [mydb]。[dbo]。[mytable]
)
S. ELECT * FROM cte
PIVOT
(
min(mrnpivot) for [dx] in (dx1,dx2,dx3,dx4,dx5,
dx6,dx7,dx8,dx9,dx10,
dx11,dx12,dx13,dx14,dx15,
dx16,dx17,dx18,dx19,dx20)
) AS p
我的结果集如下:
MRN dx1 dx2 dx3 ... .dx20
123 NULL NULL NULL ... NULL
124 NULL NULL NULL ... NULL
...
...
数据如下所示:
mrnpivot mrn dx
123 123 a105
123 123 b27
124 124 b27
124 124 1566A
124 124 a106
我想要它看起来像这样:
mrn dx1 dx2 dx3 dx4 ... dx20
123 a105 b27 NULL NULL ... NULL
124 b27 1566A a106 NULL ... NULL
也许是一个支点是不是我想要的?
假设您想要按升序排列DX
值,这样的话应该工作:
WITH cte 作为
(
SELECT
MRN,
DX,
ROW_NUMBER() OVER ( PARTITION BY MRN ORDER BY DX) As RN
FROM
[mydb]。[dbo]。[mytable]
)
SELECT
MRN,
Max( CASE RN WHEN 1 那么 DX END ) As DX1,
Max( CASE RN WHEN 2 那么 DX END )作为 DX2,
Max( CASE RN WHEN 3 那么 DX END )作为 DX3,
Max( CASE RN WHEN 4 那么 DX END )作为 DX4,
Max( CASE RN WHEN 5 那么 DX END )作为 DX5,
Max( CASE RN WHEN 6 那么 DX END )作为 DX6,
Max( CASE RN WHEN 7 那么 DX END )作为 DX7,
Max( CASE RN WHEN 8 那么 DX 结束)作为 DX8,
Max( CASE RN WHEN 9 那么 DX END )作为 DX9,
Max( CASE RN WHEN 10 那么 DX END )作为 DX10,
Max( CASE RN WHEN 11 那么 DX END )作为 DX11,
Max( CASE RN WHEN 12 那么 DX 结束)作为 DX12,
Max( CASE RN WHEN 13 那么 DX END ) As DX13,
Max( CASE RN < span class =code-keyword> WHEN 14 那么 DX END ) As DX14,
Max( CASE RN WHEN 15 那么 DX END )作为 DX15,
Max( CASE RN WHEN 16 那么 DX END )作为 DX16,
Max( CASE RN WHEN 17 那么 DX END )作为 DX17,
Max( CASE RN WHEN 18 那么 DX END )作为 DX18,
Max( CASE RN WHEN 19 那么 DX < span class =code-keyword> END )作为 DX19,
Max( CASE RN WHEN 20 那么 DX END )作为 DX20
FROM
cte
GROUP BY
MRN
;
根据您的样本数据,这将产生:
MRN | DX1 | DX2 | DX3 | DX4 | ...
-----------------------------------------
123 | a105 | b27 | NULL | NULL | ...
124 | 1566A | a106 | b27 | NULL | ...
I have a table that I'm trying to pivot. The columns of interest are
MRN (nvarchar that represents a numerical sequence)
DX (nvarchar that represents a code)
I want to pivot the data so that I have one mrn and all of the dx codes for that mrn (there can be up to 20 dx codes per mrn).
My problem is that my result set returns all null values for the dx codes (it is understood that there's at if an MRN exists in the source data, there are 1 or more dx codes associated with it).
What am I missing?
What I have tried:
;with cte as
(
SELECT [MRN] as MRNPivot
,MRN
,[DX]
FROM [mydb].[dbo].[mytable]
)
SELECT * FROM cte
PIVOT
(
min(mrnpivot) for [dx] in (dx1, dx2, dx3, dx4, dx5,
dx6, dx7, dx8, dx9, dx10,
dx11, dx12, dx13, dx14, dx15,
dx16, dx17, dx18, dx19, dx20)
) AS p
My result set looks like this:
MRN dx1 dx2 dx3.... dx20
123 NULL NULL NULL... NULL
124 NULL NULL NULL... NULL
...
...
The data looks like this:
mrnpivot mrn dx
123 123 a105
123 123 b27
124 124 b27
124 124 1566A
124 124 a106
I want it to look like this:
mrn dx1 dx2 dx3 dx4... dx20
123 a105 b27 NULL NULL... NULL
124 b27 1566A a106 NULL... NULL
Maybe a pivot isn't what I want?
Assuming you want theDX
values in ascending order, something like this should work:
WITH cte As ( SELECT MRN, DX, ROW_NUMBER() OVER (PARTITION BY MRN ORDER BY DX) As RN FROM [mydb].[dbo].[mytable] ) SELECT MRN, Max(CASE RN WHEN 1 THEN DX END) As DX1, Max(CASE RN WHEN 2 THEN DX END) As DX2, Max(CASE RN WHEN 3 THEN DX END) As DX3, Max(CASE RN WHEN 4 THEN DX END) As DX4, Max(CASE RN WHEN 5 THEN DX END) As DX5, Max(CASE RN WHEN 6 THEN DX END) As DX6, Max(CASE RN WHEN 7 THEN DX END) As DX7, Max(CASE RN WHEN 8 THEN DX END) As DX8, Max(CASE RN WHEN 9 THEN DX END) As DX9, Max(CASE RN WHEN 10 THEN DX END) As DX10, Max(CASE RN WHEN 11 THEN DX END) As DX11, Max(CASE RN WHEN 12 THEN DX END) As DX12, Max(CASE RN WHEN 13 THEN DX END) As DX13, Max(CASE RN WHEN 14 THEN DX END) As DX14, Max(CASE RN WHEN 15 THEN DX END) As DX15, Max(CASE RN WHEN 16 THEN DX END) As DX16, Max(CASE RN WHEN 17 THEN DX END) As DX17, Max(CASE RN WHEN 18 THEN DX END) As DX18, Max(CASE RN WHEN 19 THEN DX END) As DX19, Max(CASE RN WHEN 20 THEN DX END) As DX20 FROM cte GROUP BY MRN ;
Given your sample data, this will produce:
MRN | DX1 | DX2 | DX3 | DX4 | ... ----------------------------------------- 123 | a105 | b27 | NULL | NULL | ... 124 | 1566A | a106 | b27 | NULL | ...
这篇关于Sql sererver(2008R2)支持问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!