需要保持第1列相同，但动态PIVOT第二列

I have a query that looks at data that has been inserted into a TEMP table (not including as there is sensitive information in that table).

我有一个查询,查看已插入TEMP表的数据(不包括该表中的敏感信息)。

I can get the information I need, but I need to organize it better.

我可以获得我需要的信息,但我需要更好地组织它。

The output data displays as

输出数据显示为

trac_id CONTACT_DATE
040 2017-02-20 00:00:00.000
059 2017-03-08 00:00:00.000
001 2017-03-01 00:00:00.000
001 2017-03-08 00:00:00.000
001 2017-03-13 00:00:00.000
001 2017-03-16 00:00:00.000
001 2017-03-16 00:00:00.000
001 2017-03-17 00:00:00.000
001 2017-03-22 00:00:00.000
001 2017-03-23 00:00:00.000
001 2017-03-23 00:00:00.000
001 2017-03-24 00:00:00.000
001 2017-03-27 00:00:00.000
001 2017-03-27 00:00:00.000
001 2017-03-30 00:00:00.000
001 2017-03-31 00:00:00.000
068 2017-02-13 00:00:00.000
067 2017-01-24 00:00:00.000
060 2017-02-08 00:00:00.000
060 2017-03-07 00:00:00.000
011 2017-02-16 00:00:00.000
011 2017-03-01 00:00:00.000
011 2017-03-23 00:00:00.000
011 2017-03-30 00:00:00.000
005 2017-02-16 00:00:00.000
005 2017-03-18 00:00:00.000
005 2017-03-08 00:00:00.000
013 2017-03-08 00:00:00.000
013 2017-03-13 00:00:00.000
013 2017-03-16 00:00:00.000
013 2017-03-16 00:00:00.000
013 2017-03-17 00:00:00.000
013 2017-03-22 00:00:00.000
013 2017-03-23 00:00:00.000
013 2017-03-24 00:00:00.000
013 2017-03-27 00:00:00.000
013 2017-03-27 00:00:00.000
013 2017-03-30 00:00:00.000
013 2017-03-30 00:00:00.000
013 2017-03-31 00:00:00.000
043 2017-02-03 00:00:00.000

Right now I'm using the following query to get this:

现在我正在使用以下查询来获取此信息:

SELECT
    spl.trac_id
    ,pev.CONTACT_DATE


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;

What I need to get is to have 2+n columns. I won't know exactly how many but a quick look shows trac_id 001 with 14 entries. So if that was the max number I would need the columns to be trac_id, mm_1, mm_2, mm_3, mm_4,...,mm_14 without hard coding the number of columns I want to PIVOT into. My problem is that in all of the posts and documentation I've seen about using PIVOT I see the data inserted into a table beforehand and then usually the entire table is PIVOT'ed.

我需要得到的是2 + n列。我不知道究竟有多少,但快速查看显示trac_id 001有14个条目。因此,如果这是最大数量,我需要将列设为trac_id,mm_1,mm_2,mm_3,mm_4,...,mm_14,而无需硬编码我想要PIVOT的列数。我的问题是,在我看到的关于使用PIVOT的所有帖子和文档中,我看到事先插入到表中的数据,然后通常整个表都是PIVOT的。

Is it possible to only PIVOT the second column and if so, how would I do this?

是否可以仅将第二列PIVOT,如果是,我该怎么做?

So, I was able to figure out part of my solution based on a couple posts and your help @Jakub_Ojmucianski. What I've come up with is the following, but it's only halfway there and I'm sure I've made a mistake:

所以,我能够根据几个帖子和你的帮助@Jakub_Ojmucianski找出我的部分解决方案。我想出的是以下内容,但它只有一半,我确信我犯了一个错误:

DECLARE @SQL VARCHAR(MAX)='',@PVT_COL VARCHAR(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 = 
'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'

EXEC (@SQL)

I see the following (Just including the first three new rows):

我看到以下内容(仅包括前三个新行):

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
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   NULL    NULL    NULL    NULL    NULL    NULL
5   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    2/16/2017   3/18/2017   3/8/2017    NULL    NULL    NULL
8   NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    3/8/2017    3/23/2017   3/30/2017

1 个解决方案

#1

you can do something like that:

你可以这样做:

   IF(OBJECT_ID('tempdb..#myTable') IS NOT null)
    DROP TABLE #myTable

IF(OBJECT_ID('tempdb..#pivotColumn') IS NOT null)
    DROP TABLE #pivotColumn

CREATE TABLE #myTable
(
trac_id varchar(3),
CONTACT_DATE datetime
)

INSERT INTO #myTable VALUES
('040', '2017-02-20 00:00:00.000'),
('059' ,'2017-03-08 00:00:00.000'),
('001' ,'2017-03-01 00:00:00.000'),
('001' ,'2017-03-08 00:00:00.000'),
('001' ,'2017-03-13 00:00:00.000'),
('001' ,'2017-03-16 00:00:00.000'),
('001' ,'2017-03-17 00:00:00.000')

SELECT ROW_NUMBER() OVER(ORDER BY CONTACT_DATE) as rowNumber,CONTACT_DATE INTO #pivotColumn FROM(
SELECT DISTINCT CONTACT_DATE  FROM #myTable
) AS source


DECLARE @columns VARCHAR(MAX)='' 
DECLARE @curentRow int = 1;
WHILE @curentRow <= (SELECT MAX(rowNumber) from #pivotColumn)
BEGIN
    SET @columns+= '['+(SELECT Cast(CONTACT_DATE as varchar) FROM #pivotColumn WHERE rowNumber = @curentRow)+'],'
    SET @curentRow += 1;
END

SET @columns = SUBSTRING(@columns,1,LEN(@columns)-1)


DECLARE @code Varchar(MAX) = 
'
SELECT * FROM #myTable
Pivot
(
COUNT(trac_id) FOR CONTACT_DATE IN (
'
+
@columns
+
'
) 
) as p'

EXEC(@code)

But be aware of grouping function in dynamic pivot - you have to decide what you want to do with those data? Sum it, count it?

但要注意动态数据透视中的分组功能 - 您必须决定要对这些数据执行什么操作?总结一下,数吧?

Regards

#1