使用动态列构建SQL查询 [英] Build SQL query with dynamic columns
问题描述
我的表格如下:
患者表
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?请帮忙.
How can we add more columns in this way? How to write that SELECT? Please help.
StackExchange上的一些帖子说，SQL语句无法处理它.真的是这样吗?
Some posts on StackExchange say it is impossible for a SQL statement to handle it. Is it really so?
推荐答案
这种类型的数据转换将需要同时使用pivot和unpivot函数来完成.由于您的访问将是未知的，因此您将要使用动态sql.但是首先，我将向您展示如何使用硬编码的值构建查询，从而使您更容易理解流程的工作方式.
This type of data transformation will need to be done with both a pivot and the unpivot functions. Since your visits will be unknown, then you will want to use dynamic sql. But first, I will show you how to build the query with the values hard-coded so it makes it easier to understand how the process works.
首先，您需要UNPIVOT date和weight列，以便值位于同一列中.可以使用UNION ALL查询或unpivot函数完成此操作:
First, you need to UNPIVOT the date and weight columns so the values are in the same column. This can be done using a UNION ALL query or the unpivot function:
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提琴.该查询的结果将date和weight列的值都放入具有多行的单个列中.请注意，我对记录应用了row_number()，因此您将能够知道每次访问都包含哪些值:
See SQL Fiddle with Demo. The result of this query places the values of both the date and weight column into a single column with multiple rows. Notice that I applied a row_number() to the records so you will be able to tell what values go with each visit:
| 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列中的项目，但是首先我们需要更改名称，以便为您提供所需的名称.
The next step is to apply the PIVOT function to the items in the col column, but first we need to alter the name so it gives you the names that you want.
为此，我稍微修改了SELECT语句，以将行号添加到col名称:
To do that I alter the SELECT statement slightly to add the row number to the col name:
select patientid, name, 'Visit'+col + cast(rn as varchar(10)) new_col,
value
from ...
这将为您提供新的名称，这些名称是您想要用作列的名称:
This will give you the new names which are the names that you want as columns:
Visitdate1
Visitweight1
Visitdate2
Visitweight2
对于PIVOT，如果您对值进行硬编码，则查询的数据将类似于以下内容:
To PIVOT the data your query will look like the following if you hard-code the values:
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提琴.
动态PIVOT:
现在，我已经解释了如何设置它的逻辑，您将要使用动态sql实现相同的过程.您的动态sql版本将是:
Now that I have explained the logic behind how this is set up, you will want to implement this same process using dynamic sql. You dynamic sql version will be:
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提琴
两个版本的结果是:
| PATIENTID | NAME | VISITDATE1 | VISITWEIGHT1 | VISITDATE2 | VISITWEIGHT2 |
-----------------------------------------------------------------------------
| 1 | James | 01-01 | 220 | 02-01 | 210 |
这篇关于使用动态列构建SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
