Solved

SQL Server Query Syntax

Posted on 2013-02-04
6
254 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

911 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

17 Experts available now in Live!

Get 1:1 Help Now