question

sherriekubis avatar image
sherriekubis asked

Pivot table with FOR clause error

I have a table wqmp_gw_samp_technician_evw that looks like:

After my query I would like these results:

This is the query:

WITH
    pivot_data    
    AS
     (select dbo.Initials(technician) technician, 
        fcguid,
        ROW_NUMBER () OVER (PARTITION BY fcguid 
                ORDER BY technician DESC) row_num
      from wqmp_gw_samp_technician_evw)
select row_num, fcguid, 
       technician_1, 
       technician_2,
       technician_3
from pivot_data
 pivot (min (technician) 
   for row_num 
    in (1 technician_1, 
        2 technician_2, 
        3 technician_3) 
        )    order by fcguid;

<sorry, I tried to get out of this code block but can't get it to do that>

The error message I receive is:
Lookup Error - SQL Server Database Error: Incorrect syntax near '1'.
dbo.Initials is a function that returns the
initials of the technician.

I admit that I am coming from Oracle, and
this query works there, but now in MS SQL I'm stumped on how to show in a pivot
the technician initials for each fcguid.

Any insights are appreciated. Microsoft SQL
Server 2016 (SP2-CU11-GDR) (KB4535706) - 13.0.5622.0 (X64) Dec 15 2019 08:03:11
Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server
2016 Standard 10.0 (Build 14393: ) (Hypervisor)


 


Sherrie

t-sqlpivot
table2.jpg (26.8 KiB)
table3.jpg (12.6 KiB)
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It looks as though you want the "Complex Pivot Example" from https://docs.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15#complex-pivot-example

rough gist:

the FOR bit is just FOR ([1], [2], [3]) -- the brackets are important here
and you do the renaming in the outer SELECT - SELECT Rownum, fcguid, [1] AS Tech1, [2] AS Tech2...
etc.

0 Likes 0 ·

1 Answer

·
sherriekubis avatar image
sherriekubis answered

Thomas, worked like a charm, thank you.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.