Solved

SQL Server Query Syntax

Posted on 2013-02-04
6
253 Views
Last Modified: 2013-02-04
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
Comment
Question by:spirose
  • 3
  • 3
6 Comments
 
LVL 39

Expert Comment

by:appari
ID: 38853461
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
 

Author Comment

by:spirose
ID: 38853503
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
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
ID: 38853516
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:spirose
ID: 38853533
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
 
LVL 39

Expert Comment

by:appari
ID: 38853540
change
FOR Col1 IN (SpecA, SpecB,SpecC)
to
FOR Col1 IN ([Spec A], [Spec B], [Spec C] )
0
 

Author Closing Comment

by:spirose
ID: 38853557
Thanks! works like a charm :)
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In this article I will describe the Copy Database Wizard 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.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

705 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

14 Experts available now in Live!

Get 1:1 Help Now