我的表如下:
患者表
PatientId Name
1 James
...
访问表
Date PatientID_FK Weight
1/1 1 220
2/1 1 210
...
如何建立返回的查询
PatientId Name Visit1Date Visit1Weight Visit2Date Visit2Weight ...
1 James 1/1 220 2/1 210
2 ...
我们如何以这种方式添加更多列?如何写那个
SELECT
?请帮忙。StackExchange上的一些帖子说,SQL语句无法处理它。真的是这样吗
最佳答案
这种类型的数据转换将需要同时使用pivot
和unpivot
函数来完成。由于您的访问将是未知的,因此您将要使用动态sql。但是首先,我将向您展示如何使用硬编码的值构建查询,以便更轻松地了解流程的工作方式。
首先,您需要对UNPIVOT
和date
列进行weight
,以便将值放在同一列中。这可以使用UNION ALL
查询或unpivot函数完成:
UNPIVOT:
select patientid, name, rn, col, value
from
(
select p.patientid, p.name, convert(char(5), v.date, 110) date,
cast(v.weight as char(5)) weight,
row_number() over(partition by PatientID_FK order by date) rn
from patients p
left join visits v
on p.patientid = v.PatientID_FK
) src
unpivot
(
value
for col in (date, weight)
) unpiv
参见SQL Fiddle with Demo。该查询的结果将date和weight列的值都放入具有多行的单个列中。请注意,我在记录中应用了
row_number()
,因此您将能够知道每次访问使用的值:| PATIENTID | NAME | RN | COL | VALUE |
-------------------------------------------
| 1 | James | 1 | date | 01-01 |
| 1 | James | 1 | weight | 220 |
| 1 | James | 2 | date | 02-01 |
| 1 | James | 2 | weight | 210 |
PIVOT:
下一步是将
PIVOT
函数应用于col
列中的项目,但是首先我们需要更改名称,以便为您提供所需的名称。为此,我会稍微更改
SELECT
语句,以将行号添加到col名称:select patientid, name, 'Visit'+col + cast(rn as varchar(10)) new_col,
value
from ...
这将为您提供新的名称,这些名称是您要用作列的名称:
Visitdate1
Visitweight1
Visitdate2
Visitweight2
要对数据进行
PIVOT
编码,如果您对值进行硬编码,查询将如下所示:select *
from
(
select patientid, name, 'Visit'+col + cast(rn as varchar(10)) new_col,
value
from
(
select p.patientid, p.name, convert(char(5), v.date, 110) date,
cast(v.weight as char(5)) weight,
row_number() over(partition by PatientID_FK order by date) rn
from patients p
left join visits v
on p.patientid = v.PatientID_FK
) src
unpivot
(
value
for col in (date, weight)
) unpiv
) s1
pivot
(
max(value)
for new_col in (Visitdate1,Visitweight1,
Visitdate2,Visitweight2)
) piv
参见SQL Fiddle with Demo。
动态PIVOT:
现在,我已经解释了如何设置它的逻辑,您将要使用动态sql实现相同的过程。您的动态sql版本将是:
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX),
@colsPivot as NVARCHAR(MAX)
select @colsUnpivot = stuff((select ', '+quotename(C.name)
from sys.columns as C
where C.object_id = object_id('visits') and
C.name not in ('PatientID_FK')
for xml path('')), 1, 1, '')
select @colsPivot = STUFF((SELECT ',' + quotename('Visit'+c.name
+ cast(v.rn as varchar(10)))
from
(
select row_number() over(partition by PatientID_FK order by date) rn
from visits
) v
cross apply sys.columns as C
where C.object_id = object_id('visits') and
C.name not in ('PatientID_FK')
group by c.name, v.rn
order by v.rn
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select *
from
(
select patientid, name, ''Visit''+col + cast(rn as varchar(10)) new_col,
value
from
(
select p.patientid, p.name, convert(char(5), v.date, 110) date,
cast(v.weight as char(5)) weight,
row_number() over(partition by PatientID_FK order by date) rn
from patients p
left join visits v
on p.patientid = v.PatientID_FK
) x
unpivot
(
value
for col in ('+ @colsunpivot +')
) u
) x1
pivot
(
max(value)
for new_col in ('+ @colspivot +')
) p'
exec(@query)
参见SQL Fiddle with Demo
这两个版本的结果是:
| PATIENTID | NAME | VISITDATE1 | VISITWEIGHT1 | VISITDATE2 | VISITWEIGHT2 |
-----------------------------------------------------------------------------
| 1 | James | 01-01 | 220 | 02-01 | 210 |