Solved

Pivot Rows to Columns

Posted on 2013-06-06
8
463 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 40

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 40

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 40

Expert Comment

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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

813 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now