Improve company productivity with a Business Account.Sign Up

x
  • 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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