Solved

SQL Server Query Syntax

Posted on 2013-02-04
6
263 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

615 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