?
Solved

Pivot Rows to Columns

Posted on 2013-06-06
8
Medium Priority
?
478 Views
Last Modified: 2013-06-14
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

0
Comment
Question by:skull52
  • 4
  • 3
8 Comments
 
LVL 16

Expert Comment

by:santoshmotwani
ID: 39227423
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
 

Author Comment

by:skull52
ID: 39228818
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
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 39229841
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 

Author Comment

by:skull52
ID: 39229952
Sharath_123,
That worked perfectly thanks.
0
 

Author Comment

by:skull52
ID: 39236050
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
 
LVL 41

Expert Comment

by:Sharath
ID: 39243132
Is this still an issue?
0
 

Author Comment

by:skull52
ID: 39244227
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
 
LVL 41

Expert Comment

by:Sharath
ID: 39246809
Sure. can you open another question to get attention from other experts too.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question