March 1, 2013 at 8:55 pm
Hi,
I have a table containing medical test results (as integer with 3 values: 0,1,2) like this :
[PATIENT_ID],[Name],...,[T1],[T2],[T3]...... (the T? are test names (in abbreviations) dynamically added to table)
------------------------------------------------
01, John,.....,1,2,0,.....
02, Jake,.....,0,2,0,.....
03, Joe,.....,2,2,2,.....
04, Jane,.....,1,2,1,.....
..........
Another table includes information about each test :
.....,[TEST_DESC],,......
-----------------------------------
.....,Test name 1,T1,..........
.....,Test name 2,T2,..........
.....,Test name 3,T3,..........
..........................
Each time one test introduces to system, one row is added to second table and one column to first table with a default value.
Now, I need to make a group report for tests. To do so, first I need to change it to this format :
[PATIENT_ID],[Name],[TEST_DESC],[RESULT]
-----------------------------------------------------
01,John,Test name 1,1
01,John,Test name 2,2
01,John,Test name 3,0
..........................
02, Jake,Test name 1,0
02, Jake,Test name 2,2
02, Jake,Test name 3,0
...........................
Any suggestion will be appreciated.
March 1, 2013 at 10:04 pm
No answer in 14 hours. Take a look at the article at the first link in my signature below for a way to change that.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 1, 2013 at 10:29 pm
Dear Jeff,
Thank for the link and guidance. I opened this topic about 2 hours ago. As here is midnight, I will come back tomorrow and modify my topic.
Thanks again
March 1, 2013 at 10:51 pm
Jeff Moden (3/1/2013)
No answer in 14 hours. Take a look at the article at the first link in my signature below for a way to change that.
Hi again,
Following kind guidance of Jeff. I modify my question. So, I have two tables.
-- First table includes test results (T? columns will be dynamically changed)
DECLARE @TABLE1 TABLE(P_ID VARCHAR(2), P_NAME VARCHAR(10), T1 INT, T2 INT, T3 INT)
INSERT INTO @TABLE1 (P_ID, P_NAME, T1, T2, T3) VALUES ('01','John',1,2,0)
INSERT INTO @TABLE1 (P_ID, P_NAME, T1, T2, T3) VALUES ('02','Jake',2,0,2)
INSERT INTO @TABLE1 (P_ID, P_NAME, T1, T2, T3) VALUES ('03','Joe',0,1,2)
--Second table contains definitions and parameter information for each test
DECLARE @TABLE2 TABLE(T_DESC VARCHAR(20), T_ABB VARCHAR(3) )
INSERT INTO @TABLE2 (T_DESC, T_ABB) VALUES ('Test1', 'T1')
INSERT INTO @TABLE2 (T_DESC, T_ABB) VALUES ('Test2', 'T2')
INSERT INTO @TABLE2 (T_DESC, T_ABB) VALUES ('Test3', 'T3')
Each time one test introduces to system, one row is added to second table and one column to first table with a default value.
Now, I need to make a group report for tests. To do so, first I need to change it to this format :
01,John,Test1,1
01,John,Test2,2
01,John,Test3,0
02,Jake,Test1,2
02,Jake,Test2,0
02,Jake,Test3,2
03,Joe,Test1,0
03,Joe,Test2,1
03,Joe,Test3,2
Just to mention again. In my database the number of T? columns in @TABLE1 is variable and they dynamically change.
Thanks in advance
March 2, 2013 at 12:40 am
You may want to read the following articles:
March 2, 2013 at 8:38 am
Lynn Pettis (3/2/2013)
You may want to read the following articles:
Dear Lynn,
Thanks for guidance. I read them and the articles gave me some idea. However, my main issue is how to manage the dynamic part of table.
If you can give me any advice, I appreciate.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy