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
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
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 below
could some one help me out pls.....