Pivot Rows to Columns

Hello,
I have a query that I need to pivot the results with 2 column Headings, currently the result set is as follows

ITEMNMBR               LONGNAME
AALGUIMI                 GALIL  
AALGUIMI                 223 REM                                                          
ADCG2012000          BEAR
ADCG2012000          5.56 NATO

This Code Generates the above results:
 SELECT   top 4  EXT00103.PT_UD_Key AS ITEMNMBR, EXT40102.LONGNAME
FROM         EXT00103 INNER JOIN
                      EXT40102 ON EXT00103.PT_Window_ID = EXT40102.PT_Window_ID AND EXT00103.PT_UD_Number = EXT40102.Field_Number AND 
                      EXT00103.TOTAL = EXT40102.LNITMSEQ
WHERE     (EXT00103.PT_Window_ID = 'FIREARMINFO')
ORDER BY ITEMNMBR, EXT00103.PT_UD_Number     

Open in new window

I want it to look like this
ITEMNMBR             MODEL          CALIBER
AALGUIMI              GALIL              223 REM      
AADCG2012000      BEAR             5.56 NATO

I have tried this but it generates
 Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'PIVOT'.
   SELECT     ITEMNMBR, [1] AS [MODEL], [2] AS [CALIBER]
FROM         (SELECT      EXT00103.PT_UD_Key AS ITEMNMBR, EXT40102.LONGNAME
                       FROM         EXT00103 INNER JOIN
                      EXT40102 ON EXT00103.PT_Window_ID = EXT40102.PT_Window_ID AND EXT00103.PT_UD_Number = EXT40102.Field_Number AND 
                      EXT00103.TOTAL = EXT40102.LNITMSEQ
WHERE     (EXT00103.PT_Window_ID = 'FIREARMINFO')) AS p (PIVOT (MAX(MODEL) FOR CALIBER IN ([1], [2])) AS pvt 
ORDER BY ITEMNMBR                 
                     

Open in new window

skull52Asked:
Who is Participating?
 
SharathConnect With a Mentor Data EngineerCommented:
try this.
;WITH cte 
     AS (SELECT TOP 4 EXT00103.PT_UD_Key AS ITEMNMBR, 
                      EXT40102.LONGNAME 
         FROM   EXT00103 
                INNER JOIN EXT40102 
                        ON EXT00103.PT_Window_ID = EXT40102.PT_Window_ID 
                           AND EXT00103.PT_UD_Number = EXT40102.Field_Number 
                           AND EXT00103.TOTAL = EXT40102.LNITMSEQ 
         WHERE  ( EXT00103.PT_Window_ID = 'FIREARMINFO' ) 
         ORDER  BY ITEMNMBR, 
                   EXT00103.PT_UD_Number) 
SELECT ITEMNMBR, 
       MAX(CASE 
             WHEN PATINDEX('%[0-9]%', LONGNAME) <> 1 THEN LONGNAME 
           END) MODEL, 
       MAX(CASE 
             WHEN PATINDEX('%[0-9]%', LONGNAME) = 1 THEN LONGNAME 
           END) CALIBER 
FROM   cte 
GROUP  BY ITEMNMBR; 

Open in new window

0
 
santoshmotwaniCommented:
SELECT itemnmbr,
       [1] AS [MODEL],
       [2] AS [CALIBER]
FROM   (SELECT ext00103.pt_ud_key AS ITEMNMBR,
               ext40102.longname
        FROM   ext00103
               INNER JOIN ext40102
                       ON ext00103.pt_window_id = ext40102.pt_window_id
                          AND ext00103.pt_ud_number = ext40102.field_number
                          AND ext00103.total = ext40102.lnitmseq
        WHERE  ( ext00103.pt_window_id = 'FIREARMINFO' )) AS p
       PIVOT (Max(model)
             FOR caliber IN ([1],
                             [2])) AS pvt
ORDER  BY itemnmbr

--- you have got extra ( before pivot
0
 
skull52Author Commented:
Thanks for the response but it is giving me the following error

Msg 207, Level 16, State 1, Line 12
Invalid column name 'model'.
Msg 207, Level 16, State 1, Line 13
Invalid column name 'caliber'.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
skull52Author Commented:
Sharath_123,
That worked perfectly thanks.
0
 
skull52Author Commented:
I spoke to soon regarding  Sharath_123 solution, it appears that it is giving wrong data. if I filter on a specific item in the select portion of the cte I get correct data

ITEMNMBR                 LONGNAME
----------------------------------------------------
REMGKM5568             EXPRESS   = MODEL                                                  
REMGKM5568             12            = CALIBER

SELECT top 100 percent EXT00103.PT_UD_Key AS ITEMNMBR, 
                      EXT40102.LONGNAME 
         FROM   EXT00103 
                INNER JOIN EXT40102 
                        ON EXT00103.PT_Window_ID = EXT40102.PT_Window_ID 
                           AND EXT00103.PT_UD_Number = EXT40102.Field_Number 
                           AND EXT00103.TOTAL = EXT40102.LNITMSEQ 
         WHERE  ( EXT00103.PT_Window_ID = 'FIREARMINFO' ) AND  (EXT00103.PT_UD_Key = 'REMGKM5568')
         ORDER  BY ITEMNMBR, 
                   EXT00103.PT_UD_Number

Open in new window

However if I run the cte I get a null value for MODEL and EXPRESS for CALIBER

ITEMNMBR         MODEL     CALIBER
---------------------------------------------------------
REMGKM5568       NULL      EXPRESS
0
 
SharathData EngineerCommented:
Is this still an issue?
0
 
skull52Author Commented:
Yes, I found a workaround because I had a narrow time-frame in which to complete the task  but I would still like to get this to work correctly
0
 
SharathData EngineerCommented:
Sure. can you open another question to get attention from other experts too.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.