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
Solved

Arrange column in SQL

Posted on 2015-01-25
5
132 Views
Last Modified: 2015-01-26
How can I return result as my sample below in sql?  There will always be only on mismatch, but one or multiple possible(s).

Sample data - my current result
clientid  poss_mismatch customername
1            mismatch            bank of a
1            possible               bofa
1            possible               b of a
3            mismatch            Wells
3            possible               Wells Fargo
3            possible               WellsFargo
etc


New Result
Clientid   Col_Mismatch         Col_Possible     Customername
1                bank of a
1                                                possible              bofa    
1                                                possible              b of a  
3               Wells
3                                                possible               Wells Fargo
3                                                possible               WellsFargo

etc
0
Comment
Question by:VBdotnet2005
  • 2
  • 2
5 Comments
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 40569320
Use the PIVOT clause, e.g.

SELECT * FROM yourTable
PIVOT ( MIN(CustomerName) FOR poss_mismatch IN (possible, mismatch)) P;

Open in new window

0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 40569798
like this ?

although  your ordiginal order (of possibles) can't be guaranteed to be maintained...

select  clientid
   ,case when poss_mismatch='Mismatch' then customername
         else ' ' end as col_mismatch
   ,case when pos_mismatch='mismatch' then ' '
         else pos_mismatch end as col_possible
   ,case when pos_mismatch='mismatch' then ' '
         else customername end as Customername
from yourtable
order by 1,2 Desc,3,4

Open in new window

0
 

Author Comment

by:VBdotnet2005
ID: 40571099
ste5an,
Is there a way to remove null from mistmatch and possible?
0
 

Author Comment

by:VBdotnet2005
ID: 40571100
How can I remove null?

SELECT * FROM yourTable
PIVOT ( MIN(CustomerName) FOR poss_mismatch IN (possible, mismatch)) P;

Open in new window

0
 
LVL 33

Expert Comment

by:ste5an
ID: 40571333
Use COALESCE():

SELECT Clientid,
  Customername ,
  COALESCE(possible, ''),
  COALESCE(mismatch, '')
FROM yourTable
PIVOT ( MIN(CustomerName) FOR poss_mismatch IN (possible, mismatch)) P;

Open in new window

0

Featured Post

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.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
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…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

860 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