• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 273
  • Last Modified:

SQL Server Query Syntax

I have a table with 10 columns
 2 columns need to be adjusted from this format:

Col1                  Col2
SpecA                 A1234
SpecB                 B1234
SpecC                 C1234
SpecB                 BB123
SpecB                 BBB123
SpecC                 C123
SpecA                 AA123


To this format:
SpecA          SpecB         SpecC
A1234         B1234        C1234
AA123         BB123        C123
NULL           BBB123      NULL
0
spirose
Asked:
spirose
  • 3
  • 3
1 Solution
 
appariCommented:
try like this, are the remaining 8 cols values are same for SpecA,SpecB,Spec rows?


SELECT *
FROM
(SELECT Col1, Col2, Col3, Col4,Col5,Col6,Col7,Col8,Col9,Col10
    FROM TableName) AS SrcTab
PIVOT
(
Max(Col2)
FOR Col1 IN (SpecA, SpecB,SpecC)
) AS PivotTable
0
 
spiroseAuthor Commented:
Hi Appari,
Thanks for chiming in. There is already an existing pivot (See example below). How do I incorporate additional pivots?

SELECT  ColA,
ColB,
ColC,
ColD,
ColE,
       ColF,
ColG,
ColH
FROM    ( SELECT DISTINCT
                   
          FROM      AnyTable
         
        ) AS sourcetable PIVOT ( max(ColID) FOR MainCOl IN ( ColB, ColC, ColD, ColE ) )AS pvt
0
 
appariCommented:
try like this code in bold part is your existing SQL


SELECT *
FROM
(
SELECT  ColA,
ColB,
ColC,
ColD,
ColE,
       ColF,
ColG,
ColH
FROM    ( SELECT DISTINCT
                   
          FROM      AnyTable
         
        ) AS sourcetable PIVOT ( max(ColID) FOR MainCOl IN ( ColB, ColC, ColD, ColE ) )AS pvt


) AS SrcTab
PIVOT
(
Max(Col2)
FOR Col1 IN (SpecA, SpecB,SpecC)
) AS PivotTable
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
spiroseAuthor Commented:
Hi Appari,
One issue is that the field values for Col1 has spaces between them as follows:

Col1                    Col2
Spec A                 A1234
Spec B                 B1234
Spec C                 C1234
Spec B                 BB123
Spec B                 BBB123
Spec C                 C123
Spec A                 AA123
0
 
appariCommented:
change
FOR Col1 IN (SpecA, SpecB,SpecC)
to
FOR Col1 IN ([Spec A], [Spec B], [Spec C] )
0
 
spiroseAuthor Commented:
Thanks! works like a charm :)
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now