Solved

Pivot Rows to Columns

Posted on 2013-06-06
8
469 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 500 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 

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

Industry Leaders: 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

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how the fundamental information of how to create a table.

696 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