question

Ok, I find the answer from this link: https://blogs.msdn.microsoft.com/kenobonn/2009/03/22/pivot-on-two-or-more-fields-in-sql-server/ The demo data and codes are: --SQL to create tables and insert data here… DECLARE @Pet Table ( PetID INT , OwnerID INT --FK to fictitious PetOwner table , PetName NVARCHAR(50) , AnimalType NVARCHAR(10) ) DECLARE @PetDetail Table ( PetDetailID INT , PetID INT -- FK to @Pet table , DetailType NVARCHAR(50) , DetailValue NVARCHAR(50) ) INSERT INTO @Pet(PetID, OwnerID, PetName, AnimalType) VALUES (1, 1, 'Sonya', 'Dog') , (2, 2, 'Bessy', 'Cat') , (3, 2, 'Speedy', 'Cat') , (4, 3, 'Nema', 'Cat') , (5, 3, 'Milo', 'Cat') , (6, 3, 'Naala', 'Cat') INSERT INTO @PetDetail(PetDetailID, PetID, DetailType, DetailValue) VALUES (1, 1, 'Breed', 'Norwegian Samoyed') , (2, 1, 'Gender', 'F') , (3, 2, 'Breed', 'Holstein Hybrid') , (4, 2, 'Gender', 'F') , (5, 3, 'Breed', 'DSH') , (6, 3, 'Gender', 'F') , (7, 4, 'Breed', 'Manx') , (8, 4, 'Gender', 'M') , (9, 5, 'Breed', 'DSH') , (10, 5, 'Gender', 'F') , (11, 6, 'Breed', 'Lynx-Point Siamese') , (12, 6, 'Gender', 'F') --SELECT PetID, OwnerID, PetName, AnimalType FROM @Pet ORDER BY PetID --SELECT PetDetailID, PetID, DetailType, DetailValue FROM @PetDetail ORDER BY PetDetailID --Simple Join SQL HERE… SELECT p.OwnerID, p.PetName, p.AnimalType , pd.DetailType, pd.DetailValue ,DENSE_RANK() OVER (PARTITION BY p.OwnerID ORDER BY p.PetName ASC) AS [Pet#] --,RANK() OVER (PARTITION BY p.OwnerID ORDER BY p.PetName ASC) AS [Pet# by RANK] FROM @Pet p INNER JOIN @PetDetail pd ON p.PetID = pd.PetID ORDER BY p.OwnerID, p.PetName, pd.DetailType -- --PIVOT QUERY HERE… SELECT OwnerID , AnimalType , Pet1Name = MAX([PetName1]) , Pet1Gender = MAX([Gender1]) , Pet1Breed = MAX([Breed1]) , Pet2Name = MAX([PetName2]) , Pet2Gender = MAX([Gender2]) , Pet2Breed = MAX([Breed2]) , Pet3Name = MAX([PetName3]) , Pet3Gender = MAX([Gender3]) , Pet3Breed = MAX([Breed3]) FROM ( SELECT p.OwnerID , p.PetName , p.AnimalType , pd.DetailType , pd.DetailValue ,pd.DetailType + CAST(DENSE_RANK() OVER (PARTITION BY p.OwnerID ORDER BY p.PetName ASC) AS NVARCHAR) AS [PetNumber] ,'PetName' + CAST(DENSE_RANK() OVER (PARTITION BY p.OwnerID ORDER BY p.PetName ASC) AS NVARCHAR) AS [PetNamePivot] FROM @Pet p INNER JOIN @PetDetail pd ON p.PetID = pd.PetID ) AS query PIVOT (MAX(DetailValue) FOR PetNumber IN ([Gender1],[Gender2],[Gender3], [Breed1], [Breed2], [Breed3])) AS Pivot1 PIVOT (MAX(PetName) FOR PetNamePivot IN ([PetName1],[PetName2],[PetName3])) AS Pivot2 GROUP BY OwnerID , AnimalType For unknown rows, I need to generate a dynamical sql to set the unknown column names

I tested the above code, it only works for that data provided. Adding more pets will behave weird.

In the same site, I found this one works. I added test data in front of the original code and changed the table variable (which does not work) to temp table: DROP TABLE #SourceTable CREATE TABLE #SourceTable (ModelPath INT, Name VARCHAR(100), Value VARCHAR(100)) INSERT INTO #SourceTable VALUES (1, 'n11','v11') ,(1, 'n12','v12') ,(1, 'n13','v13') ,(2, 'n21','v21') ,(2, 'n22','v22') ,(3, 'n31','v31') ,(3, 'n32','v32') ,(3, 'n33','v33') ,(3, 'n34','v34') ,(3, 'n35','v35') DECLARE @OrderedCols AS NVARCHAR(MAX), @NameCols AS NVARCHAR(MAX), @ValueCols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX); --Build out the column name strings like "ProperyName1, PropertyValue1, PropertyName2, etc etc" SET @OrderedCols = STUFF((SELECT distinct '' + ',MAX(' + QUOTENAME('Name' + CAST(DENSE_RANK() OVER (PARTITION BY ModelPath ORDER BY Name ASC) AS NVARCHAR)) + ') ' + ' AS ProperyName' + CAST(DENSE_RANK() OVER (PARTITION BY ModelPath ORDER BY Name ASC) AS NVARCHAR) + ',MAX(' + QUOTENAME('Value' + CAST(DENSE_RANK() OVER (PARTITION BY ModelPath ORDER BY Name ASC) AS NVARCHAR)) + ')' + ' AS ProperyValue' + CAST(DENSE_RANK() OVER (PARTITION BY ModelPath ORDER BY Name ASC) AS NVARCHAR) FROM #SourceTable c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') --Build just the "Name" Columns for the Pivot SET @NameCols = STUFF((SELECT distinct ',' + QUOTENAME('Name' + CAST(DENSE_RANK() OVER (PARTITION BY ModelPath ORDER BY Name ASC) AS NVARCHAR)) FROM #SourceTable c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') --Build just the "Value" Columns for the Pivot SET @ValueCols = STUFF((SELECT distinct ',' + QUOTENAME('Value' + CAST(DENSE_RANK() OVER (PARTITION BY ModelPath ORDER BY Name ASC) AS NVARCHAR)) FROM #SourceTable c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') --Define the Dynamic SQL Query SET @query = 'SELECT ModelPath , ' + @OrderedCols + ' FROM ( SELECT ModelPath ,Name ,Value ,''Name'' + CAST(DENSE_RANK() OVER (PARTITION BY ModelPath ORDER BY Name ASC) AS NVARCHAR) AS [NamePivot] ,''Value'' + CAST(DENSE_RANK() OVER (PARTITION BY ModelPath ORDER BY Name ASC) AS NVARCHAR) AS [ValuePivot] FROM #SourceTable ) AS query PIVOT (MAX(Name) FOR NamePivot IN (' + @NameCols + ')) AS Pivot1 PIVOT (MAX(Value) FOR ValuePivot IN (' + @ValueCols + ')) AS Pivot2 GROUP BY ModelPath ORDER BY ModelPath' execute(@query)

This is the version that works for the example data of three columns pivot. I have ordered the output according to the Dtime column: DROP TABLE #SourceTable CREATE TABLE #SourceTable (ModelPath INT, Name VARCHAR(100), Value VARCHAR(100), DTime VARCHAR(100) ) INSERT INTO #SourceTable VALUES (1, 'n11','v11','2011') ,(1, 'n12','v12','2009') ,(1, 'n13','v13','2013') ,(2, 'n21','v21','2012') ,(2, 'n22','v22','2014') ,(3, 'n31','v31','2015') ,(3, 'n32','v32','2001') ,(3, 'n33','v33','2012') ,(3, 'n34','v34','2002') ,(3, 'n35','v35','2004') DECLARE @OrderedCols AS NVARCHAR(MAX), @NameCols AS NVARCHAR(MAX), @ValueCols AS NVARCHAR(MAX), @DtimeCols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) --Build out the column name strings like "ProperyName1, PropertyValue1, PropertyName2, etc etc" SET @OrderedCols = STUFF((SELECT distinct '' + ',MAX(' + QUOTENAME('Name' + CAST(DENSE_RANK() OVER (PARTITION BY ModelPath ORDER BY Name ASC) AS NVARCHAR)) + ') ' + ' AS ProperyName' + CAST(DENSE_RANK() OVER (PARTITION BY ModelPath ORDER BY Name ASC) AS NVARCHAR) + ',MAX(' + QUOTENAME('Value' + CAST(DENSE_RANK() OVER (PARTITION BY ModelPath ORDER BY Name ASC) AS NVARCHAR)) + ')' + ' AS ProperyValue' + CAST(DENSE_RANK() OVER (PARTITION BY ModelPath ORDER BY Name ASC) AS NVARCHAR) + ',MAX(' + QUOTENAME('Dtime' + CAST(DENSE_RANK() OVER (PARTITION BY ModelPath ORDER BY Name ASC) AS NVARCHAR)) + ')' + ' AS ProperyDtime' + CAST(DENSE_RANK() OVER (PARTITION BY ModelPath ORDER BY Name ASC) AS NVARCHAR) FROM #SourceTable c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') --Build just the "Name" Columns for the Pivot SET @NameCols = STUFF((SELECT distinct ',' + QUOTENAME('Name' + CAST(DENSE_RANK() OVER (PARTITION BY ModelPath ORDER BY Name ASC) AS NVARCHAR)) FROM #SourceTable c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') --Build just the "Value" Columns for the Pivot SET @ValueCols = STUFF((SELECT distinct ',' + QUOTENAME('Value' + CAST(DENSE_RANK() OVER (PARTITION BY ModelPath ORDER BY Name ASC) AS NVARCHAR)) FROM #SourceTable c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') --Build just the "Dtime" Columns for the Pivot SET @DtimeCols = STUFF((SELECT distinct ',' + QUOTENAME('Dtime' + CAST(DENSE_RANK() OVER (PARTITION BY ModelPath ORDER BY Name ASC) AS NVARCHAR)) FROM #SourceTable c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') --Define the Dynamic SQL Query SET @query = 'SELECT ModelPath , ' + @OrderedCols + ' FROM ( SELECT ModelPath ,Name ,Value ,Dtime ,''Name'' + CAST(DENSE_RANK() OVER (PARTITION BY ModelPath ORDER BY DTIME ASC) AS NVARCHAR) AS [NamePivot] ,''Value'' + CAST(DENSE_RANK() OVER (PARTITION BY ModelPath ORDER BY DTIME ASC) AS NVARCHAR) AS [ValuePivot] ,''Dtime'' + CAST(DENSE_RANK() OVER (PARTITION BY ModelPath ORDER BY DTIME ASC) AS NVARCHAR) AS [DtimePivot] FROM #SourceTable ) AS query PIVOT (MAX(Name) FOR NamePivot IN (' + @NameCols + ')) AS Pivot1 PIVOT (MAX(Value) FOR ValuePivot IN (' + @ValueCols + ')) AS Pivot2 PIVOT (MAX(Dtime) FOR DtimePivot IN (' + @DtimeCols + ')) AS Pivot3 GROUP BY ModelPath ORDER BY ModelPath' execute(@query) --print @query

My solution to the above wrong sequence when pets >= 10 for an owner is : 1) save those columns (select without distinct) to a temp table 2) select distinct from this temp table order by len(Name), Name ASC into another temp table 3) Build the @OrderedCols using the second temp table