Using Pivot in T-SQL

Question

0.00/5 (No votes)

See more:

Hey all,

I'm trying to carry out a pivot on a table in a SQL Server Database but am hitting an unhelpful error message. My query is as follows:

SQL

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName= ISNULL(@ColumnName + ', ','') + QUOTENAME(EnquiryPeriod)
FROM (SELECT DISTINCT EnquiryPeriod FROM Enquiries) AS EnqPeriod

SET @DynamicPivotQuery =
  N'SELECT Children.ChildID, ' + @ColumnName + '
    FROM (Enquiries
    INNER JOIN CarerChildren ON CarerChildren.CarerID = Enquiries.CarerID)
    INNER JOIN Children ON CarerChildren.ChildID = Children.ChildID
    WHERE (((CarerChildren.DateMovedOn) Is Null))
    GROUP BY Children.ChildID, [Firstname] + [Surname], Children.DateOfBirth
    ORDER BY [Firstname] + [Surname], Enquiries.EnquiryPeriod
    PIVOT(Max(EnquiryDate)
          FOR EnquiryPeriod IN (' + @ColumnName + ')) AS PVTTable'

EXEC (@DynamicPivotQuery)

It gives the error "Incorrect syntax near the word 'PIVOT'. It's probably a simple error, just wondering if anyone can spot what I'm doing wrong?

Posted 21-Aug-14 6:19am

Steve 2712

Updated 21-Aug-14 15:08pm

chaau

v2

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Ashi0891 · Answer 1 · 2014-08-21T08:11:00

I havn't worked using pivot so its just a guess. Don't mind if I'm mistaken.
I think you need to follow this syntax as below

XML

SELECT <non-pivoted column>,
    [first pivoted column] AS <column name>,
    [second pivoted column] AS <column name>,
    ...
    [last pivoted column] AS <column name>
FROM
    (<SELECT query that produces the data>)
    AS <alias for the source query>
PIVOT
(
    <aggregation function>(<column being aggregated>)
FOR
[<column that contains the values that will become column headers>]
    IN ( [first pivoted column], [second pivoted column],
    ... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;

so I think you need to put order by after pivot?

Reference: Using PIVOT and UNPIVOT-TechNet-Microsoft[^]

chaau · Answer 2 · 2014-08-21T15:13:00

You need to modify the sub-query that you use for your source so that it has all columns that will be used for your PIVOT, like this:

SQL

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)
SELECT @ColumnName= ISNULL(@ColumnName + ', ','') + QUOTENAME(EnquiryPeriod)
FROM (SELECT DISTINCT EnquiryPeriod FROM Enquiries) AS EnqPeriod
 
SET @DynamicPivotQuery =
  N'SELECT ChildID, [childName], ' + @ColumnName + '
    FROM (SELECT Children.ChildID, [Firstname] + [Surname] as [childName],
    Enquiries.EnquiryPeriod, EnquiryDate
    FROM Enquiries
    INNER JOIN CarerChildren ON CarerChildren.CarerID = Enquiries.CarerID)
    INNER JOIN Children ON CarerChildren.ChildID = Children.ChildID
    WHERE CarerChildren.DateMovedOn Is Null) AS pvtSource
    PIVOT(Max(EnquiryDate)
          FOR EnquiryPeriod IN (' + @ColumnName + ')) AS PVTTable'
 
EXEC (@DynamicPivotQuery)

Herman<T>.Instance · Answer 3 · 2014-08-22T01:33:00

Solution 3

Two things:
-> ISNULL(@ColumnName + ', ','') better: COALESCE(@ColumnName +', ', '')
-> is QUOTENAME(EnquiryPeriod) separating also with a comma? NO but if the distinct results in more than 1 row.....you have a problem.
In my article [^]you see a method to deal with that

Posted 22-Aug-14 1:33am

Herman<T>.Instance

Using Pivot in T-SQL

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0