7045

Columns in Dynamic Pivot don't start over at next key item

Question:

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

Where I want the data to be formatted as:

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

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.

Answer1:

The key is including partition by trac_id in the 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 demo: <a href="http://rextester.com/XDVOTD39040" rel="nofollow">http://rextester.com/XDVOTD39040</a>

returns:

+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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 | +--------------------------------------------------------------------------------------------------------------------------------------------------------------+

and

+---------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+ | 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 | +---------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+ <hr />

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 demo: <a href="http://rextester.com/HDTK5946" rel="nofollow">http://rextester.com/HDTK5946</a>

returns:

+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | 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 | +--------------------------------------------------------------------------------------------------------------------------------------------------------------+

and

+---------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+ | 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 | | | | | | | | | | | | | | +---------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+------------+

Recommend

  • Unable to connect with WPA2 android
  • “Error converting data type nvarchar to int” when executing Stored Procedure and reading return valu
  • How to write an INSTEAD OF INSERT trigger on a multi table view that works with identities?
  • Saving Entity causes duplicate insert into lookup data
  • Full Text not indexing varbinary column (with html)
  • Most concise way to write this query to add hour to a date
  • compare s, t with ş, ţ in SQL Server
  • MVC Identity 2.2.1 - Primary Key (Guid) as UniqueIdentifier instead of nVarChar(128)
  • Sqlserver table Row to column data - pivot table
  • Squash League Results - SQL Query
  • In SQL Server Management Studio, SQL Server fails on hebrew diacritical marks
  • Creating View with dynamic columns by stored procedure
  • Search and Replace a string t-SQL
  • How can I Implement sql search functionality in a Stored procedure (Sql Server 2008)
  • How can I deal with the return of a null value parameter from a stored procedure to C# code?
  • Cannot use Turkish characters with Entity Framework
  • Retrieve List of Cells in Excel Where Column Equals Something
  • Can I create dynamic pivot query from key value table with different data types?
  • SQL Server PIVOT Function
  • how to pivot complex dataframe
  • Can I use SQL to split contents of a table column stored as CSV (comma separated values) into indivi
  • Passing variable into xp_cmdshell
  • How to select all column in XML
  • Need faster concat of columns in sql server table
  • Build Matrix of Comparisons in SQl Server
  • mapping between two ontologies
  • Need code translation from VB to C#
  • Best practice to eliminate magic numbers within a member function
  • RxJava debounce by arbitrary value
  • Eliminate partial duplicate rows from result set
  • D3 get axis values on zoom event
  • Not able to aggregate on nested fields in elasticsearch
  • C: Incompatible pointer type initializing
  • PHPUnit_Framework_TestCase class is not available. Fix… - Makegood , Eclipse
  • PHP - How to update data to MySQL when click a radio button
  • Counter field in MS Access, how to generate?
  • Incrementing object id automatically JS constructor (static method and variable)
  • How to get next/previous record number?
  • Free memory of cv::Mat loaded using FileStorage API
  • unknown Exception android