Complicated Pivot Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Complicated Pivot Problem

Author

Topic

LaurieCox

158 Posts

Posted - 2010-07-19 : 09:37:11

I have given data structure, sample data and expected results. I am also working on the problem from my end. I am currently reading about the pivot statement.

Anyway I have the following table:


CREATE TABLE #DiagnosisIndividual (
    dx_id int,
    PATID varchar(40),
    Axis int,
    Code varchar(40),
    StartDate DateTime,
    PrimaryDx int,
    ProvID int)

The data in the Code field must be 'pivoted' with each set of rows defined by PATID/Start Date/ProvID creating one row in the #DiagnosisPivot table:


CREATE TABLE #DiagnosisPivot (
    PATID varchar(40) null,
    StartDate datetime null,
    ProvId int,
    primaryDxCode varchar (40) null,
    axis_I_1 varchar(40) null,
    axis_I_2 varchar(40) null,
    axis_I_3 varchar(40) null,
    axis_I_4 varchar(40) null,
    axis_I_5 varchar(40) null,
    axis_II_1 varchar(40) null,
    axis_II_2 varchar(40) null,
    axis_II_3 varchar(40) null,
    axis_II_4 varchar(40) null,
    axis_II_5 varchar(40) null,
    axis_III_1 varchar(40) null,
    axis_III_2 varchar(40) null,
    axis_III_3 varchar(40) null,
    axis_III_4 varchar(40) null,
    axis_III_5 varchar(40) null,
    ISC1_axis_IV char(1),
    ISC2_axis_IV char(1),
    ISC3_axis_IV char(1),
    ISC4_axis_IV char(1),
    ISC5_axis_IV char(1),
    ISC6_axis_IV char(1),
    ISC7_axis_IV char(1),
    ISC8_axis_IV char(1),
    ISC9_axis_IV char(1),
    axis_V_GAF_score int)

With the following rules

For each Axis type:

If Axis = 1 Code goes into the axis_I_x fields.

If Axis = 2 Code goes into the axis_II_x fields.

If Axis = 3 Code goes into the axis_III_x fields.

I am guaranteed that there will be no more than five codes each for axis 1 thru 3.

If Axis = 4 then the corresponding ISCx_axis_IV (from the code value) field is set to Y. (i.e. if Code = ISC9 then ISC9_axis_IV = Y)

Note: if there is not a corresponding ISCx field then the field should default to N

If Axis = 5 (there should always only be one of these) the axis_V_GAF_score is set to code.

There will be only one row for a given PATID where primaryDx = 1. The value in Code field should go into the primaryDxCode field (as well as into the correct Axis field as defined above).

So using this data:


INSERT INTO #DiagnosisIndividual
SELECT 3220194, 20902, 1, '296.40', '7/14/2010', 1, 3436 UNION ALL     
SELECT 3220192, 20902, 1, '303.90', '7/14/2010', 0, 3436 UNION ALL     
SELECT 3220195, 20902, 1, '296.46', '7/14/2010', 0, 3436 UNION ALL     
SELECT 3220193, 20902, 3, '401', '7/14/2010', 0, 3436 UNION ALL     
SELECT 3220200, 20902, 4, 'ISC6', '7/14/2010', 0, 3436 UNION ALL     
SELECT 3220198, 20902, 4, 'ISC9', '7/14/2010', 0, 3436 UNION ALL     
SELECT 3220199, 20902, 5, '45', '7/14/2010', 0, 3436 UNION ALL     
SELECT 3075861, 23775, 1, '296.90', '6/15/2010', 0, 2891 UNION ALL     
SELECT 3075862, 23775, 2, '317', '6/15/2010', 1, 2891 UNION ALL     
SELECT 3075850, 23775, 2, '389', '6/15/2010', 0, 2891 UNION ALL
SELECT 3075843, 23775, 3, 'V10.82', '6/15/2010', 0, 2891 UNION ALL     
SELECT 3075844, 23775, 3, '244.9', '6/15/2010', 0, 2891 UNION ALL     
SELECT 3075852, 23775, 3, '758.0', '6/15/2010', 0, 2891 UNION ALL     
SELECT 3075877, 23775, 3, '366.5', '6/15/2010', 0, 2891 UNION ALL     
SELECT 3075847, 23775, 3, '525.19', '6/15/2010', 0, 2891 UNION ALL     
SELECT 3075882, 23775, 4, 'ISC5', '6/15/2010', 0, 2891 UNION ALL     
SELECT 3075853, 23775, 4, 'ISC9', '6/15/2010', 0, 2891 UNION ALL     
SELECT 3075858, 23775, 5, '50', '6/15/2010', 0, 2891

There should be two rows generated in #DiagnosisPivot.


Column            Row 1        Row 2
============      ==========   ============
PATID             20902        23775
StartDate         7/14/2010    6/15/2010
ProvId            3436         2891
primaryDxCode     296.40       317
axis_I_1          296.40       296.90
axis_I_2          303.90            
axis_I_3          296.46            
axis_I_4                              
axis_I_5                              
axis_II_1                      317
axis_II_2                      389
axis_II_3                            
axis_II_4                              
axis_II_5                              
axis_III_1        401          V10.82
axis_III_2                     244.9, 
axis_III_3                     758.0, 
axis_III_4                     366.5, 
axis_III_5                     525.19        
ISC1_axis_IV      N            N  
ISC2_axis_IV      N            N  
ISC3_axis_IV      N            N  
ISC4_axis_IV      N            N  
ISC5_axis_IV      N            Y           
ISC6_axis_IV      Y            N  
ISC7_axis_IV      N            N  
ISC8_axis_IV      N            N  
ISC9_axis_IV      Y            Y           
axis_V_GAF_score  45           50

I am not even sure if it is desirable to do this in one query. It may be that I should treat each axis as a separate query into individual tables and then somehow merge the rows into one table.

Thanks,

Laurie

LaurieCox

158 Posts

Posted - 2010-07-19 : 15:50:30

After spending the day reading about pivots, I think that the pivot won't help me.

From what I can see the data that you pivot on becomes column names in the result table.

So if I had data that looked like this:


PATID    Axis   Code
1000     1      x
1000     2      y
1000     3      z
2000     1      a
2000     2      b
2000     3      c

I could create a query that looks like this:


SELECT PATID,  
    [1] AS Axis1, 
    [2] AS Axis2, 
    [3] AS Axis3 
FROM 
    (SELECT PATID, Axis, Code
        FROM #SimplePivot) s 
PIVOT 
( 
   max(Code)
    FOR Axis IN ([1],[2],[3]) 
) p 
ORDER BY [PATID]

To create output that looks like this:


PATID   Axis1    Axis2   Axis 3
1000    x        y       z
2000    a        b       c

And if you have multiple Axis x values for a given PATID it uses whatever aggregate function in the Pivot clause to give one result.

But this is not what I want to do at all.

Here is a simplified description of the problem (working only with one Axis):

Table definition and source data:


CREATE TABLE #DiagnosisIndividual (
    dx_id int,
    PATID varchar(40),
    Axis int,
    Code varchar(40),
    StartDate DateTime,
    PrimaryDx int,
    ProvID int)

INSERT INTO #DiagnosisIndividual
SELECT 3220194, 20902, 1, '296.40', '7/14/2010', 1, 3436 UNION ALL     
SELECT 3220192, 20902, 1, '303.90', '7/14/2010', 0, 3436 UNION ALL     
SELECT 3220195, 20902, 1, '296.46', '7/14/2010', 0, 3436 UNION ALL     
SELECT 3075861, 23775, 1, '296.90', '6/15/2010', 0, 2891

Output table definition:


CREATE TABLE #DiagnosisPivot (
    PATID varchar(40) null,
    StartDate datetime null,
    ProvId int,
    primaryDxCode varchar (40) null,
    axis_I_1 varchar(40) null,
    axis_I_2 varchar(40) null,
    axis_I_3 varchar(40) null,
    axis_I_4 varchar(40) null,
    axis_I_5 varchar(40) null)

Expected results:


Column            Row 1        Row 2
============      ==========   ============
PATID             20902        23775
StartDate         7/14/2010    6/15/2010
ProvId            3436         2891
axis_I_1          296.40       296.90
axis_I_2          303.90            
axis_I_3          296.46            
axis_I_4                              
axis_I_5

Is that doable?

Thanks,

Laurie

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-20 : 10:07:18

look into http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx and also dynamic_pivot. I believe it is possible.

If you don't have the passion to help people, you have no passion

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-07-20 : 10:48:32

For version 2005 onwards use this
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

LaurieCox

158 Posts

Posted - 2010-07-20 : 12:51:49

Thanks yosiasz and Madhivanan for the links

But now after reviewing both pages, I am even more convinced that my problem can't be solved with pivot.

From Dynamic Cross-Tabs/Pivot Tables:

quote:

The pivot column must be in the table (4). You can use an expression for the pivot column (a+b, LEFT(FirstName,3), etc.) as long as it can be derived from the table listed in (4). A cross-tab heading will be created for each distinct value in the pivot colum/expression.

In my case the Pivot column is NOT in the table. Because of this I have created another topic not misdirected with the pivot keyword.

Thanks again for your responses,

Laurie

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-07-20 : 13:08:11

next method would be to loop through temp table and populate accordingly. it seemed like a good pivot fit. in fact eventually you might have to that after you loop through table. I tried pivot for hours but to no avail

If you don't have the passion to help people, you have no passion

LaurieCox

158 Posts

Posted - 2010-07-21 : 16:04:54

yosiasz,

Thanks for your input and time. It looks like Transact Charlie has come up with a solution for a simplified version of the problem in my other thread.

I have not had time to play with it yet. Also, as he said, I am not sure if the solution should be done in sql.

Thanks again,

Laurie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-07-22 : 04:31:46

Slightly harder........ but essentially the same thing


SELECT
	PATID          
	, StartDate      
	, ProvId         
	, MAX(CASE WHEN PrimaryDx = 1 THEN code ELSE NULL END) AS [primaryDxCode]

	, MAX(CASE WHEN axis = 1 AND rowPos = 1 THEN code ELSE '' END) AS [axis_I_1]
	, MAX(CASE WHEN axis = 1 AND rowPos = 2 THEN code ELSE '' END) AS [axis_I_2]
	, MAX(CASE WHEN axis = 1 AND rowPos = 3 THEN code ELSE '' END) AS [axis_I_3]
	, MAX(CASE WHEN axis = 1 AND rowPos = 4 THEN code ELSE '' END) AS [axis_I_4]
	, MAX(CASE WHEN axis = 1 AND rowPos = 5 THEN code ELSE '' END) AS [axis_I_5]

	, MAX(CASE WHEN axis = 2 AND rowPos = 1 THEN code ELSE '' END) AS [axis_II_1]
	, MAX(CASE WHEN axis = 2 AND rowPos = 2 THEN code ELSE '' END) AS [axis_II_2]
	, MAX(CASE WHEN axis = 2 AND rowPos = 3 THEN code ELSE '' END) AS [axis_II_3]
	, MAX(CASE WHEN axis = 2 AND rowPos = 4 THEN code ELSE '' END) AS [axis_II_4]
	, MAX(CASE WHEN axis = 2 AND rowPos = 5 THEN code ELSE '' END) AS [axis_II_5]	

	, MAX(CASE WHEN axis = 3 AND rowPos = 1 THEN code ELSE '' END) AS [axis_III_1]
	, MAX(CASE WHEN axis = 3 AND rowPos = 2 THEN code ELSE '' END) AS [axis_III_2]
	, MAX(CASE WHEN axis = 3 AND rowPos = 3 THEN code ELSE '' END) AS [axis_III_3]
	, MAX(CASE WHEN axis = 3 AND rowPos = 4 THEN code ELSE '' END) AS [axis_III_4]
	, MAX(CASE WHEN axis = 3 AND rowPos = 5 THEN code ELSE '' END) AS [axis_III_5]

	, MAX(CASE WHEN axis = 4 AND code = 'ISC1' THEN 'Y' ELSE 'N' END) AS [ISC1_axis_IV]
	, MAX(CASE WHEN axis = 4 AND code = 'ISC2' THEN 'Y' ELSE 'N' END) AS [ISC2_axis_IV]
	, MAX(CASE WHEN axis = 4 AND code = 'ISC3' THEN 'Y' ELSE 'N' END) AS [ISC3_axis_IV]
	, MAX(CASE WHEN axis = 4 AND code = 'ISC4' THEN 'Y' ELSE 'N' END) AS [ISC4_axis_IV]
	, MAX(CASE WHEN axis = 4 AND code = 'ISC5' THEN 'Y' ELSE 'N' END) AS [ISC5_axis_IV]
	, MAX(CASE WHEN axis = 4 AND code = 'ISC6' THEN 'Y' ELSE 'N' END) AS [ISC6_axis_IV]
	, MAX(CASE WHEN axis = 4 AND code = 'ISC7' THEN 'Y' ELSE 'N' END) AS [ISC7_axis_IV]
	, MAX(CASE WHEN axis = 4 AND code = 'ISC8' THEN 'Y' ELSE 'N' END) AS [ISC8_axis_IV]
	, MAX(CASE WHEN axis = 4 AND code = 'ISC9' THEN 'Y' ELSE 'N' END) AS [ISC9_axis_IV]

	, MAX(CASE WHEN axis = 5 THEN code ELSE '' END) AS [axis_V_GAF_score]
FROM
	(
	SELECT
		PATID          
		, StartDate      
		, ProvId
		, PrimaryDx
		, code
		, axis
		, ROW_NUMBER() OVER (
				PARTITION BY [ProvID], [axis]
				ORDER BY
					CASE PrimaryDx WHEN 1 THEN 0 ELSE 1 END
					, [dx_id]
				)
				AS [rowPos]			
	FROM
		#DiagnosisIndividual
	)
	raw_data
GROUP BY
	PATID          
	, StartDate      
	, ProvId

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

LaurieCox

158 Posts

Posted - 2010-07-22 : 11:14:15

Wow, that's cool.

Thanks,

Laurie

LaurieCox

158 Posts

Posted - 2010-07-23 : 10:40:23

Hi Charlie,

The code is working great though I did find one typo in your solution:

quote:
Originally posted by Transact Charlie

[snip]
PARTITION BY [ProvID], [axis]
[/snip]

Should be PARTITION BY [PATID], [axis]

It was causing weird output like this when I ran it over the whole set of data:

[CODE]
PATID axis_I_1 axis_I_2 axis_I_3 axis_I_4 ...
10333 [NULL] [NULL] 296.90 [NULL]
[/CODE]

It didn't cause a problem in the test data as each PATID had a different ProvID where as in the actual data this was not the case. So it got confused about which "row" it was processing.

Anyway i fixed the typo and it works across all data.

Thanks again for your help,

Laurie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources