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.
| Author |
Topic |
|
drmohamedbekheit
Starting Member
5 Posts |
 Posted - 2014-11-05 : 15:34:26
|
| Dear AllI 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
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-05 : 17:02:04
|
| Can you please post your table schema along with a few rows showing before and after stacking into one row? |
 |
|
|
drmohamedbekheit
Starting Member
5 Posts |
Posted - 2014-11-06 : 01:59:51
|
| Thanks for reply GbrittonNom Poids date Hour Time before or after Day Na mmol/L K mmol/L Cl mmol/LIFLOW001 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 partyou will find multiple rows with iflow001 and iflow002along 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 |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-06 : 12:47:53
|
| 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. |
|
|
|
drmohamedbekheit
Starting Member
5 Posts |
Posted - 2014-11-07 : 02:29:37
|
| 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 |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-07 : 08:48:59
|
| 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. |
|
|
|
drmohamedbekheit
Starting Member
5 Posts |
Posted - 2014-11-07 : 09:02:22
|
| No they are only 4 for each iflow (experiments set). only J-7, J0, J3 and J7 are allowed. |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-11-07 : 10:19:57
|
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 dmyinsert 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 @tselect 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 @tgroup by Nomorder by Nom |
|
|
|
|
|
|
|
|
Multiple records with the same ID, unify into one