troubleshooting Question

SQL server converting rows to columns

Avatar of soozh
soozhFlag for Sweden asked on
SQLMicrosoft SQL Server
5 Comments1 Solution20 ViewsLast Modified:
I am sure this is a really simple question but my brain is not working today.

I have a table called persons that is defined as:
      [Id] [int] IDENTITY(1,1) NOT NULL,
      [PersonId] int not null,


      [IsBaseline] bit not null,


      [H_BAS_Visus] int,
      [V_BAS_Visus] int,


      [H_FU_Visus] int,
      [V_FU_Visus] int

The PersonId column uniquely defines the person.  The IsBaseline column defines whether the data has been collected at a baseline visit (it then has a value of true), or a follow up visit (value null/false).

When it is a baseline visit the columns *_BAS_Visus contain data, and when a follow up visit then the columns *_FU_Visus contain data.

I need to produce a view over this table where the columns are just PersonId, IsBaseline, and Visus.

So for every row in the Persons table there will be two rows in my view... one for each eye.

So this data:                                                
PersonIdIsBaselineH_BAS_VisusV_BasVisusH_FU_VisusV_FU_Visus
1112

10

34
20

56
30

78
41910


Should produce this:
 
PersonIdIsBaselineVisus
111
112
103
104
205
206
307
308
419
4110


Can anyone give me a few tips?



ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros