SQL Pivot, multiple columns to rows

[squest77]

I am trying to create a table that has the following structure:

SSN,
ICD9

From a table with the following structure:

SSN,
ICD9_1,
ICD9_2,
...,
ICD9_8

The only way I can see accomplishing this now is by using a massive union statement:

SELECT
DISTINCT SSN, ICD9 FROM (
SELECT SSN, ICD9_1 AS ICD9 FROM FAC_TEST UNION
SELECT SSN, ICD9_2 AS ICD9 FROM FAC_TEST UNION
SELECT SSN, ICD9_3 AS ICD9 FROM FAC_TEST UNION
SELECT SSN, ICD9_4 AS ICD9 FROM FAC_TEST UNION
SELECT SSN, ICD9_5 AS ICD9 FROM FAC_TEST UNION
SELECT SSN, ICD9_6 AS ICD9 FROM FAC_TEST UNION
SELECT SSN, ICD9_7 AS ICD9 FROM FAC_TEST UNION
SELECT SSN, ICD9_8 AS ICD9 FROM FAC_TEST) AS TEST;

However the actual source table for this tasks is massive, and hitting it 8 times would generate some calls from the DBA's.

Any suggestions?

Thanks!

 

[BrianTyler]

This is a similar situation dealt with earlier in this forum. Look at Thread 178-1092937 as an example.

Brian

 

[ddiamond]

squest77,

Thread 178-1092937 shows you how to create a pivot table. I believe you are trying to do the exact opposite. You are starting with a pivot table and trying to create a normalized table.

How massive is the tables? If you are populating a new table from this table, you might want to consider using 8 load statements with 8 cursors instead of unions. The first load statement would use the replace option, and the next seven would use the insert option. The load statement is much faster than the insert statement.

 

[juliane26]

Do you really need UNION or actually UNION ALL ?

Of course, UNION ALL does not avoid the probably 8 tablescans, but it saves heavy sorting afterwards.




Juliane

 

[squest77]

Part of the problem with retrieving the data is the fact that I don't have a ton of information/tools. I know the tables are big, but don't know the specifics. And the normal queries that I run finish in about 6-7 hours time. Of course these probably could be quicker, but we have not say in indexes or anything. And this also limits me to what I can do, basically just pull the data. I have to privileges to write packages or create temporary tables.

Thanks for all your suggestions! I am going to look into the UNION ALL and see if that will help.