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