0
create table Projectdata
(
    ProjectId INT,
    ProjectName varchar(50),
    AssociateCount INT,
    Technology varchar(150),
    ReportMonth INT,
    Reportyear INT
)

insert into Projectdata values (111, 'ABC', 2, '.NET', 1, 2014)
insert into Projectdata values (112, 'DEF', 3, '.NET', 1, 2014)
insert into Projectdata values (113, 'XYZ', 5, 'JAVA', 1, 2014)
insert into Projectdata values (111, 'ABC', 3, 'JAVA', 2, 2014)
insert into Projectdata values (112, 'DEF', 2, '.NET', 2, 2014)
insert into Projectdata values (113, 'XYZ', 5, 'JAVA', 2, 2014)
insert into Projectdata values (112, 'DEF', 1, 'JAVA', 3, 2014)
insert into Projectdata values (113, 'XYZ', 5, 'JAVA', 3, 2014)

Select * from Projectdata gives data like

enter image description here

I have written a dynamic pivot query but i could't see the data in the required format.

posting my dynamic query and output

DECLARE @outputStatus nvarchar(max)
, @pivotlistStatus VARCHAR(MAX)
, @pivotlistScore VARCHAR(MAX)


SELECT   
    @pivotlistStatus = ISNULL(@pivotlistStatus + ',', '') + QUOTENAME(createdate1)
FROM
    (
        SELECT 
            TOP 1000 CONVERT(CHAR(3),DATENAME(MONTH, DATEADD(MONTH,ReportMonth,0) -1))+'-'+ CONVERT(VARCHAR(4),ReportYear)+'-'+'Status' as createdate1
        FROM
            (
                SELECT 
                    DISTINCT ReportMonth, 
                    ReportYear
                FROM 
                    Projectdata
                GROUP BY 
                    ReportMonth, 
                    ReportYear
            )rep
        ORDER BY
            ReportMonth, 
            ReportYear
    )as months1
SELECT   
    @pivotlistScore = ISNULL(@pivotlistScore + ',', '') + QUOTENAME(createdate1)
FROM
    (
        SELECT 
            TOP 1000 CONVERT(CHAR(3),DATENAME(MONTH, DATEADD(MONTH,ReportMonth,0) -1))+'-'+ CONVERT(VARCHAR(4),ReportYear)+'-'+'Score' as createdate1
        FROM
            (           
                SELECT 
                    DISTINCT ReportMonth, 
                    ReportYear
                FROM 
                    Projectdata
                GROUP BY 
                    ReportMonth, 
                    ReportYear
            )rep
        ORDER BY
            ReportMonth, 
            reportyear
    )as months  

 
SET @outputStatus = N'
SELECT 
    ProjectId,
    ProjectName,
    ' + @pivotlistStatus + '
    ,' + @pivotlistScore +'
    
FROM
    (SELECT 
        DISTINCT 
        ProjectId,
        ProjectName,
        CONVERT(CHAR(3),DATENAME(MONTH, DATEADD(MONTH,ReportMonth,0) -1))+''-''+ CONVERT(VARCHAR(4),ReportYear)+''-''+''Status'' AS CreatedateStatus ,
        CONVERT(CHAR(3),DATENAME(MONTH, DATEADD(MONTH,ReportMonth,0) -1))+''-''+ CONVERT(VARCHAR(4),ReportYear)+''-''+''Score'' AS CreatedateScore ,
        AssociateCount,
        Technology
    FROM    
        Projectdata 
      ) pvtunion
     PIVOT ( MAX(Technology) FOR CreatedateStatus IN (' + @pivotlistStatus + ') ) AS PVTstatus
     PIVOT ( SUM(AssociateCount) FOR CreatedateScore IN (' + @pivotlistScore + ') ) AS PVTScore
    
ORDER BY ProjectId'
 
EXEC sp_executesql @outputStatus

enter image description here

But i need the one project data in a single row... i could see the result as 8 rows but i need the ouput in 3 rows as belowenter image description here

could some one help me out pls.....

Community
  • 1
  • 1
user1696526
  • 11
  • 1
  • 2
  • Possible duplicate of SQL Server Pivot on Multiple Columns (http://stackoverflow.com/questions/18662012/sql-server-pivot-on-multiple-columns) ... though the dynamic PIVOT adds something to the question. – AHiggins Jul 22 '14 at 12:59
  • Assume a finite number of columns and write a static version. Figure out the dynamic parts in the resulting query and translate it to dynamic SQL in the same way as you would a single-column PIVOT query. – Andriy M Jul 22 '14 at 14:07

0 Answers0