CODEWITHSUNDEEP
sql-serverpivot
user1163513
user1163513

Reputation: 4207

SQL Server Syntax Error on PIVOT

Can anyone please help me with the PIVOT table syntax error as I am using this for the First time.

DECLARE @sql AS varchar(max)<br/>
DECLARE @pivot_list AS varchar(max) <br/>
DECLARE @select_list AS varchar(max) <br/>

SELECT @pivot_list = COALESCE(@pivot_list + ', ', '') + '[' + CONVERT(varchar,    STATE_NAME) + ']'<br/>
      ,@select_list = COALESCE(@select_list + ', ', '') + '[' + CONVERT(varchar, STATE_NAME) + '] AS [' + CONVERT(varchar, STATE_NAME) + ']'
FROM (
SELECT DISTINCT name as STATE_NAME
    FROM k12_dms_states
) AS PIVOT_CODES

SET @sql = '
 SELECT COUNT(k12_dms_contacts_institution_jobtitles.id)  as total_count 
,k12_dms_job_titles.title as job_title,' + @select_list + '
FROM k12_dms_institution_master 
INNER JOIN k12_dms_contacts_institution_jobtitles ON k12_dms_institution_master.id = k12_dms_contacts_institution_jobtitles.inst_id 
INNER JOIN k12_dms_job_titles ON k12_dms_job_titles.id = k12_dms_contacts_institution_jobtitles.job_title_id 
GROUP BY k12_dms_job_titles.title
PIVOT (
total_count
FOR STATE_NAME IN (
    ' + @pivot_list + '
)
) AS pvt
 '

  PRINT @sql

  EXEC (@sql)

I am getting this error: -

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'PIVOT'.

Upvotes: 0

Views: 856

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239724

PIVOT belongs in the FROM clause. It needs to occur before any GROUP BY clause.

(Further edits based on commenting, to try to correct):

SET @sql = '
 SELECT 
k12_dms_job_titles.title as job_title,' + @select_list + '
FROM k12_dms_institution_master 
INNER JOIN k12_dms_contacts_institution_jobtitles ON k12_dms_institution_master.id = k12_dms_contacts_institution_jobtitles.inst_id 
INNER JOIN k12_dms_job_titles ON k12_dms_job_titles.id = k12_dms_contacts_institution_jobtitles.job_title_id 
PIVOT (
COUNT(k12_dms_contacts_institution_jobtitles.id)
FOR STATE_NAME IN (
    ' + @pivot_list + '
)
) AS pvt
 '

Upvotes: 1

Related Questions