I have appointments and the results from each appointment for 1 unique UID number per person that run vertically in the database. I need a SQL command to move all data up ont to 1 line based off the UID number; creating a time line for the appointments
Database looks like:
UID DATE WEIGHT
1 02/03/04 50
1 03/04/06 60
1 04/05/07 70
2 07/08/09 80
2 09/10/11 100
5 11/02/15 150
5 12/04/14 130
5 17/02/03 80
5 30/05/05 90
The way I need the above data to look for querying purposes is:
UID DATE WEIGHT DATE WEIGHT DATE WEIGHT
1 02/03/04 50 03/04/06 60 04/05/07 70
2 07/08/09 80 09/10/11 100 11/02/15 150
5 12/04/14 130 17/02/03 80 30/05/05 90....
This is a very small example as I work with thousands of records with up to 56 variables.
Does anyone have any ideas?
I am working with an SQL database (created around 2009). I am not sure of anything else, the programmer left no information.
Thanks for your time
Penny
Here is one way of writing the query. You have to repeat the pair of columns as many times as the maximum number of expected rows for any given UID. So it is not very scalable if it ends up that you need 57 variables, and had planned only for 56. You will have to add to the query then.
;WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY uid ORDER BY date) AS RN
FROM
YourTable
)
SELECT
uid,
MAX(CASE WHEN RN = 1 THEN Date END) AS Date,
MAX(CASE WHEN RN = 1 THEN Weight END) AS Weight,
MAX(CASE WHEN RN = 2 THEN Date END) AS Date,
MAX(CASE WHEN RN = 2 THEN Weight END) AS Weight,
MAX(CASE WHEN RN = 3 THEN Date END) AS Date,
MAX(CASE WHEN RN = 3 THEN Weight END) AS Weight
FROM
cte
GROUP BY
Uid;
Hi James, sadly the command didn’t work. Could you please take a look and see if I set it up right. I entered it into the command window exactly how it looks below. I am stepping outside my knowledge level with this one.
Error in query (1065): Query was empty
;WITH cte AS
(SELECT *, ROW_NUMBER() OVER (PARTITION BY internalid ORDER BY date) AS RN FROM visit_appointments)
SELECT internalid,
MAX(CASE WHEN RN = 1 THEN Date END) AS date,
MAX(CASE WHEN RN = 1 THEN Weight END) AS weight,
MAX(CASE WHEN RN = 2 THEN Date END) AS date, MAX(CASE WHEN RN = 2 THEN Weight END) AS weight,
MAX(CASE WHEN RN = 3 THEN Date END) AS date,
MAX(CASE WHEN RN = 3 THEN Weight END) AS weight
FROM
cte
GROUP BY
internalid
Error in query (1064): Syntax error near 'WITH cte AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY internalid ORDER BY date' at line 1
Error in query: 1 2
;WITH cte AS
(SELECT *, ROW_NUMBER() OVER (PARTITION BY internalid ORDER BY date) AS RN
FROM visit_appointments)
SELECT
internalid,
MAX(CASE WHEN RN = 1 THEN Date END) AS Date
MAX(CASE WHEN RN = 1 THEN Weight END) AS weight,
MAX(CASE WHEN RN = 2 THEN Date END) AS date, MAX(CASE WHEN RN = 2 THEN Weight END) AS weight,
MAX(CASE WHEN RN = 3 THEN Date END) AS date,
MAX(CASE WHEN RN = 3 THEN Weight END) AS weight
FROM
cte
GROUP BY
internalid;
