CODEWITHSUNDEEP
sqlsql-serverselect
Okasha Momin
Okasha Momin

Reputation: 231

SQL Select Issue: How to split Column values

I'm working in SQL Server 2012 (Express Edition). I have one table named as Observations. In that table I have 2 columns "Observations, ObservationType". There are only two types of observations in the table:

  1. Clinical
  2. Non-Clinical

My table structure look like this..

Observations     ObservationType
-------------------------------
AAAA            Clinical
BBBB            Clincal
CCCC            Non-Clinical
DDDD            Clinical
EEEE            Non-Clinical

Now I have to select all row explicitly based on observation type I tried but my output was like

Clinical                 Non-Clinical
---------------------------------------
AAAA                        NULL
BBBB                        NULL
NULL                        CCCC
DDDD                        NULL
NULL                        EEEE

But I want like this

Clinical                 Non-Clinical
---------------------------------------
AAAA                        CCCC
BBBB                        EEEE
DDDD

Upvotes: 0

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269453

This is a little tricky, because the columns in a row have nothing to do with each other. However, you can accomplish this by using row_number() to generate a sequence number and then aggregate:

select max(case when ObservationType = 'Clinical' then Observation end) as Clinical,
       max(case when ObservationType = 'Non-Clinical' then Observation end) as NonClinical
from (select o.*,
             row_number() over (partition by ObservationType order by Observation) as seqnum
      from Observations o
     ) o
group by seqnum
order by seqnum;

Upvotes: 1

Related Questions