Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Multiple records with the same ID, unify into one
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

drmohamedbekheit
Starting Member

5 Posts

Posted - 11/05/2014 :  15:34:26  Show Profile  Reply with Quote
Dear All
I have an sql database, which is based online. In that one, I have several rows for the same ID, which contain different data in each.

First, I need to do repeated measures analysis, for which I need that the measures be in the same record (row). what syntax I might use to help me stacking up the different measures into one row instead of multiple.
Ofcourse I will have to create more variables with different names to avoid duplicates (is not it so?)

Best regards

gbritton
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 11/05/2014 :  17:02:04  Show Profile  Reply with Quote
Can you please post your table schema along with a few rows showing before and after stacking into one row?
Go to Top of Page

drmohamedbekheit
Starting Member

5 Posts

Posted - 11/06/2014 :  01:59:51  Show Profile  Reply with Quote
Thanks for reply Gbritton
Nom Poids date Hour Time before or after Day Na mmol/L K mmol/L Cl mmol/L
IFLOW001  40  03/02/2014  00:00:00  before  J-7  142 5.6  99 
IFLOW001  40  17/02/2014  00:00:00  after  J7  137  5.4  101 
IFLOW001  40  13/02/2014  00:00:00  after  J3  143  4.2  107 
IFLOW001  40  10/02/2014  00:00:00  after J0  133  5.3  105 
IFLOW002  44  18/02/2014  00:00:00  after  J0  144  6  115 
IFLOW002  44  11/02/2014  00:00:00  before  J-7  137  4.8  99 

This is the important part
you will find multiple rows with iflow001 and iflow002
along with different values for each variable. these measures take place at different time points defined by the Day and the before and after variables. There are many other variables and records, that is why I need to do this in a non-manual fashion.

I do not know how to make them appear
Iflow001 Na mmol/L(J-7) K mmol/l (J-7) Cl mmol/L(J-7) , Na mmol/L(J0) K mmol/L(J0) Cl mmol/L(J0) and so on... I think I have to manually prepare another table with the fields names then using an intelligent select syntax which I need to know how should it be?

Please do not hesitate to ask more questions, if needed.
I hope that what I am asking for is possible.
Best regards
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 11/06/2014 :  12:47:53  Show Profile  Reply with Quote
Are the identifiers J7, J3, J0, J-7 etc. fixed by the design or may there be an indeterminate number of them? If not fixed, we'll need a dynamic sql solution.
Go to Top of Page

drmohamedbekheit
Starting Member

5 Posts

Posted - 11/07/2014 :  02:29:37  Show Profile  Reply with Quote
At the moment (in the current SQL web based database), these identifiers are of key value to know the time in which each set of analysis was performed. This means that they act as date for example (since each row with the same name represents a different time point of the same variables except of course those who define the time of the experiment).

They are in a single column (the J7,,,,,) if this is what you are asking for.

Regards
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 11/07/2014 :  08:48:59  Show Profile  Reply with Quote
I wasn't asking of those values (j7,...) are in a single column. I'm asking if the values themselves are from a fixed set. That is, today you have J7, might you have J8 tomorrow, or are no other values allowed? The reason for the question is that if the values are known,we can write a simple query that pivots the data. If they are unknown or may grow, we need a dynamic query to do it.
Go to Top of Page

drmohamedbekheit
Starting Member

5 Posts

Posted - 11/07/2014 :  09:02:22  Show Profile  Reply with Quote
No they are only 4 for each iflow (experiments set). only J-7, J0, J3 and J7 are allowed.
Go to Top of Page

gbritton
Flowing Fount of Yak Knowledge

2780 Posts

Posted - 11/07/2014 :  10:19:57  Show Profile  Reply with Quote
OK -- so I mocked up your data and built a query that should give you an idea how to proceed:


declare @t table(
	Nom varchar(10), Poids int, [date] date, [Hour] time, [Time before or after] varchar(10),	
	[Day] char(3), [Na mmol/L] float, [K mmol/L] float, [Cl mmol/L] float)
set dateformat dmy
insert into @t 
	(Nom, Poids, [date], [Hour], [Time before or after], [Day], [Na mmol/L], [K mmol/L], [Cl mmol/L])
	values

('IFLOW001', 	40,	cast('03/02/2014' as date),	cast('00:00:00' as time), 	'before',   'J-7',	 142,	5.6,	99 ),
('IFLOW001', 	40,	cast('17/02/2014' as date),	cast('00:00:00' as time), 	'after ',	'J7 ',	137 ,	5.4,	101),
('IFLOW001', 	40,	cast('13/02/2014' as date),	cast('00:00:00' as time), 	'after ',	'J3 ',	143 ,	4.2,	107),
('IFLOW001', 	40,	cast('10/02/2014' as date),	cast('00:00:00' as time), 	'after ',   'J0 ',	133 ,	5.3,	105),
('IFLOW002', 	44,	cast('18/02/2014' as date),	cast('00:00:00' as time), 	'after ',	'J0 ',	144 ,	6,		115 ),
('IFLOW002', 	44,	cast('11/02/2014' as date),	cast('00:00:00' as time), 	'before', 	'J-7',	137 ,	4.8,	99 )

select * from @t

select Nom
	, sum(case when [day] = 'J-7' then [Na mmol/L] end) as [Na mmol/L(J-7)]
	, sum(case when [day] = 'J-7' then [K mmol/L]  end) as [K mmol/L(J-7)]
	, sum(case when [day] = 'J-7' then [Cl mmol/L] end) as [Cl mmol/L(J-7)]
	, sum(case when [day] = 'J7' then [Na mmol/L] end) as [Na mmol/L(J7)]
	, sum(case when [day] = 'J7' then [K mmol/L]  end) as [K mmol/L(J7)]
	, sum(case when [day] = 'J7' then [Cl mmol/L] end) as [Cl mmol/L(J7)]
	, sum(case when [day] = 'J3' then [Na mmol/L] end) as [Na mmol/L(J3)]
	, sum(case when [day] = 'J3' then [K mmol/L]  end) as [K mmol/L(J3)]
	, sum(case when [day] = 'J3' then [Cl mmol/L] end) as [Cl mmol/L(J3)]
	, sum(case when [day] = 'J0' then [Na mmol/L] end) as [Na mmol/L(J0)]
	, sum(case when [day] = 'J0' then [K mmol/L]  end) as [K mmol/L(J0)]
	, sum(case when [day] = 'J0' then [Cl mmol/L] end) as [Cl mmol/L(J0)]
from @t
group by Nom
order by Nom
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2019 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000