locked
PIVOT QUERY Issue RRS feed

  • Question

  • User1623409651 posted

    Hi all

    I have below data 

    Facility EnrollmentID ColumnNumber
    CR102 A 1
    CR102 B 2
    CR102 C 3
    CR103 D 1
    CR103 E 2

    Required Result

    Facility Column1 Column2 Column3
    CR102 A B C
    CR103 D E

    May i use the pivot for this.

    Thanks

    Thursday, March 1, 2018 5:20 AM

Answers

  • User726159118 posted

    Hi Rameez,

    Refer below updated code.

    
    CREATE TABLE #testDataWithPartitoin ( Facility  varchar(10), EnrollmentID varchar(10), ColumnNumber varchar(15),PartID int)
    
    
    CREATE TABLE #testData ( Facility  varchar(10), EnrollmentID varchar(15), ColumnNumber int)
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102','16FBSM013',1) 
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102','CSC17S001',1) 
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102','CSC17S002',1) 
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102','CSC17S007',1) 
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102','CSC17S009',1) 
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102','CSC17S011',1) 
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102','15FBSM014',2) 
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102','15FBSM035',2) 
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102','15FBSM066',2) 
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102','15FBSM070',2) 
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102','CSC17F016',2) 
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102','CSC17F017',2) 
    
    
    
    Insert into #testDataWithPartitoin (Facility,EnrollmentID,ColumnNumber,PartID)
    SELECT tt.Facility,tt.EnrollmentID, 'Column_'+ Convert(varchar(10),tt.ColumnNumber) , RANK() over(partition by TT.ColumnNumber order by Ltrim(Rtrim(EnrollmentID)) asc) AS TEMPID FROM #testData TT
    
    
    --SELECT * FROM #testDataWithPartitoin
    
    
    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)
    
    
    
    
    
    
    --Get distinct values of the PIVOT Column 
    SELECT @ColumnName= ISNULL( Convert(varchar(10), @ColumnName) + ',','') 
           + QUOTENAME(ColumnNumber)
    FROM (SELECT DISTINCT ColumnNumber FROM #testDataWithPartitoin) AS Courses
    
    
    SET   @DynamicPivotQuery = N'SELECT Facility, ' +   @ColumnName + '
        FROM #testDataWithPartitoin
        PIVOT( MAX(EnrollmentID) 
              FOR ColumnNumber IN (' + @ColumnName + ')) AS P';
    
    
    
    
    
    --Execute the Dynamic Pivot Query
    EXEC sp_executesql @DynamicPivotQuery
    
    Drop Table #testData
    DROP Table #testDataWithPartitoin

    Mark As Answer If it is helpful.

    Regards,
    Omkar.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 1, 2018 9:46 AM
  • User77042963 posted
     
    
    CREATE TABLE #testData ( Facility varchar(10), SubjectName varchar(50), EnrollmentID varchar(15), ColumnNumber int)
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','16FBSM013',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17S001',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17S002',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17S007',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17S009',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17S011',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','B','15FBSM014',2) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','B','15FBSM035',2) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','B','15FBSM066',2) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','B','15FBSM070',2) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','B','CSC17F016',2) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','B','CSC17F017',2) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','15FBSM014',3) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','15FBSM015',3) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','15FBSM016',3) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','15FBSM017',3) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17F018',3) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17F019',3)
    
    --;with mycte as (
    -- SELECT * , row_number() Over(Partition by Facility,SubjectName, ColumnNumber Order by EnrollmentID ) rn 
    -- from #testData)
    -- Select Facility   
    -- ,Max(Case when SubjectName+ Cast(ColumnNumber as varchar(2))='A'+'1' then EnrollmentID End ) 'Column_1_A'
    -- ,Max(Case when SubjectName+ Cast(ColumnNumber as varchar(2))='B'+'2' then EnrollmentID End ) 'Column_2_B'
    -- ,Max(Case when SubjectName+ Cast(ColumnNumber as varchar(2))='A'+'3' then EnrollmentID End ) 'Column_3_A'
    --from mycte
    --Group by Facility, rn
    
     
    
    
     --dynamic sql
    Declare @sqlCase as NVarchar(4000) =null
    Declare @sql as NVarchar(4000)=null
     
      
    declare @ColumnHeaders VARCHAR(MAX), @ColumnHeaders1 VARCHAR(MAX);
    set @ColumnHeaders = STUFF( (SELECT  ',' 
    + 'Max(CASE WHEN SubjectName+ Cast(ColumnNumber as varchar(2))=' + quotename(SubjectName+Cast(ColumnNumber as varchar(2)),'''') + ' THEN EnrollmentID else null end ) as ' + quotename('Column_'+Cast(ColumnNumber as varchar(2))+'_'+SubjectName,'''')  + char(10)+char(13)
                                 FROM #testData
    							 group by SubjectName, ColumnNumber  
                                 order by ColumnNumber,SubjectName
                                 FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
    							 ;
     --print @ColumnHeaders
     
     Set @sql  =' Select Facility,'
     +   @ColumnHeaders + ' from (
    Select * , row_number() Over(Partition by Facility,SubjectName, ColumnNumber Order by EnrollmentID ) rn   from #testData) t
      Group by Facility, rn ';
         
    --print @sql
    EXEC(@SQL)
    
    
     
    drop table #testData

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 5, 2018 7:07 PM

All replies

  • User516094431 posted

    Required solution:

    CREATE TABLE #tTable 
    (
      Facility VARCHAR(10), 
      EnrollmentID  VARCHAR(1), 
      ColumnNumber int
    );
    
    INSERT INTO #tTable
    (
      Facility, 
      EnrollmentID, ColumnNumber
    )
    VALUES
        ('CR102', 'A', 1),
        ('CR102', 'B', 2),
        ('CR102', 'C', 3),
        ('CR103', 'D', 1),
        ('CR103', 'E', 2)
        
    
    
    select *
    from 
    (
      select Facility, EnrollmentID, ColumnNumber
      from #tTable
    ) src
    pivot
    (
      MAX(EnrollmentID)
      for ColumnNumber in ([1], [2], [3])
    ) piv;

    Thursday, March 1, 2018 5:45 AM
  • User1623409651 posted

    Thanks Shoaib for your reply,

    Basically i have data with Column Number with facility like below

    ('CR102', 'A', 1),
    ('CR102', 'B', 2),
    ('CR102', 'C', 2),
    ('CR102', 'D', 2),
    ('CR102', 'C', 3),
    ('CR102', 'C', 3),
    ('CR102', 'C', 3),
    ('CR103', 'D', 1),
    ('CR103', 'E', 2)

    in One Column i have number of rows . can you suggest the way how i can return the data column wise

    faciltiy 1 2 3
    CR102 A E I
    CR102 B F J
    CR102 C G K
    CR102 D H L
    CR103 A D G
    CR103 B E H
    CR103 C F I

    Basically Column number varies against Facility some facility contains 3 column and some Facility contains 4 column

    like this one

             || Col 1 || Col 2 || Col 3
    ____________________________________    
    Facilty1 || 10    ||  20   || 30
    Facilty1 || 1     ||  2    || 3 

    Thanks

    Thursday, March 1, 2018 6:04 AM
  • User726159118 posted

    Hi Rameez,

    Refer below query that may help you. I have created dynamic columns using Pivot.

    CREATE TABLE #testData ( Facility  varchar(10), EnrollmentID varchar(10), ColumnNumber int)
    
     
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102' ,	'A' ,	1)
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102' ,	'B' ,	2)
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102' ,	'C' ,	3)
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR103' ,	'D' ,	1)
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR103' ,	'E', 	2)
    
    --SELECT * FROM #testData
    
    
    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)
    
    
    
    --Get distinct values of the PIVOT Column 
    SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
           + QUOTENAME(ColumnNumber)
    FROM (SELECT DISTINCT ColumnNumber FROM #testData) AS Courses
    
    
    SET @DynamicPivotQuery = 
      N'select *
    from 
    (
      select Facility, EnrollmentID,ColumnNumber
      from #testData
    ) src
    pivot
    (
      SUM(EnrollmentID)
      for [ColumnNumber] in (' + @ColumnName + ')
    ) piv;'
    
    
    
    --Execute the Dynamic Pivot Query
    EXEC sp_executesql @DynamicPivotQuery
    
    Drop Table #testData


    Mark As Answer If it is helpful.

    Regards,
    Omkar.

    Thursday, March 1, 2018 7:06 AM
  • User1623409651 posted

    Thanks for reply Omkar,

    What about if column have more than two enrollment IDs.

    Thanks

    Thursday, March 1, 2018 7:20 AM
  • User726159118 posted

    Hi Rameez,

    I have updated the code check it is helpful.

    
    CREATE TABLE #testData ( Facility  varchar(10), EnrollmentID varchar(10), ColumnNumber int)
    
     
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102' ,	'A' ,	1)
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102' ,	'B' ,	2)
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102' ,	'C' ,	3)
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR103' ,	'D' ,	1)
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR103' ,	'E', 	2)
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR103' ,	'A' ,	3)
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR103' ,	'B', 	4)
    
    --SELECT * FROM #testData
    
    
    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)
    
    
    
    --Get distinct values of the PIVOT Column 
    SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
           + QUOTENAME(ColumnNumber)
    FROM (SELECT DISTINCT ColumnNumber FROM #testData) AS Courses
    
    
    SET @DynamicPivotQuery = 
      N'select *
    from 
    (
      select Facility, EnrollmentID,ColumnNumber
      from #testData
    ) src
    pivot
    (
      max(EnrollmentID)
      for [ColumnNumber] in (' + @ColumnName + ')
    ) piv;'
    
    
    
    --Execute the Dynamic Pivot Query
    EXEC sp_executesql @DynamicPivotQuery
    
    Drop Table #testData

    Mark As Answer If it is helpful.


    Regards,
    Omkar.

    Thursday, March 1, 2018 7:33 AM
  • User1623409651 posted

    Thanks for your reply Omkar,

    I have multiple Students data against  column Number of Facility like the below

    Facility EnrollementID Column Number
    CR102 16FBSM013 1
    CR102 CSC17S001 1
    CR102 CSC17S002 1
    CR102 CSC17S007 1
    CR102 CSC17S009 1
    CR102 CSC17S011 1
    CR102 15FBSM014 2
    CR102 15FBSM035 2
    CR102 15FBSM066 2
    CR102 15FBSM070 2
    CR102 CSC17F016 2
    CR102 CSC17F017 2

    Required Output

    Facility Column1 Column2
    CR102 16FBSM013 15FBSM014
    CR102 CSC17S001 15FBSM035
    CR102 CSC17S002 15FBSM066
    CR102 CSC17S007 15FBSM070
    CR102 CSC17S009 CSC17F016
    CR102 CSC17S011 CSC17F017

    i think PIVOT is not enough for this

    Thanks

    Thursday, March 1, 2018 7:49 AM
  • User726159118 posted

    Hi Rameez,

    Refer below updated code.

    
    CREATE TABLE #testDataWithPartitoin ( Facility  varchar(10), EnrollmentID varchar(10), ColumnNumber varchar(15),PartID int)
    
    
    CREATE TABLE #testData ( Facility  varchar(10), EnrollmentID varchar(15), ColumnNumber int)
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102','16FBSM013',1) 
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102','CSC17S001',1) 
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102','CSC17S002',1) 
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102','CSC17S007',1) 
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102','CSC17S009',1) 
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102','CSC17S011',1) 
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102','15FBSM014',2) 
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102','15FBSM035',2) 
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102','15FBSM066',2) 
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102','15FBSM070',2) 
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102','CSC17F016',2) 
    Insert into #testData (Facility,EnrollmentID, ColumnNumber) Values('CR102','CSC17F017',2) 
    
    
    
    Insert into #testDataWithPartitoin (Facility,EnrollmentID,ColumnNumber,PartID)
    SELECT tt.Facility,tt.EnrollmentID, 'Column_'+ Convert(varchar(10),tt.ColumnNumber) , RANK() over(partition by TT.ColumnNumber order by Ltrim(Rtrim(EnrollmentID)) asc) AS TEMPID FROM #testData TT
    
    
    --SELECT * FROM #testDataWithPartitoin
    
    
    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)
    
    
    
    
    
    
    --Get distinct values of the PIVOT Column 
    SELECT @ColumnName= ISNULL( Convert(varchar(10), @ColumnName) + ',','') 
           + QUOTENAME(ColumnNumber)
    FROM (SELECT DISTINCT ColumnNumber FROM #testDataWithPartitoin) AS Courses
    
    
    SET   @DynamicPivotQuery = N'SELECT Facility, ' +   @ColumnName + '
        FROM #testDataWithPartitoin
        PIVOT( MAX(EnrollmentID) 
              FOR ColumnNumber IN (' + @ColumnName + ')) AS P';
    
    
    
    
    
    --Execute the Dynamic Pivot Query
    EXEC sp_executesql @DynamicPivotQuery
    
    Drop Table #testData
    DROP Table #testDataWithPartitoin

    Mark As Answer If it is helpful.

    Regards,
    Omkar.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, March 1, 2018 9:46 AM
  • User1623409651 posted

    Thank you very much OmKar for your frequent reply.

    Your solution helped allot. i have last and final question when i add the  one more column to table . it  leaves empty column rows. secondly iam unable to pass the where clause in dynamic pivot query .  please check the below sample. May we give the Subject Name  in the column heading like

    Column_1

    A

    Column_2

    B

    Column3

    A

    CREATE TABLE #testDataWithPartitoin ( Facility varchar(50), SubjectName varchar(50), EnrollmentID varchar(50), ColumnNumber varchar(15),PartID int)
    
    
    CREATE TABLE #testData ( Facility varchar(10), SubjectName varchar(50), EnrollmentID varchar(15), ColumnNumber int)
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','16FBSM013',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17S001',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17S002',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17S007',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17S009',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17S011',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','B','15FBSM014',2) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','B','15FBSM035',2) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','B','15FBSM066',2) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','B','15FBSM070',2) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','B','CSC17F016',2) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','B','CSC17F017',2) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','15FBSM014',3) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','15FBSM015',3) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','15FBSM016',3) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','15FBSM017',3) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17F018',3) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17F019',3)
    
    -- SELECT * from #testData
    
    Insert into #testDataWithPartitoin (Facility,SubjectName,EnrollmentID,ColumnNumber,PartID)
    SELECT tt.Facility, tt.SubjectName ,tt.EnrollmentID, 'Column_'+ Convert(varchar(10),tt.ColumnNumber) , RANK() over(partition by TT.ColumnNumber order by Ltrim(Rtrim(EnrollmentID)) asc) AS TEMPID FROM #testData TT
    
    
    --SELECT * FROM #testDataWithPartitoin
    
    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)
    --Get distinct values of the PIVOT Column 
    SELECT @ColumnName= ISNULL( Convert(varchar(100), @ColumnName) + ',','') 
    + QUOTENAME(ColumnNumber)
    FROM (SELECT DISTINCT ColumnNumber FROM #testDataWithPartitoin) AS Courses
    
    
    SET @DynamicPivotQuery = N'SELECT Facility, ' + @ColumnName + '
    FROM #testDataWithPartitoin
    PIVOT(MAX(EnrollmentID) 
    FOR ColumnNumber IN (' + @ColumnName + ')) AS P';
    
    --Execute the Dynamic Pivot Query
    EXEC sp_executesql @DynamicPivotQuery
    
    Drop Table #testData
    DROP Table #testDataWithPartitoin

    Thanks

    Thursday, March 1, 2018 12:18 PM
  • User726159118 posted

    Hi Rameez,

    this is little bit tricky but i have try to set subject name in header. refer below code.

    CREATE TABLE #testDataWithPartitoin ( Facility varchar(50), SubjectName varchar(50), EnrollmentID varchar(50), ColumnNumber varchar(15),PartID int)
    
    CREATE TABLE #testDisplayDataColumnNames ( ColumnNumber varchar(50), DisplayName varchar(50))
    CREATE TABLE #testData ( Facility varchar(10), SubjectName varchar(50), EnrollmentID varchar(15), ColumnNumber int)
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','16FBSM013',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17S001',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17S002',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17S007',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17S009',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17S011',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','B','15FBSM014',2) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','B','15FBSM035',2) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','B','15FBSM066',2) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','B','15FBSM070',2) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','B','CSC17F016',2) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','B','CSC17F017',2) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','15FBSM014',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','15FBSM015',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','15FBSM016',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','15FBSM017',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17F018',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17F019',1)
    
    -- SELECT * from #testData
    
    Insert into #testDataWithPartitoin (Facility,EnrollmentID,ColumnNumber,PartID)
    SELECT tt.Facility ,tt.EnrollmentID ,'Column_'+ Convert(varchar(10),tt.ColumnNumber)  ,  RANK() over(partition by TT.ColumnNumber order by Ltrim(Rtrim(TT.EnrollmentID)) asc) AS TEMPID FROM #testData TT
    
    Insert into #testDisplayDataColumnNames (ColumnNumber, DisplayName)
    SELECT 'Column_'+ Convert(varchar(10),tt.ColumnNumber) , '[Column_'+ Convert(varchar(10),tt.ColumnNumber)  + ' '+ tt.SubjectName+']'  FROM #testData TT
    
    
    
    
    
    --SELECT * FROM #testDisplayDataColumnNames
    
    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)
    
    DECLARE @DynaColumnName AS NVARCHAR(MAX)
    --Get distinct values of the PIVOT Column 
    --SELECT @ColumnName= ISNULL( Convert(varchar(max), @ColumnName) + ',','') 
    --+ QUOTENAME(ColumnNumber)
    --FROM (SELECT DISTINCT ColumnNumber FROM #testDataWithPartitoin) AS Courses
    
    
    
    SELECT @ColumnName= ISNULL( Convert(varchar(max), @ColumnName) + ',','') 
           + ColumnNumber
    FROM (SELECT DISTINCT ColumnNumber FROM #testDataWithPartitoin) AS Courses
    
    PRINT(@ColumnName)
    
    
    SELECT @DynaColumnName= ISNULL(@DynaColumnName  + ',','') + QUOTENAME(DisplayName) + ' Varchar(25)' FROM (SELECT DISTINCT DisplayName FROM #testDisplayDataColumnNames) AS LcodeName
    
    
    CREATE TABLE [#test2]
    (TEMPID Varchar(50) )
    
    declare @sql varchar(2000)
    select @sql = 'ALTER TABLE #test2 ADD ' + @DynaColumnName
    
    exec(@sql) --- this adds the fields to the temp table
    
    
    
    SET   @DynamicPivotQuery = N'Insert into [#test2] SELECT Facility, ' +   @ColumnName + '
        FROM #testDataWithPartitoin
        PIVOT( MAX(EnrollmentID) 
              FOR ColumnNumber IN (' + @ColumnName + ')) AS P';
    
    
    		  
    
    
    
    
    
    --Execute the Dynamic Pivot Query
    EXEC sp_executesql @DynamicPivotQuery
    
    
    
    
    select * from [#test2]
    
    Drop Table #testData
    Drop Table #test2
    drop table [#testDisplayDataColumnNames]
    DROP Table #testDataWithPartitoin
    
    

    Mark As Answer if it is helpful.

    Regards,
    Omkar.

    Monday, March 5, 2018 5:29 AM
  • User77042963 posted
     
    
    CREATE TABLE #testData ( Facility varchar(10), SubjectName varchar(50), EnrollmentID varchar(15), ColumnNumber int)
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','16FBSM013',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17S001',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17S002',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17S007',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17S009',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17S011',1) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','B','15FBSM014',2) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','B','15FBSM035',2) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','B','15FBSM066',2) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','B','15FBSM070',2) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','B','CSC17F016',2) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','B','CSC17F017',2) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','15FBSM014',3) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','15FBSM015',3) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','15FBSM016',3) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','15FBSM017',3) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17F018',3) 
    Insert into #testData (Facility,SubjectName, EnrollmentID, ColumnNumber) Values('CR102','A','CSC17F019',3)
    
    --;with mycte as (
    -- SELECT * , row_number() Over(Partition by Facility,SubjectName, ColumnNumber Order by EnrollmentID ) rn 
    -- from #testData)
    -- Select Facility   
    -- ,Max(Case when SubjectName+ Cast(ColumnNumber as varchar(2))='A'+'1' then EnrollmentID End ) 'Column_1_A'
    -- ,Max(Case when SubjectName+ Cast(ColumnNumber as varchar(2))='B'+'2' then EnrollmentID End ) 'Column_2_B'
    -- ,Max(Case when SubjectName+ Cast(ColumnNumber as varchar(2))='A'+'3' then EnrollmentID End ) 'Column_3_A'
    --from mycte
    --Group by Facility, rn
    
     
    
    
     --dynamic sql
    Declare @sqlCase as NVarchar(4000) =null
    Declare @sql as NVarchar(4000)=null
     
      
    declare @ColumnHeaders VARCHAR(MAX), @ColumnHeaders1 VARCHAR(MAX);
    set @ColumnHeaders = STUFF( (SELECT  ',' 
    + 'Max(CASE WHEN SubjectName+ Cast(ColumnNumber as varchar(2))=' + quotename(SubjectName+Cast(ColumnNumber as varchar(2)),'''') + ' THEN EnrollmentID else null end ) as ' + quotename('Column_'+Cast(ColumnNumber as varchar(2))+'_'+SubjectName,'''')  + char(10)+char(13)
                                 FROM #testData
    							 group by SubjectName, ColumnNumber  
                                 order by ColumnNumber,SubjectName
                                 FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
    							 ;
     --print @ColumnHeaders
     
     Set @sql  =' Select Facility,'
     +   @ColumnHeaders + ' from (
    Select * , row_number() Over(Partition by Facility,SubjectName, ColumnNumber Order by EnrollmentID ) rn   from #testData) t
      Group by Facility, rn ';
         
    --print @sql
    EXEC(@SQL)
    
    
     
    drop table #testData

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, March 5, 2018 7:07 PM
  • User1623409651 posted

    Thanks limno your solution 100% worked.

    Last query I don't want to order by the result set, i want to generate row numbers in the same order the data are added

    I have added Name in the  Top of Enrollment ID Column  after running the query it shows first column in the fifth position 

    --dynamic sql
    Declare @sqlCase as NVarchar(4000) =null
    Declare @sql as NVarchar(4000)=null
     
      
    declare @ColumnHeaders VARCHAR(MAX), @ColumnHeaders1 VARCHAR(MAX);
    set @ColumnHeaders = STUFF( (SELECT  ',' 
    + 'Max(CASE WHEN SubjectName+ Cast(ColumnNumber as varchar(2))=' + quotename(SubjectName+Cast(ColumnNumber as varchar(2)),'''') + ' THEN EnrollmentID else null end ) as ' + quotename('Column_'+Cast(ColumnNumber as varchar(2))+'_'+SubjectName,'''')  + char(10)+char(13)
                                 FROM #testData
    			     group by SubjectName, ColumnNumber  
                                 order by ColumnNumber,SubjectName
                                 FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '')
    							 ;
     --print @ColumnHeaders
     
     Set @sql  =' Select Facility,'
     +   @ColumnHeaders + ' from (
    Select * , row_number() Over(Partition by Facility,SubjectName, ColumnNumber Order by EnrollmentID ) rn   from #testData) t
      Group by Facility, rn ';
         
    --print @sql
    EXEC(@SQL)

    Please suggest the way

    Tuesday, March 6, 2018 12:45 PM
  • User77042963 posted

    "... i want to generate row numbers in the same order the data are added"

    There is no order in your table storage by definition. Only way to guarantee resultset order is to use an ORDER BY clause to define the order in your SELECT.  I used one column in the order by  clause to make row_number function work, but it may not be the column you are interested in. You need a sequence column or timestamp column to track some order in your mind.

    Tuesday, March 6, 2018 2:44 PM
  • User1623409651 posted

    Thanks limno for you reply.

    Enrollment ID is the column name which is already in sorted order , i dont want to order by this column . i tried with  adding another column for order by . Issue Resolved

    Thanks for your help

    Wednesday, March 7, 2018 4:32 AM