Post reply

How to make a Pivote table

  • March 1, 2013 at 8:55 pm

    #394295

    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

    #1592644

    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

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 1, 2013 at 10:29 pm

    #1592646

    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

    #1592647

    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

    #1592658

    You may want to read the following articles:

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    http://www.sqlservercentral.com/articles/Crosstab/65048/

  • March 2, 2013 at 8:38 am

    #1592680

    Lynn Pettis (3/2/2013)

    You may want to read the following articles:

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    http://www.sqlservercentral.com/articles/Crosstab/65048/

    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