unpivot with a twist ... - 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)

unpivot with a twist ...

Author

Topic

LaurieCox

158 Posts

Posted - 2010-09-08 : 12:06:21

I have managed to modify my code into a union of two unpivot queries (see second post). I would appreciate any constructive criticism of my solution.

I have a table that looks like this:


CREATE TABLE #DiagHistory(
             [PATID] [varchar](20) NULL,
             [PrimaryDxCode] [varchar](50) NULL,
             [EPISODE_NUMBER] [int] NULL,
             [StaffId] [varchar](20) NULL,
             [date_of_diagnosis] [datetime] NULL,
             [axis_I_diag_1] [varchar](50) NULL,
             [axis_I_diag_2] [varchar](50) NULL,
             [axis_I_diag_3] [varchar](50) NULL,
             [axis_II_diag_1] [varchar](50) NULL,
             [axis_II_diag_2] [varchar](50) NULL,
             [axis_II_diag_3] [varchar](50) NULL,
             [axis_III_diag_1] [varchar](50) NULL,
             [axis_III_diag_2] [varchar](50) NULL,
             [axis_III_diag_3] [varchar](50) NULL,
             [axis_IV_prim_sup_grp] [varchar](5) NULL,
             [axis_IV_soc_env] [varchar](5) NULL,
             [axis_IV_educ] [varchar](5) NULL,
             [axis_IV_occ] [varchar](5) NULL,
             [axis_IV_housing] [varchar](5) NULL,
             [axis_IV_eco] [varchar](5) NULL,
             [axis_IV_health_serv] [varchar](5) NULL,
             [axis_IV_legsys] [varchar](5) NULL,
             [axis_IV_other_prob] [varchar](5) NULL,
             [axis_V_gaf] [varchar](5) NULL
            )  
INSERT INTO  #DiagHistory
SELECT '1716','317',5,'008763','10/15/2008',
       'V71.09',NULL,NULL,
       '317',NULL,NULL,
       'V42.0','389.00','996.64',
       'N','N','Y','Y','N','Y','N','N','N',NULL UNION ALL
SELECT '1769','304.80',11,'001208','08/06/2009',
       '304.80','300.00',NULL,
       '301.7',NULL,NULL,
       NULL,NULL,NULL,
       'Y','Y','Y','Y','Y','Y','Y','Y',NULL,'56' UNION ALL
SELECT '20058','296.90',1,'035619','09/22/2009',
       '296.90',NULL,NULL,
       '799.9',NULL,NULL,
       NULL,NULL,NULL,
       'Y',NULL,'N',NULL,'N',NULL,'Y',NULL,'N','5'

I want to unpivot the data into this table:


CREATE TABLE #HistDiag_Unpivot(
	      [PATID] [varchar](20) NULL,
	      [date_of_diagnosis] [datetime] NULL,
	      [EPISODE_NUMBER] [int] NULL,
	      [StaffId] [varchar](20) NULL,
	      [PrimaryDx] [int]  NULL,
	      [Axis] [int] NULL,
	      [Code] [varchar](50) NULL
            )

I am able to unpivot the axis_X_diag_X columns:


insert into #HistDiag_Unpivot
select PATID
       , date_of_diagnosis
       , EPISODE_NUMBER 
       , StaffId
       , case when PrimaryDxCode = AxisCode then 1 
              else 0
         end as PrimaryDx
       , CASE when substring(pvt.AxisType,1,7) = 'axis_I_' then 1
              when substring(pvt.AxisType,1,8) = 'axis_II_' then 2
              when substring(pvt.AxisType,1,9) = 'axis_III_' then 3
         END as Axis
       , pvt.AxisCode as Code
  from #DiagHistory h
unpivot (AxisCode for AxisType in (axis_I_diag_1
				, axis_I_diag_2
				, axis_I_diag_3
				, axis_II_diag_1
				, axis_II_diag_2
				, axis_II_diag_3
				, axis_III_diag_1
				, axis_III_diag_2
				, axis_III_diag_3
		)) as pvt

But I am having problems with the Axis IV and V columns. When I add them to the AxisType list I get this error:

quote:

The type of column "axis_IV_prim_sup_grp" conflicts with the type of other columns specified in the UNPIVOT list.

This makes sense as they are defined as varchar(5) where the others are varchar(50).

There is a further hiccup to the Axis IV's. These columns in the original table can have a value of Y, N or Null. I only want to create a row in the unpivot table if the value is Y. But I do not want the code column in the unpivot table to be Y. The code depends on the column name:


Column Name             Code
====================    ====
axis_IV_prim_sup_grp    ISC1
axis_IV_soc_env         ISC2
axis_IV_educ            ISC3
axis_IV_occ             ISC4
axis_IV_housing         ISC5
axis_IV_eco             ISC6
axis_IV_health_serv     ISC7
axis_IV_legsys          ISC8
axis_IV_other_prob      ISC9

So, I have solved this problem by adding a series of insert statements:


insert into #HistDiag_Unpivot
select PATID
       , date_of_diagnosis
       , EPISODE_NUMBER
       , StaffId
       , 0 as PrimaryDx -- Axis IV's will never be primary
       , 4 as Axis
       , 'ISC1' as Code 
  from #DiagHistory 
 where axis_IV_prim_sup_grp = 'Y'
UNION ALL
select PATID
       , date_of_diagnosis
       , EPISODE_NUMBER
       , StaffId
       , 0 as PrimaryDx
       , 4 as Axis
       , 'ISC2' as Code 
  from #DiagHistory 
 where axis_IV_soc_env = 'Y'
 --  <snip> ... for the rest of the axis IV's </snip>
UNION ALL
select PATID
       , date_of_diagnosis
       , EPISODE_NUMBER
       , StaffId
       , 0 as PrimaryDx -- Axis V's will never be primary
       , 5 as Axis
       , axis_V_gaf 
  from #DiagHistory 
 where axis_V_gaf is not null

This works, but my question is: Is there a way to do everything in one unpivot statement? And if there is a way is it better than what I am currently doing?

The real table that I am unpivotting has 125278 rows in it.

Thanks,

Laurie

LaurieCox

158 Posts

Posted - 2010-09-09 : 09:36:36

I combined all of my individual select statements for Axis IV and V and came up with this solution:


insert into #HistDiag_Unpivot
select PATID
           , date_of_diagnosis
           , EPISODE_NUMBER
           , StaffId
           , case when PrimaryDx = AxisCode then 1 
                  else 0
             end as PrimaryDx
           , CASE when substring(pvt.AxisType,1,7) = 'axis_I_' then 1
                  when substring(pvt.AxisType,1,8) = 'axis_II_' then 2
                  when substring(pvt.AxisType,1,9) = 'axis_III_' then 3
             END as Axis
           , pvt.AxisCode as Code
  from #DiagHistory h
unpivot (AxisCode for AxisType in (axis_I_diag_1
                                   , axis_I_diag_2
                                   , axis_I_diag_3
                                   , axis_II_diag_1
                                   , axis_II_diag_2
                                   , axis_II_diag_3
                                   , axis_III_diag_1
                                   , axis_III_diag_2
                                   , axis_III_diag_3
                                   )) as pvt

UNION
select * 
  from (select PATID
               , date_of_diagnosis
               , EPISODE_NUMBER
               , StaffId
               , 0 as PrimaryDx
               , CASE when substring(pvt.AxisType,1,8) = 'axis_IV_' then 4
                      when substring(pvt.AxisType,1,7) = 'axis_V_' then 5
                 END as Axis
               , case when AxisCode = 'Y' then
                           case AxisType
                                when 'axis_IV_prim_sup_grp' then 'ISC1'
                                when 'axis_IV_soc_env'  then 'ISC2'
                                when 'axis_IV_educ' then 'ISC3'
                                when 'axis_IV_occ' then 'ISC4'
                                when 'axis_IV_housing'  then 'ISC5'
                                when 'axis_IV_eco'  then 'ISC6'
                                when 'axis_IV_health_serv' then 'ISC7'
                                when 'axis_IV_legsys' then 'ISC8'
                                when 'axis_IV_other_prob' then 'ISC9'
                           end
                     when AxisType = 'axis_V_gaf' then pvt.AxisCode
                 end as Code
          from #DiagHistory h
       unpivot (AxisCode for AxisType in (axis_IV_prim_sup_grp
                                          , axis_IV_soc_env 
                                          , axis_IV_educ
                                          , axis_IV_occ 
                                          , axis_IV_housing 
                                          , axis_IV_eco 
                                          , axis_IV_health_serv
                                          , axis_IV_legsys
                                          , axis_IV_other_prob  
                                          , axis_V_gaf
                                      )) as pvt) as x
 where x.code is not null

I would appreciate any constructive criticism of my solution.

Thanks,

Laurie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources