Dynamic Pivot中的列不会从下一个关键项开始 [英] Columns in Dynamic Pivot don't start over at next key item
本文介绍了Dynamic Pivot中的列不会从下一个关键项开始的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
这是已经部分回答的其他问题的延续，但我认为现在已经失去了当前的状态.
This is a continuation from other questions that have been partially answered, but I think the current state is lost now.
首先，我有一个临时表，其中存储了一些用于查询数据库的变量.从那里，我建立了一个临时表来标识满足以下查询中某些条件的访问:
To start, I have a temp table where I have stored some variables that are used to query a database. From there I have built a temp table to identify the visits that meet certain criteria in the following query:
SELECT
spl.trac_id
,CONVERT(DATE,pev.CONTACT_DATE) AS 'Contact'
INTO
#medmtemp
FROM
#SAMHSA_PAT_LIST spl
INNER JOIN dbo.IDENTITY_ID_VIEW iiv
ON iiv.IDENTITY_ID=spl.MRN
LEFT JOIN dbo.PAT_ENC_VIEW pev
ON pev.PAT_ID = iiv.PAT_ID
LEFT JOIN dbo.PAT_ENC_RSN_VISIT_VIEW rsn
ON rsn.PAT_ENC_CSN_ID=pev.PAT_ENC_CSN_ID
WHERE
pev.CONTACT_DATE >= @Start_Date
AND pev.CONTACT_DATE < @End_Date
AND pev.APPT_STATUS_C IN ( 2 , 6 , 8 , 9 )
AND rsn.ENC_REASON_ID = 590;
从那里我创建了一个透视查询，如下所示:
From there I've created a pivot query as follows:
DECLARE @SQL NVARCHAR(MAX)=''
,@PVT_COL NVARCHAR(MAX)='';
SELECT @PVT_COL =@PVT_COL + '[mm_'+CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS VARCHAR(4))+'],'
FROM #medmtemp
SELECT @PVT_COL = LEFT(@PVT_COL,LEN(@PVT_COL)-1)
SELECT @SQL =
N'SELECT * FROM (
SELECT [trac_id], Contact ,''mm_''+CAST(ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS VARCHAR(4)) AS COL_NME
FROM #medmtemp
)AS A
PIVOT
(
MAX(Contact) FOR COL_NME IN (' + @PVT_COL + ')
)PVT'
EXECUTE (@SQL)
不幸的是，我看到的结果是这样的:
Unfortunately the results I'm seeing are this:
trac_id mm_1 mm_2 mm_3 mm_4 mm_5 mm_6 mm_7 mm_8 mm_9 mm_10 mm_11 mm_12 mm_13 mm_14 mm_15 mm_16 mm_17 mm_18 mm_19 mm_20
001 2017-03-01 2017-03-08 2017-03-13 2017-03-16 2017-03-16 2017-03-17 2017-03-22 2017-03-23 2017-03-23 2017-03-24 2017-03-27 2017-03-27 2017-03-30 2017-03-31 NULL NULL NULL NULL NULL NULL
005 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2017-02-16 2017-03-18 2017-03-08 NULL NULL NULL
008 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2017-03-08 2017-03-23 2017-03-30
我想将数据格式化为:
trac_id mm_1 mm_2 mm_3 mm_4 mm_5 mm_6 mm_7 mm_8 mm_9 mm_10 mm_11 mm_12 mm_13 mm_14
1 3/1/2017 3/8/2017 3/13/2017 3/16/2017 3/16/2017 3/17/2017 3/22/2017 3/23/2017 3/23/2017 3/24/2017 3/27/2017 3/27/2017 3/30/2017 3/31/2017
5 2/16/2017 3/18/2017 3/8/2017 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
8 3/8/2017 3/23/2017 3/30/2017 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
如果NULL列为空，那就更好了，但这是一个更好的选择.我希望我能解决这个问题，因为我将在多个查询中使用相同格式的PIVOT.
Even better would be if the NULL columns were blank, but that's more a nice to have. I'm hoping I can get this solved as I'll be using this same format of PIVOT in multiple queries.
推荐答案
键在row_number()
declare @cols nvarchar(max);
declare @sql nvarchar(max);
select @cols = stuff((
select distinct
',' + quotename('mm_'
+ right('0' +convert(nvarchar(10),row_number() over (
partition by trac_id
order by contact
)),2)
)
from t
for xml path (''), type).value('.','nvarchar(max)')
,1,1,'');
select @sql = '
select trac_id, ' + @cols + '
from (
select
trac_id
, contact = convert(char(10),contact,120)
, rn=''mm_''+right(''0'' +convert(nvarchar(10),row_number() over (
partition by trac_id
order by contact
)),2)
from t
) as a
pivot (max([contact]) for [rn] in (' + @cols + ') ) p';
select @sql as CodeGenerated;
exec sp_executesql @sql;
rextester演示: http://rextester.com/XDVOTD39040
rextester demo: http://rextester.com/XDVOTD39040
返回:
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CodeGenerated |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| select trac_id, [mm_01],[mm_02],[mm_03],[mm_04],[mm_05],[mm_06],[mm_07],[mm_08],[mm_09],[mm_10],[mm_11],[mm_12],[mm_13],[mm_14] |
| from ( |
| select |
| trac_id |
| , contact = convert(char(10),contact,120) |
| , rn='mm_'+right('0' +convert(nvarchar(10),row_number() over ( |
| partition by trac_id |
| order by contact |
| )),2) |
| from t |
| ) as a |
| pivot (max([contact]) for [rn] in ([mm_01],[mm_02],[mm_03],[mm_04],[mm_05],[mm_06],[mm_07],[mm_08],[mm_09],[mm_10],[mm_11],[mm_12],[mm_13],[mm_14]) ) p |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
和
+---------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
| trac_id | mm_01 | mm_02 | mm_03 | mm_04 | mm_05 | mm_06 | mm_07 | mm_08 | mm_09 | mm_10 | mm_11 | mm_12 | mm_13 | mm_14 |
+---------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
| 001 | 2017-03-01 | 2017-03-08 | 2017-03-13 | 2017-03-16 | 2017-03-16 | 2017-03-17 | 2017-03-22 | 2017-03-23 | 2017-03-23 | 2017-03-24 | 2017-03-27 | 2017-03-27 | 2017-03-30 | 2017-03-31 |
| 005 | 2017-02-16 | 2017-03-08 | 2017-03-18 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 011 | 2017-02-16 | 2017-03-01 | 2017-03-23 | 2017-03-30 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 013 | 2017-03-08 | 2017-03-13 | 2017-03-16 | 2017-03-16 | 2017-03-17 | 2017-03-22 | 2017-03-23 | 2017-03-24 | 2017-03-27 | 2017-03-27 | 2017-03-30 | 2017-03-30 | 2017-03-31 | NULL |
| 040 | 2017-02-20 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 043 | 2017-02-03 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 059 | 2017-03-08 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 060 | 2017-02-08 | 2017-03-07 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 067 | 2017-01-24 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 068 | 2017-02-13 | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+---------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
要将null值转换为空字符串:
To convert null values to empty strings:
declare @cols nvarchar(max);
declare @select_cols nvarchar(max);
declare @sql nvarchar(max);
select @cols = stuff((
select distinct
',' + quotename('mm_'
+ right('0' +convert(nvarchar(10),row_number() over (
partition by trac_id
order by contact
)),2)
)
from t
for xml path (''), type).value('.','nvarchar(max)')
,1,1,'');
select @select_cols = (
select distinct
char(10)+' , ' + quotename('mm_'
+ right('0' +convert(nvarchar(10),row_number() over (
partition by trac_id
order by contact
)),2)
) +' = isnull('+
quotename('mm_'
+ right('0' +convert(nvarchar(10),row_number() over (
partition by trac_id
order by contact
)),2)
)+','''')'
from t
for xml path (''), type).value('.','nvarchar(max)')
select @sql = '
select trac_id' + @select_cols + '
from (
select
trac_id
, contact = convert(char(10),contact,120)
, rn=''mm_''+right(''0'' +convert(nvarchar(10),row_number() over (
partition by trac_id
order by contact
)),2)
from t
) as a
pivot (max([contact]) for [rn] in (' + @cols + ') ) p';
select @sql as CodeGenerated;
exec sp_executesql @sql;
rextester演示: http://rextester.com/HDTK5946
rextester demo: http://rextester.com/HDTK5946
返回:
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CodeGenerated |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| select trac_id |
| , [mm_01] = isnull([mm_01],'') |
| , [mm_02] = isnull([mm_02],'') |
| , [mm_03] = isnull([mm_03],'') |
| , [mm_04] = isnull([mm_04],'') |
| , [mm_05] = isnull([mm_05],'') |
| , [mm_06] = isnull([mm_06],'') |
| , [mm_07] = isnull([mm_07],'') |
| , [mm_08] = isnull([mm_08],'') |
| , [mm_09] = isnull([mm_09],'') |
| , [mm_10] = isnull([mm_10],'') |
| , [mm_11] = isnull([mm_11],'') |
| , [mm_12] = isnull([mm_12],'') |
| , [mm_13] = isnull([mm_13],'') |
| , [mm_14] = isnull([mm_14],'') |
| from ( |
| select |
| trac_id |
| , contact = convert(char(10),contact,120) |
| , rn='mm_'+right('0' +convert(nvarchar(10),row_number() over ( |
| partition by trac_id |
| order by contact |
| )),2) |
| from t |
| ) as a |
| pivot (max([contact]) for [rn] in ([mm_01],[mm_02],[mm_03],[mm_04],[mm_05],[mm_06],[mm_07],[mm_08],[mm_09],[mm_10],[mm_11],[mm_12],[mm_13],[mm_14]) ) p |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
和
+---------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
| trac_id | mm_01 | mm_02 | mm_03 | mm_04 | mm_05 | mm_06 | mm_07 | mm_08 | mm_09 | mm_10 | mm_11 | mm_12 | mm_13 | mm_14 |
+---------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
| 001 | 2017-03-01 | 2017-03-08 | 2017-03-13 | 2017-03-16 | 2017-03-16 | 2017-03-17 | 2017-03-22 | 2017-03-23 | 2017-03-23 | 2017-03-24 | 2017-03-27 | 2017-03-27 | 2017-03-30 | 2017-03-31 |
| 005 | 2017-02-16 | 2017-03-08 | 2017-03-18 | | | | | | | | | | | |
| 011 | 2017-02-16 | 2017-03-01 | 2017-03-23 | 2017-03-30 | | | | | | | | | | |
| 013 | 2017-03-08 | 2017-03-13 | 2017-03-16 | 2017-03-16 | 2017-03-17 | 2017-03-22 | 2017-03-23 | 2017-03-24 | 2017-03-27 | 2017-03-27 | 2017-03-30 | 2017-03-30 | 2017-03-31 | |
| 040 | 2017-02-20 | | | | | | | | | | | | | |
| 043 | 2017-02-03 | | | | | | | | | | | | | |
| 059 | 2017-03-08 | | | | | | | | | | | | | |
| 060 | 2017-02-08 | 2017-03-07 | | | | | | | | | | | | |
| 067 | 2017-01-24 | | | | | | | | | | | | | |
| 068 | 2017-02-13 | | | | | | | | | | | | | |
+---------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+
这篇关于Dynamic Pivot中的列不会从下一个关键项开始的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
