asked on SQL server converting rows to columns
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:
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:
Should produce this:
Can anyone give me a few tips?
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
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:
| PersonId | IsBaseline | H_BAS_Visus | V_BasVisus | H_FU_Visus | V_FU_Visus |
| 1 | 1 | 1 | 2 | ||
| 1 | 0 | 3 | 4 | ||
| 2 | 0 | 5 | 6 | ||
| 3 | 0 | 7 | 8 | ||
| 4 | 1 | 9 | 10 |
Should produce this:
| PersonId | IsBaseline | Visus |
| 1 | 1 | 1 |
| 1 | 1 | 2 |
| 1 | 0 | 3 |
| 1 | 0 | 4 |
| 2 | 0 | 5 |
| 2 | 0 | 6 |
| 3 | 0 | 7 |
| 3 | 0 | 8 |
| 4 | 1 | 9 |
| 4 | 1 | 10 |
Can anyone give me a few tips?
SQLMicrosoft SQL Server
Last Comment
PortletPaul
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks Ryan... As always the solution is pretty straight forward.
I think that UNION ALL should be used in Ryan's suggestion. Otherwise UNION will exclude duplicates. For example, if value<1> == value<2> == 'Y' with UNION instead of two rows you will have only one. Conditions in Ryan suggestion should be corrected as well (4 times the same column name repeated in WHERE condition).
Another note, I think that the column visus_type should be added. Otherwise how do you know is visus H_FU_Visus or V_FU_Visus?
UNION with empty string will cause warnings.
It is better to do it in the following way - no warnings here:
DECLARE @t TABLE (Id INT IDENTITY, PersonId Int, IsBaseline Int, Visus Int, VisusType Int)
INSRT INTO @t (PersonId, IsBaseLine, Visus, VisusType)
SELECT PersonId, IsBaseLine, H_BAS_Visus, 1 WHERE H_BAS_Visus IS NOT NULL;
INSRT INTO @t (PersonId, IsBaseLine, Visus, VisusType)
SELECT PersonId, IsBaseLine, V_BAS_Visus, 2 WHERE V_BAS_Visus IS NOT NULL;
INSRT INTO @t (PersonId, IsBaseLine, Visus, VisusType)
SELECT PersonId, IsBaseLine, H_FU_Visus, 3 WHERE H_FU_Visus IS NOT NULL;
INSRT INTO @t (PersonId, IsBaseLine, Visus, VisusType)
SELECT PersonId, IsBaseLine, V_FU_Visus, 4 WHERE V_FU_Visus IS NOT NULL;
SElECT * FROM @t ORDER BY PersonId, VisusType;
Another note, I think that the column visus_type should be added. Otherwise how do you know is visus H_FU_Visus or V_FU_Visus?
UNION with empty string will cause warnings.
It is better to do it in the following way - no warnings here:
DECLARE @t TABLE (Id INT IDENTITY, PersonId Int, IsBaseline Int, Visus Int, VisusType Int)
INSRT INTO @t (PersonId, IsBaseLine, Visus, VisusType)
SELECT PersonId, IsBaseLine, H_BAS_Visus, 1 WHERE H_BAS_Visus IS NOT NULL;
INSRT INTO @t (PersonId, IsBaseLine, Visus, VisusType)
SELECT PersonId, IsBaseLine, V_BAS_Visus, 2 WHERE V_BAS_Visus IS NOT NULL;
INSRT INTO @t (PersonId, IsBaseLine, Visus, VisusType)
SELECT PersonId, IsBaseLine, H_FU_Visus, 3 WHERE H_FU_Visus IS NOT NULL;
INSRT INTO @t (PersonId, IsBaseLine, Visus, VisusType)
SELECT PersonId, IsBaseLine, V_FU_Visus, 4 WHERE V_FU_Visus IS NOT NULL;
SElECT * FROM @t ORDER BY PersonId, VisusType;
Set Operators - UNION
Concatenates the results of two queries into a single result set. You control whether the result set includes duplicate rows:
- UNION ALL - Includes duplicates.
- UNION - Excludes duplicates.
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/set-operators-union-transact-sql?view=sql-server-ver15
When transforming columns to rows you DO NOT want to loose data in the process,
hence you do not want/need to use UNION which can exclude some rows.
You should use UNION ALL which will not remove any data from the result.
But your example is not just transposing, you want to aggregate individual colums into one single colum.
The question is now if this is a single task to change the table structure or a recuring task, you need a query for.