March 26, 2014 at 9:03 am
Hello All
I have a query
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME(name)
FROM (select distinct name from tbldaysworked) AS EmpName
order by name
SET @DynamicPivotQuery =
N'SELECT dayworked,' + @ColumnName + '
FROM tbldaysworked
PIVOT(max(fullday)
FOR name IN (' + @ColumnName + ')) AS PVTTable group by dayworked,' + @columnname + 'order by dayworked'
print @DynamicPivotQuery
EXEC sp_executesql @DynamicPivotQuery
That produces a table like this
Dayworked,Tom,Dick,Harry
2014-01-10,NULL,NULL,1
2013-01-10,NULL,1,NULL
2013-01-10,1,NULL,NULL
and so on.
I've tried putting my group by everywhere but it's not working. I'm missing something but what?
Any help would be much appreciated.
Thanks
M
March 26, 2014 at 9:18 am
Can you post the output from " print @DynamicPivotQuery "?
March 26, 2014 at 9:59 am
Here's what the print @DynamicPivotQuery looks like.
SELECT dayworked,[Tom],[Dick],[Harry]
FROM tbldaysworked
PIVOT(max(fullday)
FOR name IN ([Tom],[Dick],[Harry]
AS PVTTable group by dayworked,[Tom],[Dick],[Harry]
order by dayworked
March 26, 2014 at 10:26 am
I'm not great at PIVOT, because I usually use CROSS TABS because they're more flexible to work with.
Here's an example with your problem.
To know more about dynamic CROSS TABS: http://www.sqlservercentral.com/articles/Crosstab/65048/
To learn about the concatenation method that I used: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
CREATE TABLE #tbldaysworked(
dayworkeddate,
namevarchar(10))
INSERT #tbldaysworked
SELECT '20140110', 'Harry' UNION ALL
SELECT '20130110', 'Dick' UNION ALL
SELECT '20130110', 'Tom'
DECLARE @SQL nvarchar(MAX)
SELECT @SQL = 'SELECT dayworked ' + CHAR(13)
+ (SELECT CHAR(9) + ',MAX(CASE WHEN name = ''' + name + ''' THEN 1 END) AS ' + QUOTENAME(name) + CHAR(13)
FROM #tbldaysworked
ORDER BY dayworked, name DESC
FOR XML PATH(''),TYPE).value('.', 'nvarchar(max)')
+ 'FROM #tbldaysworked ' + CHAR(13)
+ 'GROUP BY dayworked, name' + CHAR(13)
+ 'ORDER BY dayworked DESC, name'
EXECUTE sp_executesql @SQL
GO
DROP TABLE #tbldaysworked
July 10, 2014 at 7:19 am
I had the exact same problem as the OP. I am relatively new to SQL and don't fully understand why but I was able to get my dynamic query to work by selecting just the columns I wanted to pivot from the original source table, which included rows I was not interested in, into a temp table. I used the temp table as the source for the dynamic pivot and everything then worked as expected.
Perhaps someone who understands this better can expand on why it worked. Hope that helps someone.
July 10, 2014 at 10:33 am
RTovey1 (7/10/2014)
I had the exact same problem as the OP. I am relatively new to SQL and don't fully understand why but I was able to get my dynamic query to work by selecting just the columns I wanted to pivot from the original source table, which included rows I was not interested in, into a temp table. I used the temp table as the source for the dynamic pivot and everything then worked as expected.Perhaps someone who understands this better can expand on why it worked. Hope that helps someone.
It's the nature of PIVOT. PIVOT only works well with GROUP BY when your results end up with duplicate rows. Other kinds of things don't usually translate well with PIVOT, and NULL values can be a problem, so it's always best to pre-filter your source data to exactly what you need for the pivot, no more, no less, first, and then pivot from that. Rmember that it already is doing a default group by for any field you select that is NOT part of the pivot result, so mixing another GROUP BY into it doesn't always produce a good result.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
December 8, 2014 at 3:24 pm
Just found the issue with this. The example dynamic query is all over the place, but here is what is missing:
In the example, using the code you posted, the problem is the N'SELECT statement contains the @ColumnName variable. Don't use that or the Pivot will group by it. You shouldn't need a group by clause at all as the Pivot operator does this for you.
Instead, use the original column that the Column names are derived from with an outer select.
In other words, instead of this:
SET @DynamicPivotQuery =
N'SELECT dayworked,' + @ColumnName + '
FROM tbldaysworked
PIVOT(max(fullday)
FOR name IN (' + @ColumnName + ')) AS PVTTable group by dayworked,' + @columnname + 'order by dayworked'
Use this:
SET @DynamicPivotQuery =
N'SELECT * FROM
(SELECT dayworked, name, fullday
FROM tbldaysworked) AS DW --just to give this inner query an alias
PIVOT(max(fullday)
FOR name IN (' + @ColumnName + ')) AS PVTTable)'
By doing this, the only column left to group by is the dayworked column, which is what you want.
December 19, 2014 at 3:54 pm
Hi celticfire63, your method below gives same result as michael zrax.
Luis Cazares method gives what michael zrax wanted.
-----------------------------------------------
--DATA:
CREATE TABLE #tbldaysworked(
dayworkeddate,
namevarchar(10))
INSERT #tbldaysworked
SELECT '20140110', 'Harry' UNION ALL
SELECT '20130110', 'Dick' UNION ALL
SELECT '20130110', 'Tom'
SELECT * FROM #tbldaysworked
-----------------------------------------------
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SET @DynamicPivotQuery =
N'SELECT * FROM
(SELECT dayworked, name, fullday
FROM #tbldaysworked) AS DW --just to give this inner query an alias
PIVOT(max(fullday)
FOR name IN (' + @ColumnName + ')) AS PVTTable)'
SELECT @DynamicPivotQuery
DROP TABLE #tbldaysworked
December 20, 2014 at 11:20 am
Hi Kevin,
Sorry, I don't see any difference at all in your post? what's the difference in the query itself that avoids duplicates? I tested this and did not have any dups.
thanks
December 20, 2014 at 2:41 pm
Hi celticfire63, did you run the code I posted (which I copied from your post), it returns null.
December 20, 2014 at 4:38 pm
You didn't include a column called "fullday", which was in the original request. 😎
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply