Link to home
Start Free TrialLog in
Avatar of Denis Orozco
Denis OrozcoFlag for United States of America

asked on

How to pivot my query

Hi there,
My query statement needs to be pivot. I need data to display in a prefer format.
My query looks like this:
SELECT COUNT(*), OFFICER_ID, RTRIM(TASK_NAME) FROM FL_ACTION where START_DATE between '10/01/2012' and '10/31/2012'
  AND TASK_NAME IN('*Adjudication Hearing Day Inspection*',
  '*Must Comply By Date Inspection*','1st Reinspection',
  '2nd Reinspection','Courtesy Reinspection','Requested Inspection') 
  GROUP BY OFFICER_ID, TASK_NAME
  ORDER BY OFFICER_ID

Open in new window


after i execute it my data looks like this:
(No column name)	OFFICER_ID	(No column name)
99	NULL	*Adjudication Hearing Day Inspection*
58	NULL	*Must Comply By Date Inspection*
463	NULL	1st Reinspection
418	NULL	2nd Reinspection
232	NULL	Courtesy Reinspection
4	NULL	Requested Inspection
1	aaraya                                            	Requested Inspection
1	BWHALEN                                           	*Must Comply By Date Inspection*
57	BWHALEN                                           	1st Reinspection
26	BWHALEN                                           	2nd Reinspection
12	BWHALEN                                           	Courtesy Reinspection
1	BWHALEN                                           	Requested Inspection
31	GHILL                                             	1st Reinspection
24	GHILL                                             	2nd Reinspection
7	GHILL                                             	Courtesy Reinspection
1	HROWAN                                            	2nd Reinspection
22	igardner                                          	1st Reinspection
14	igardner                                          	2nd Reinspection
6	igardner                                          	Courtesy Reinspection
21	jcadahia                                          	1st Reinspection
15	jcadahia                                          	2nd Reinspection
1	jcadahia                                          	Courtesy Reinspection
1	JDENNETTC                                         	1st Reinspection
1	JDENNETTC                                         	2nd Reinspection
64	jpineda                                           	1st Reinspection
24	jpineda                                           	2nd Reinspection
3	jpineda                                           	Requested Inspection
31	jweitzner                                         	1st Reinspection
16	jweitzner                                         	2nd Reinspection
8	jweitzner                                         	Courtesy Reinspection
2	ldenapoli                                         	Courtesy Reinspection
2	MBERTUZZI                                         	1st Reinspection
1	MBERTUZZI                                         	2nd Reinspection
1	MBERTUZZI                                         	Courtesy Reinspection
60	NROBINSON                                         	1st Reinspection
32	NROBINSON                                         	2nd Reinspection
1	NROBINSON                                         	Courtesy Reinspection
37	RPACHECO                                          	1st Reinspection
15	RPACHECO                                          	2nd Reinspection
2	RPACHECO                                          	Courtesy Reinspection
42	rrobinson                                         	1st Reinspection
28	rrobinson                                         	2nd Reinspection
6	rrobinson                                         	Courtesy Reinspection
24	sbrown                                            	1st Reinspection
13	sbrown                                            	2nd Reinspection
1	sbrown                                            	Courtesy Reinspection
2	sjacobs                                           	1st Reinspection
1	sjacobs                                           	2nd Reinspection
2	TPINDER                                           	*Must Comply By Date Inspection*
54	TPINDER                                           	1st Reinspection
49	TPINDER                                           	2nd Reinspection
4	TPINDER                                           	Courtesy Reinspection

Open in new window


and I need the data like this

      *Adjudication Hearing Day Inspection*      *Must Comply By Date Inspection*      1st Reinspection      2nd Reinspection      Courtesy Reinspection      Requested Inspection
Null      99      58      463      418      232      4
aaraya                                                                                1
BWHALEN                                                       1      57      26      12      1
...

So in order words i need the columns to be RTRIM(TASK_NAME), the rows OFFICER_ID, and the data COUNT(*)

how can i achieved this?
thanks!
Avatar of x-men
x-men
Flag of Portugal image

try:


DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(RTRIM(TASK_NAME))
  FROM (SELECT RTRIM(TASK_NAME) FROM FL_ACTION
  GROUP BY TASK_NAME;

SET @sql = N'
SELECT ' + STUFF(@columns, 1, 2, '') + '
FROM
(
  SELECT OFFICER_ID, RTRIM(TASK_NAME)
   FROM FL_ACTION
where START_DATE between ''10/01/2012'' and ''10/31/2012''
  AND TASK_NAME IN(''*Adjudication Hearing Day Inspection*'',
  ''*Must Comply By Date Inspection*'',''1st Reinspection'',
  ''2nd Reinspection'',''Courtesy Reinspection'',''Requested Inspection'')
  GROUP BY OFFICER_ID, TASK_NAME
) AS j
PIVOT
(
  SUM(TASK_NAME) FOR Name IN ('
  + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
  + ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;
Avatar of Denis Orozco

ASKER

Throws an error:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near ';'.

Before the set@sql if i removed the ; i get an error on the set statement
try:


SELECT [*Adjudication Hearing Day Inspection*],[*Must Comply By Date Inspection*],[1st Reinspection],[2nd Reinspection],[Courtesy Reinspection],[Requested Inspection]
FROM (
SELECT COUNT(*), OFFICER_ID, RTRIM(TASK_NAME) FROM FL_ACTIONFL_ACTION
where START_DATE between '10/01/2012' and '10/31/2012'
  AND TASK_NAME IN('*Adjudication Hearing Day Inspection*',
  '*Must Comply By Date Inspection*','1st Reinspection',
  '2nd Reinspection','Courtesy Reinspection','Requested Inspection')
  GROUP BY OFFICER_ID, TASK_NAME) AS Q
PIVOT(
SUM(OFFICER_ID) for TASK_NAME in ([*Adjudication Hearing Day Inspection*],[*Must Comply By Date Inspection*],[1st Reinspection],[2nd Reinspection],[Courtesy Reinspection],[Requested Inspection]) ) AS P
ASKER CERTIFIED SOLUTION
Avatar of Denis Orozco
Denis Orozco
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
there wasn't any answer that provide a solution