I'd do this with cte's (common table expressions). By breaking up the Teams table based on the taskteam column, then joining them together on the idproject column: In this video, we learn all about the PIVOT Operator in SQL Server which is most important concept in SQL Server as well most frequently asked question in S In the PIVOT example it is drawing from a CTE whereas the SUM (CASE) is drawing directly from the table. But the SUM (CASE) performs the same drawing from the CTE. In my work example, the PIVOT comes back in 10 seconds while the SUM (CASE) comes back in 14. Clearly it must be doing something different under the covers. The pivot feature enables transforming a collection of objects into a new collection of objects flattening the original hierarchy. In the following example The Pivot extension method is used to transform a collection as follow: Using the folowing Link: LINQ Extensions Library (Pivot Extensions) Using the following line to pivot the data: I have been working to make a dynamic pivot in SQL Server that a user could call in a stored procedure and pass specified parameters ie: I am struggling to finish out the schema and table parameters. BEGIN DECLARE @Piv AS NVARCHAR (max) --final pivot. print this to see the exec statement DECLARE @NewColumns AS NVARCHAR (max) -- extract data and 2 Answers. You could just use UNION ALL to combine the two results, you would need to convert the roles from the top query from int to VARCHAR though: DECLARE @ID INT = 1; WITH Ans AS ( SELECT DISTINCT SessionID,ItemID_FK,Roles FROM tbl_Answers WHERE ID_FK = @ID ), PivotData AS ( SELECT items.ItemID_PK, items.ItemName, cntRoles = COUNT (ans 3 Answers. PIVOT should work fine - SQL Fiddle demo (schema borrowed from bluefeets answer) SELECT * FROM source PIVOT ( MIN (org) AS org, MIN (position) AS position FOR lang IN ('EN' AS en, 'FI' AS fi, 'SV' AS sv) ); thanks..this was quite helpful to learn about pivoting two columns. Conclusion. Both SQL Server PIVOT and GROUPING SETS provide tremendous flexibility over the shape of your output, and can really reduce the amount of heavy lifting that has be done by your presentation layer or middle tier. Sure, Excel is great at crosstab and conditional formatting, but that only scales to individual report consumers. .

how to use pivot in sql