本文介绍了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中的列不会从下一个关键项开始的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-13 01:57