Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to make a Pivote table Expand / Collapse
Author
Message
Posted Friday, March 1, 2013 8:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, May 4, 2013 7:55 AM
Points: 42, Visits: 74
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],[ABBR],......
-----------------------------------
.....,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.

Post #1425811
Posted Friday, March 1, 2013 10:04 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 1:53 PM
Points: 35,366, Visits: 31,905
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1425816
Posted Friday, March 1, 2013 10:29 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, May 4, 2013 7:55 AM
Points: 42, Visits: 74
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
Post #1425818
Posted Friday, March 1, 2013 10:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, May 4, 2013 7:55 AM
Points: 42, Visits: 74
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

Post #1425819
Posted Saturday, March 2, 2013 12:40 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 3:27 PM
Points: 20,734, Visits: 32,505
You may want to read the following articles:

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




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1425831
Posted Saturday, March 2, 2013 8:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, May 4, 2013 7:55 AM
Points: 42, Visits: 74


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.

Post #1425860
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse