Kadelec sweeties Kadelec sweeties - 1 year ago 85
SQL Question

Pivot query genetrating duplicate rows with null values

My pivot query is generating duplicate rows, and i'm blocked. can anyone solve this?

CREATE PROCEDURE [dbo].[test2]
AS
BEGIN

--Declare necessary variables
DECLARE @SQLQuery AS NVARCHAR(MAX);
DECLARE @WeekColumns AS NVARCHAR(MAX);

--Get unique values of pivot column
SELECT @WeekColumns = COALESCE(@WeekColumns + ',', '')
+ QUOTENAME(name)
FROM ( SELECT name
FROM ( SELECT [id_week]
, name
, value
, prjfamilyID
, ROW_NUMBER() OVER ( PARTITION BY name
ORDER BY [id_week] ASC ) rn
FROM weekvalues
) a
WHERE rn = 1
) AS PivotExample;

SELECT @WeekColumns;

--Create the dynamic query with all the values for
--pivot column at runtime
SET @SQLQuery = N'SELECT prjfamilyID, ' + @WeekColumns + '
FROM [dbo].[weekvalues]
PIVOT( MAX(value)
FOR name IN (' + @WeekColumns + ')) AS P';

SELECT @SQLQuery;
--Execute dynamic query
EXEC sp_executesql @SQLQuery;

END;


and Here is the result i am getting

1 NULL NULL 1004 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
1 NULL NULL NULL NULL NULL 997 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 992 NULL NULL NULL NULL
1 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 997
2 NULL NULL 2100 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 NULL NULL NULL NULL NULL 2036 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2014 NULL NULL NULL NULL
2 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 2040
3 NULL NULL 1944 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
3 NULL NULL NULL NULL NULL 1914 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
3 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1882 NULL NULL NULL NULL
3 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 1880


what i want is for number 1 for example i want to combine all rows in one containing all values

thanks

Answer Source

Can you change your @SQLQuery query with this one and try again?

SET @SQLQuery = 
        N'SELECT * FROM (SELECT prjfamilyID, value, name
    FROM [dbo].[weekvalues] ) SRC
    PIVOT( MAX(value) 
          FOR name IN (' + @WeekColumns + ')) AS P';
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download