Solved

Pivot Rows to Columns

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

Author Comment

by:skull52
ID: 39229952
Sharath_123,
That worked perfectly thanks.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.

757 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

18 Experts available now in Live!

Get 1:1 Help Now