July 12, 2010 at 8:58 am
To all,
I am in the process of creating my first dynamic cross-tab. I have read all of Jeff Modem's articles and am pretty familiar with creating static cross-tabs. However, I just can't get my head around how to turn this into a dynamic cross-tab. I have a table where I need to split a comma deliminted string into separate rows (creating an id-sequence no) and then apply the associated field with the row. The catch is that each id can have multiple lines, with the comma delimited field having only 8 values, but there can be up to 1200 id-sequence number that will need to be placed in rows.
I have included the setup for a test table, some sample data and the code I have so far. I really want to avoid having to write all of those case statements. The required output is at the bottom.
CREATE TABLE [dbo].[ENC_HIST](
[CLAIM_ID] [varchar](3) NOT NULL,
[CLAIM_SEQUENCE_NUMBERS] [varchar](200) NOT NULL,
[FIRST_DATE_OF_SERVICE_1] [datetime] NULL,
[SERVICE_PROVIDER_NUM_1] [varchar](15) NULL,
[REIMBURSE_IND_1] [varchar](1) NULL,
[AMOUNT_PAID_1] [varchar](9) NULL,
[PROCEDURE_CODE_1] [varchar](7) NULL,
[UNITS_1] [varchar](5) NULL,
[MODIFIER_1] [varchar](3) NULL,
[FIRST_DATE_OF_SERVICE_2] [datetime] NULL,
[SERVICE_PROVIDER_NUM_2] [varchar](15) NULL,
[REIMBURSE_IND_2] [varchar](1) NULL,
[AMOUNT_PAID_2] [varchar](9) NULL,
[PROCEDURE_CODE_2] [varchar](7) NULL,
[UNITS_2] [varchar](5) NULL,
[MODIFIER_2] [varchar](3) NULL,
[FIRST_DATE_OF_SERVICE_3] [datetime] NULL,
[SERVICE_PROVIDER_NUM_3] [varchar](15) NULL,
[REIMBURSE_IND_3] [varchar](1) NULL,
[AMOUNT_PAID_3] [varchar](9) NULL,
[PROCEDURE_CODE_3] [varchar](7) NULL,
[UNITS_3] [varchar](5) NULL,
[MODIFIER_3] [varchar](3) NULL,
[FIRST_DATE_OF_SERVICE_4] [datetime] NULL,
[SERVICE_PROVIDER_NUM_4] [varchar](15) NULL,
[REIMBURSE_IND_4] [varchar](1) NULL,
[AMOUNT_PAID_4] [varchar](9) NULL,
[PROCEDURE_CODE_4] [varchar](7) NULL,
[UNITS_4] [varchar](5) NULL,
[MODIFIER_4] [varchar](3) NULL,
[FIRST_DATE_OF_SERVICE_5] [datetime] NULL,
[SERVICE_PROVIDER_NUM_5] [varchar](15) NULL,
[REIMBURSE_IND_5] [varchar](1) NULL,
[AMOUNT_PAID_5] [varchar](9) NULL,
[PROCEDURE_CODE_5] [varchar](7) NULL,
[UNITS_5] [varchar](5) NULL,
[MODIFIER_5] [varchar](3) NULL,
[FIRST_DATE_OF_SERVICE_6] [datetime] NULL,
[SERVICE_PROVIDER_NUM_6] [varchar](15) NULL,
[REIMBURSE_IND_6] [varchar](1) NULL,
[AMOUNT_PAID_6] [varchar](9) NULL,
[PROCEDURE_CODE_6] [varchar](7) NULL,
[UNITS_6] [varchar](5) NULL,
[MODIFIER_6] [varchar](3) NULL,
[FIRST_DATE_OF_SERVICE_7] [datetime] NULL,
[SERVICE_PROVIDER_NUM_7] [varchar](15) NULL,
[REIMBURSE_IND_7] [varchar](1) NULL,
[AMOUNT_PAID_7] [varchar](9) NULL,
[PROCEDURE_CODE_7] [varchar](7) NULL,
[UNITS_7] [varchar](5) NULL,
[MODIFIER_7] [varchar](3) NULL,
[FIRST_DATE_OF_SERVICE_8] [datetime] NULL,
[SERVICE_PROVIDER_NUM_8] [varchar](15) NULL,
[REIMBURSE_IND_8] [varchar](1) NULL,
[AMOUNT_PAID_8] [varchar](9) NULL,
[PROCEDURE_CODE_8] [varchar](7) NULL,
[UNITS_8] [varchar](5) NULL,
[MODIFIER_8] [varchar](3) NULL,
)
--SAMPLE DATA
--INSERT THE SAMPLE DATA INTO THE TEST TABLE FOR PROCESSING
INSERT INTO ENC_HIST
(
CLAIM_ID,CLAIM_SEQUENCE_NUMBERS,
FIRST_DATE_OF_SERVICE_1,SERVICE_PROVIDER_NUM_1,REIMBURSE_IND_1,AMOUNT_PAID_1,PROCEDURE_CODE_1,UNITS_1,MODIFIER_1,
FIRST_DATE_OF_SERVICE_2,SERVICE_PROVIDER_NUM_2,REIMBURSE_IND_2,AMOUNT_PAID_2,PROCEDURE_CODE_2,UNITS_2,MODIFIER_2,
FIRST_DATE_OF_SERVICE_3,SERVICE_PROVIDER_NUM_3,REIMBURSE_IND_3,AMOUNT_PAID_3 ,PROCEDURE_CODE_3,UNITS_3 ,MODIFIER_3,
FIRST_DATE_OF_SERVICE_4,SERVICE_PROVIDER_NUM_4,REIMBURSE_IND_4,AMOUNT_PAID_4,PROCEDURE_CODE_4,UNITS_4,MODIFIER_4,
FIRST_DATE_OF_SERVICE_5,SERVICE_PROVIDER_NUM_5,REIMBURSE_IND_5,AMOUNT_PAID_5,PROCEDURE_CODE_5,UNITS_5,MODIFIER_5,
FIRST_DATE_OF_SERVICE_6,SERVICE_PROVIDER_NUM_6,REIMBURSE_IND_6,AMOUNT_PAID_6,PROCEDURE_CODE_6,UNITS_6 ,MODIFIER_6,
FIRST_DATE_OF_SERVICE_7,SERVICE_PROVIDER_NUM_7,REIMBURSE_IND_7,AMOUNT_PAID_7,PROCEDURE_CODE_7 ,UNITS_7,MODIFIER_7,
FIRST_DATE_OF_SERVICE_8 ,SERVICE_PROVIDER_NUM_8,REIMBURSE_IND_8,AMOUNT_PAID_8,PROCEDURE_CODE_8,UNITS_8,MODIFIER_8
)
SELECT
'100','1,2,3,4,5,6,7,8',
'2009-01-01 00:00:00','121000','F',6000,'90801',4,NULL,
'2009-01-01 00:00:00','121000','F',5000,'74160',3,NULL,
'2009-01-01 00:00:00','121000','F',3000,'72125',4,NULL,
'2009-01-01 00:00:00','121000','F',70000,'99223',7,NULL ,
'2009-01-01 00:00:00','121000','F',6000,'81025',1,NULL,
'2009-01-01 00:00:00','121000','F',8000,'69210',5,NULL,
'2009-01-01 00:00:00','121000','F',1000,'J2185',1,NULL,
'2009-01-01 00:00:00','121000','F',8000,'31575',7,NULL UNION ALL
SELECT
'100','9,10,11,12,13,14,15,16',
'2009-01-01 00:00:00','121000','F',6000,'90801',4,NULL,
'2009-01-01 00:00:00','121000','F',5000,'74160',3,NULL,
'2009-01-01 00:00:00','121000','F',3000,'72125',4,NULL,
'2009-01-01 00:00:00','121000','F',70000,'99223',7,NULL,
'2009-01-01 00:00:00','121000','F',6000,'81025',1,NULL,
'2009-01-01 00:00:00','121000','F',8000,'69210',5,NULL,
'2009-01-01 00:00:00','121000','F',1000,'J2185',1,NULL,
'2009-01-01 00:00:00','121000','F',8000,'31575',7,NULL UNION ALL
SELECT
'101','1,2,3,4',
'2009-11-21 00:00:00','122000','C',0,'90801',1,NULL,
'2009-11-21 00:00:00','122000','F',500000,'A7015',3,NULL,
'2009-11-21 00:00:00','122000','F',3000,'93306',4,NULL,
'2009-11-21 00:00:00','122000','C',0,'99223',1,NULL,
NULL,NULL,NULL,0,NULL,0,NULL,
NULL,NULL,NULL,0,NULL,0,NULL,
NULL,NULL,NULL,0,NULL,0,NULL,
NULL,NULL,NULL,0,NULL,0,NULL
UNION ALL
SELECT
'103','1,2,3,4,5,6,7,8',
'2009-12-31 00:00:00','123000','F',1000,'90801',4,NULL,
'2009-12-31 00:00:00','123000','F',2000,'74160',3,NULL,
'2009-12-31 00:00:00','123000','F',3000,'72125',4,NULL,
'2009-12-31 00:00:00','123000','F',40000,'99223',7,NULL ,
'2009-12-31 00:00:00','123000','F',5000,'81025',1,NULL,
'2009-12-31 00:00:00','123000','F',6000,'69210',5,NULL,
'2009-12-31 00:00:00','123000','F',8000,'J2185',1,NULL,
'2009-12-31 00:00:00','123000','F',10000,'31575',7,NULL UNION ALL
SELECT
'103','9,10,11,12,13,14,15,16',
'2009-12-31 00:00:00','123000','F',6000,'99204',1,NULL,
'2009-12-31 00:00:00','123000','F',5000,'93306',1,NULL,
'2009-12-31 00:00:00','123000','F',3000,'99244',1,NULL,
'2009-12-31 00:00:00','123000','F',70000,'92507',1,NULL,
'2009-12-31 00:00:00','123000','F',6000,'99212',1,NULL,
'2009-12-31 00:00:00','123000','F',8000,'76856',5,NULL,
'2009-12-31 00:00:00','123000','F',1000,'99243',1,NULL,
'2009-12-31 00:00:00','123000','F',8000,'81003',2,NULL UNION ALL
SELECT
'103','17,18,19,20,21,22,23,24',
'2009-12-31 00:00:00','123000','F',8000,'11040',2,NULL,
'2009-12-31 00:00:00','123000','F',6000,'71020',2,NULL,
'2009-12-31 00:00:00','123000','F',4000,'72125',2,NULL,
'2009-12-31 00:00:00','123000','F',2000,'70553',2,NULL,
'2009-12-31 00:00:00','123000','C',0,'76856',2,NULL,
'2009-12-31 00:00:00','123000','C',0,'69210',4,NULL,
'2009-12-31 00:00:00','123000','C',0,'J2185',6,NULL,
'2009-12-31 00:00:00','123000','C',0,'31575',9,NULL UNION ALL
SELECT
'103','25,26,27,28,29,30,31,32',
'2009-12-31 00:00:00','123000','C',6000,'90801',4,NULL,
'2009-12-31 00:00:00','123000','C',5000,'74160',3,NULL,
'2009-12-31 00:00:00','123000','F',3000,'72125',4,NULL,
'2009-12-31 00:00:00','123000','F',70000,'99223',7,NULL,
'2009-12-31 00:00:00','123000','F',6000,'81025',1,NULL,
'2009-12-31 00:00:00','123000','F',8000,'69210',5,NULL,
'2009-12-31 00:00:00','123000','F',1000,'J2185',1,NULL,
'2009-12-31 00:00:00','123000','F',8000,'31575',7,NULL UNION ALL
SELECT
'103','33,34,35,36,37,38,39,40',
'2009-12-31 00:00:00','121000','F',6000,'V5060',1,NULL,
'2009-12-31 00:00:00','121000','F',5000,'L0630',2,NULL,
'2009-12-31 00:00:00','121000','F',3000,'11042',6,NULL,
'2009-12-31 00:00:00','121000','F',70000,'59430',9,NULL,
'2009-12-31 00:00:00','121000','F',6000,'51700',1,NULL,
'2009-12-31 00:00:00','121000','F',8000,'00918',1,NULL,
'2009-12-31 00:00:00','121000','F',1000,'00440',1,NULL,
'2009-12-31 00:00:00','121000','F',8000,'99123',1,NULL
--QUERY SO FAR (NOTE IS ASSUMES THAT YOU HAVE A TALLY TABLE)
--SELECT THE INDIVIDUAL RECORDS AND ASSIGN THE LINE NUMBER USING THE DATA FILE AND A TALLY TABLE
;with temp_tbl as (
SELECT
CLAIM_ID,
SUBSTRING(','+claim_sequence_numbers+',',N+1,CHARINDEX(',',','+claim_sequence_numbers+',',N+1)-N-1) AS line_no,
FIRST_DATE_OF_SERVICE_1,
SERVICE_PROVIDER_NUM_1,
REIMBURSE_IND_1,
AMOUNT_PAID_1,
PROCEDURE_CODE_1,
UNITS_1,
MODIFIER_1,
FIRST_DATE_OF_SERVICE_2,
SERVICE_PROVIDER_NUM_2,
REIMBURSE_IND_2,
AMOUNT_PAID_2,
PROCEDURE_CODE_2,
UNITS_2,
MODIFIER_2,
FIRST_DATE_OF_SERVICE_3,
SERVICE_PROVIDER_NUM_3,
REIMBURSE_IND_3,
AMOUNT_PAID_3,
PROCEDURE_CODE_3,
UNITS_3,
MODIFIER_3,
FIRST_DATE_OF_SERVICE_4,
SERVICE_PROVIDER_NUM_4,
REIMBURSE_IND_4,
AMOUNT_PAID_4,
PROCEDURE_CODE_4,
UNITS_4,
MODIFIER_4,
FIRST_DATE_OF_SERVICE_5,
SERVICE_PROVIDER_NUM_5,
REIMBURSE_IND_5,
AMOUNT_PAID_5,
PROCEDURE_CODE_5,
UNITS_5,
MODIFIER_5,
FIRST_DATE_OF_SERVICE_6,
SERVICE_PROVIDER_NUM_6,
REIMBURSE_IND_6,
AMOUNT_PAID_6,
PROCEDURE_CODE_6,
UNITS_6,
MODIFIER_6,
FIRST_DATE_OF_SERVICE_7,
SERVICE_PROVIDER_NUM_7,
REIMBURSE_IND_7,
AMOUNT_PAID_7,
PROCEDURE_CODE_7,
UNITS_7,
MODIFIER_7,
FIRST_DATE_OF_SERVICE_8,
SERVICE_PROVIDER_NUM_8,
REIMBURSE_IND_8,
AMOUNT_PAID_8,
PROCEDURE_CODE_8,
UNITS_8,
MODIFIER_8
FROM
dbo.tally
cross join
ENC_HIST
WHERE
N < LEN(','+claim_sequence_numbers+',')
AND SUBSTRING(','+claim_sequence_numbers+',',N,1) = ','
)
--MATCH UP THE LINE NUMBER TO DISTINGUISH INDIVIDUAL LINES
, temp_tbl2 as (
SELECT
LINE_NO,
claim_id,
FIRST_DATE_OF_SERVICE_1,
SERVICE_PROVIDER_NUM_1,
REIMBURSE_IND_1,
AMOUNT_PAID_1,
PROCEDURE_CODE_1,
UNITS_1,
MODIFIER_1,
FIRST_DATE_OF_SERVICE_2,
SERVICE_PROVIDER_NUM_2,
REIMBURSE_IND_2,
AMOUNT_PAID_2,
PROCEDURE_CODE_2,
UNITS_2,
MODIFIER_2,
FIRST_DATE_OF_SERVICE_3,
SERVICE_PROVIDER_NUM_3,
REIMBURSE_IND_3,
AMOUNT_PAID_3,
PROCEDURE_CODE_3,
UNITS_3,
MODIFIER_3,
FIRST_DATE_OF_SERVICE_4,
SERVICE_PROVIDER_NUM_4,
REIMBURSE_IND_4,
AMOUNT_PAID_4,
PROCEDURE_CODE_4,
UNITS_4,
MODIFIER_4,
FIRST_DATE_OF_SERVICE_5,
SERVICE_PROVIDER_NUM_5,
REIMBURSE_IND_5,
AMOUNT_PAID_5,
PROCEDURE_CODE_5,
UNITS_5,
MODIFIER_5,
FIRST_DATE_OF_SERVICE_6,
SERVICE_PROVIDER_NUM_6,
REIMBURSE_IND_6,
AMOUNT_PAID_6,
PROCEDURE_CODE_6,
UNITS_6,
MODIFIER_6,
FIRST_DATE_OF_SERVICE_7,
SERVICE_PROVIDER_NUM_7,
REIMBURSE_IND_7,
AMOUNT_PAID_7,
PROCEDURE_CODE_7,
UNITS_7,
MODIFIER_7,
FIRST_DATE_OF_SERVICE_8,
SERVICE_PROVIDER_NUM_8,
REIMBURSE_IND_8,
AMOUNT_PAID_8,
PROCEDURE_CODE_8,
UNITS_8,
MODIFIER_8,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN1') then line_no end as line_1t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN2') then line_no end as line_2t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN3') then line_no end as line_3t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN4') then line_no end as line_4t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN5') then line_no end as line_5t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN6') then line_no end as line_6t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN7') then line_no end as line_7t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN8') then line_no end as line_8t,
Case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN9') then line_no end as line_9t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN10') then line_no end as line_10t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN11') then line_no end as line_11t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN12') then line_no end as line_12t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN13') then line_no end as line_13t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN14') then line_no end as line_14t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN15') then line_no end as line_15t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN16') then line_no end as line_16t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN17') then line_no end as line_17t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN18') then line_no end as line_18t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN19') then line_no end as line_19t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN20') then line_no end as line_20t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN21') then line_no end as line_21t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN22') then line_no end as line_22t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN23') then line_no end as line_23t,
Case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN24') then line_no end as line_24t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN25') then line_no end as line_25t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN26') then line_no end as line_26t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN27') then line_no end as line_27t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN28') then line_no end as line_28t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN29') then line_no end as line_29t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN30') then line_no end as line_30t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN31') then line_no end as line_31t,
case when ('LN'+cast(row_number()over(partition by claim_id order by claim_id)as char(5))='LN32') then line_no end as line_32t
FROM
temp_tbl
)
--PUT THE RIGHT DETAIL FILEDS WITH EACH LINE NUMBER TO CREATE ONE ROW PER CLAIM_ID LINE NUMBER
, TEMP_TBL3 AS (
SELECT
CLAIM_ID,
CASE
WHEN LINE_1T IS NOT NULL THEN LINE_1T
WHEN LINE_2T IS NOT NULL THEN LINE_2T
WHEN LINE_3T IS NOT NULL THEN LINE_3T
WHEN LINE_4T IS NOT NULL THEN LINE_4T
WHEN LINE_5T IS NOT NULL THEN LINE_5T
WHEN LINE_6T IS NOT NULL THEN LINE_6T
WHEN LINE_7T IS NOT NULL THEN LINE_7T
WHEN LINE_8T IS NOT NULL THEN LINE_8T
WHEN LINE_9T IS NOT NULL THEN LINE_9T
WHEN LINE_10T IS NOT NULL THEN LINE_10T
WHEN LINE_11T IS NOT NULL THEN LINE_11T
WHEN LINE_12T IS NOT NULL THEN LINE_12T
WHEN LINE_13T IS NOT NULL THEN LINE_13T
WHEN LINE_14T IS NOT NULL THEN LINE_14T
WHEN LINE_15T IS NOT NULL THEN LINE_15T
WHEN LINE_16T IS NOT NULL THEN LINE_16T
WHEN LINE_17T IS NOT NULL THEN LINE_17T
WHEN LINE_18T IS NOT NULL THEN LINE_18T
WHEN LINE_19T IS NOT NULL THEN LINE_19T
WHEN LINE_20T IS NOT NULL THEN LINE_20T
WHEN LINE_21T IS NOT NULL THEN LINE_21T
WHEN LINE_22T IS NOT NULL THEN LINE_22T
WHEN LINE_23T IS NOT NULL THEN LINE_23T
WHEN LINE_24T IS NOT NULL THEN LINE_24T
WHEN LINE_25T IS NOT NULL THEN LINE_25T
WHEN LINE_26T IS NOT NULL THEN LINE_26T
WHEN LINE_27T IS NOT NULL THEN LINE_27T
WHEN LINE_28T IS NOT NULL THEN LINE_28T
WHEN LINE_29T IS NOT NULL THEN LINE_29T
WHEN LINE_30T IS NOT NULL THEN LINE_30T
WHEN LINE_31T IS NOT NULL THEN LINE_31T
WHEN LINE_32T IS NOT NULL THEN LINE_32T
ELSE NULL END AS SEQ_NO,
CASE
WHEN LINE_1T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_1
WHEN LINE_2T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_2
WHEN LINE_3T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_3
WHEN LINE_4T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_4
WHEN LINE_5T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_5
WHEN LINE_6T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_6
WHEN LINE_7T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_7
WHEN LINE_8T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_8
WHEN LINE_9T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_1
WHEN LINE_10T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_2
WHEN LINE_11T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_3
WHEN LINE_12T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_4
WHEN LINE_13T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_5
WHEN LINE_14T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_6
WHEN LINE_15T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_7
WHEN LINE_16T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_8
WHEN LINE_17T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_1
WHEN LINE_18T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_2
WHEN LINE_19T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_3
WHEN LINE_20T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_4
WHEN LINE_21T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_5
WHEN LINE_22T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_6
WHEN LINE_23T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_7
WHEN LINE_24T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_8
WHEN LINE_25T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_1
WHEN LINE_26T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_2
WHEN LINE_27T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_3
WHEN LINE_28T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_4
WHEN LINE_29T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_5
WHEN LINE_30T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_6
WHEN LINE_31T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_7
WHEN LINE_32T IS NOT NULL THEN FIRST_DATE_OF_SERVICE_8
ELSE NULL END AS DOS,
CASE
WHEN LINE_1T IS NOT NULL THEN SERVICE_PROVIDER_NUM_1
WHEN LINE_2T IS NOT NULL THEN SERVICE_PROVIDER_NUM_2
WHEN LINE_3T IS NOT NULL THEN SERVICE_PROVIDER_NUM_3
WHEN LINE_4T IS NOT NULL THEN SERVICE_PROVIDER_NUM_4
WHEN LINE_5T IS NOT NULL THEN SERVICE_PROVIDER_NUM_5
WHEN LINE_6T IS NOT NULL THEN SERVICE_PROVIDER_NUM_6
WHEN LINE_7T IS NOT NULL THEN SERVICE_PROVIDER_NUM_7
WHEN LINE_8T IS NOT NULL THEN SERVICE_PROVIDER_NUM_8
WHEN LINE_9T IS NOT NULL THEN SERVICE_PROVIDER_NUM_1
WHEN LINE_10T IS NOT NULL THEN SERVICE_PROVIDER_NUM_2
WHEN LINE_11T IS NOT NULL THEN SERVICE_PROVIDER_NUM_3
WHEN LINE_12T IS NOT NULL THEN SERVICE_PROVIDER_NUM_4
WHEN LINE_13T IS NOT NULL THEN SERVICE_PROVIDER_NUM_5
WHEN LINE_14T IS NOT NULL THEN SERVICE_PROVIDER_NUM_6
WHEN LINE_15T IS NOT NULL THEN SERVICE_PROVIDER_NUM_7
WHEN LINE_16T IS NOT NULL THEN SERVICE_PROVIDER_NUM_8
WHEN LINE_17T IS NOT NULL THEN SERVICE_PROVIDER_NUM_1
WHEN LINE_18T IS NOT NULL THEN SERVICE_PROVIDER_NUM_2
WHEN LINE_19T IS NOT NULL THEN SERVICE_PROVIDER_NUM_3
WHEN LINE_20T IS NOT NULL THEN SERVICE_PROVIDER_NUM_4
WHEN LINE_21T IS NOT NULL THEN SERVICE_PROVIDER_NUM_5
WHEN LINE_22T IS NOT NULL THEN SERVICE_PROVIDER_NUM_6
WHEN LINE_23T IS NOT NULL THEN SERVICE_PROVIDER_NUM_7
WHEN LINE_24T IS NOT NULL THEN SERVICE_PROVIDER_NUM_8
WHEN LINE_25T IS NOT NULL THEN SERVICE_PROVIDER_NUM_1
WHEN LINE_26T IS NOT NULL THEN SERVICE_PROVIDER_NUM_2
WHEN LINE_27T IS NOT NULL THEN SERVICE_PROVIDER_NUM_3
WHEN LINE_28T IS NOT NULL THEN SERVICE_PROVIDER_NUM_4
WHEN LINE_29T IS NOT NULL THEN SERVICE_PROVIDER_NUM_5
WHEN LINE_30T IS NOT NULL THEN SERVICE_PROVIDER_NUM_6
WHEN LINE_31T IS NOT NULL THEN SERVICE_PROVIDER_NUM_7
WHEN LINE_32T IS NOT NULL THEN SERVICE_PROVIDER_NUM_8
ELSE NULL END AS PROV_NUM,
CASE
WHEN LINE_1T IS NOT NULL THEN REIMBURSE_IND_1
WHEN LINE_2T IS NOT NULL THEN REIMBURSE_IND_2
WHEN LINE_3T IS NOT NULL THEN REIMBURSE_IND_3
WHEN LINE_4T IS NOT NULL THEN REIMBURSE_IND_4
WHEN LINE_5T IS NOT NULL THEN REIMBURSE_IND_5
WHEN LINE_6T IS NOT NULL THEN REIMBURSE_IND_6
WHEN LINE_7T IS NOT NULL THEN REIMBURSE_IND_7
WHEN LINE_8T IS NOT NULL THEN REIMBURSE_IND_8
WHEN LINE_9T IS NOT NULL THEN REIMBURSE_IND_1
WHEN LINE_10T IS NOT NULL THEN REIMBURSE_IND_2
WHEN LINE_11T IS NOT NULL THEN REIMBURSE_IND_3
WHEN LINE_12T IS NOT NULL THEN REIMBURSE_IND_4
WHEN LINE_13T IS NOT NULL THEN REIMBURSE_IND_5
WHEN LINE_14T IS NOT NULL THEN REIMBURSE_IND_6
WHEN LINE_15T IS NOT NULL THEN REIMBURSE_IND_7
WHEN LINE_16T IS NOT NULL THEN REIMBURSE_IND_8
WHEN LINE_17T IS NOT NULL THEN REIMBURSE_IND_1
WHEN LINE_18T IS NOT NULL THEN REIMBURSE_IND_2
WHEN LINE_19T IS NOT NULL THEN REIMBURSE_IND_3
WHEN LINE_20T IS NOT NULL THEN REIMBURSE_IND_4
WHEN LINE_21T IS NOT NULL THEN REIMBURSE_IND_5
WHEN LINE_22T IS NOT NULL THEN REIMBURSE_IND_6
WHEN LINE_23T IS NOT NULL THEN REIMBURSE_IND_7
WHEN LINE_24T IS NOT NULL THEN REIMBURSE_IND_8
WHEN LINE_25T IS NOT NULL THEN REIMBURSE_IND_1
WHEN LINE_26T IS NOT NULL THEN REIMBURSE_IND_2
WHEN LINE_27T IS NOT NULL THEN REIMBURSE_IND_3
WHEN LINE_28T IS NOT NULL THEN REIMBURSE_IND_4
WHEN LINE_29T IS NOT NULL THEN REIMBURSE_IND_5
WHEN LINE_30T IS NOT NULL THEN REIMBURSE_IND_6
WHEN LINE_31T IS NOT NULL THEN REIMBURSE_IND_7
WHEN LINE_32T IS NOT NULL THEN REIMBURSE_IND_8
ELSE NULL END AS REIMB,
CASE
WHEN LINE_1T IS NOT NULL THEN AMOUNT_PAID_1
WHEN LINE_2T IS NOT NULL THEN AMOUNT_PAID_2
WHEN LINE_3T IS NOT NULL THEN AMOUNT_PAID_3
WHEN LINE_4T IS NOT NULL THEN AMOUNT_PAID_4
WHEN LINE_5T IS NOT NULL THEN AMOUNT_PAID_5
WHEN LINE_6T IS NOT NULL THEN AMOUNT_PAID_6
WHEN LINE_7T IS NOT NULL THEN AMOUNT_PAID_7
WHEN LINE_8T IS NOT NULL THEN AMOUNT_PAID_8
WHEN LINE_9T IS NOT NULL THEN AMOUNT_PAID_1
WHEN LINE_10T IS NOT NULL THEN AMOUNT_PAID_2
WHEN LINE_11T IS NOT NULL THEN AMOUNT_PAID_3
WHEN LINE_12T IS NOT NULL THEN AMOUNT_PAID_4
WHEN LINE_13T IS NOT NULL THEN AMOUNT_PAID_5
WHEN LINE_14T IS NOT NULL THEN AMOUNT_PAID_6
WHEN LINE_15T IS NOT NULL THEN AMOUNT_PAID_7
WHEN LINE_16T IS NOT NULL THEN AMOUNT_PAID_8
WHEN LINE_17T IS NOT NULL THEN AMOUNT_PAID_1
WHEN LINE_18T IS NOT NULL THEN AMOUNT_PAID_2
WHEN LINE_19T IS NOT NULL THEN AMOUNT_PAID_3
WHEN LINE_20T IS NOT NULL THEN AMOUNT_PAID_4
WHEN LINE_21T IS NOT NULL THEN AMOUNT_PAID_5
WHEN LINE_22T IS NOT NULL THEN AMOUNT_PAID_6
WHEN LINE_23T IS NOT NULL THEN AMOUNT_PAID_7
WHEN LINE_24T IS NOT NULL THEN AMOUNT_PAID_8
WHEN LINE_25T IS NOT NULL THEN AMOUNT_PAID_1
WHEN LINE_26T IS NOT NULL THEN AMOUNT_PAID_2
WHEN LINE_27T IS NOT NULL THEN AMOUNT_PAID_3
WHEN LINE_28T IS NOT NULL THEN AMOUNT_PAID_4
WHEN LINE_29T IS NOT NULL THEN AMOUNT_PAID_5
WHEN LINE_30T IS NOT NULL THEN AMOUNT_PAID_6
WHEN LINE_31T IS NOT NULL THEN AMOUNT_PAID_7
WHEN LINE_32T IS NOT NULL THEN AMOUNT_PAID_8
ELSE NULL END AS AMT_PAID,
CASE
WHEN LINE_1T IS NOT NULL THEN PROCEDURE_CODE_1
WHEN LINE_2T IS NOT NULL THEN PROCEDURE_CODE_2
WHEN LINE_3T IS NOT NULL THEN PROCEDURE_CODE_3
WHEN LINE_4T IS NOT NULL THEN PROCEDURE_CODE_4
WHEN LINE_5T IS NOT NULL THEN PROCEDURE_CODE_5
WHEN LINE_6T IS NOT NULL THEN PROCEDURE_CODE_6
WHEN LINE_7T IS NOT NULL THEN PROCEDURE_CODE_7
WHEN LINE_8T IS NOT NULL THEN PROCEDURE_CODE_8
WHEN LINE_9T IS NOT NULL THEN PROCEDURE_CODE_1
WHEN LINE_10T IS NOT NULL THEN PROCEDURE_CODE_2
WHEN LINE_11T IS NOT NULL THEN PROCEDURE_CODE_3
WHEN LINE_12T IS NOT NULL THEN PROCEDURE_CODE_4
WHEN LINE_13T IS NOT NULL THEN PROCEDURE_CODE_5
WHEN LINE_14T IS NOT NULL THEN PROCEDURE_CODE_6
WHEN LINE_15T IS NOT NULL THEN PROCEDURE_CODE_7
WHEN LINE_16T IS NOT NULL THEN PROCEDURE_CODE_8
WHEN LINE_17T IS NOT NULL THEN PROCEDURE_CODE_1
WHEN LINE_18T IS NOT NULL THEN PROCEDURE_CODE_2
WHEN LINE_19T IS NOT NULL THEN PROCEDURE_CODE_3
WHEN LINE_20T IS NOT NULL THEN PROCEDURE_CODE_4
WHEN LINE_21T IS NOT NULL THEN PROCEDURE_CODE_5
WHEN LINE_22T IS NOT NULL THEN PROCEDURE_CODE_6
WHEN LINE_23T IS NOT NULL THEN PROCEDURE_CODE_7
WHEN LINE_24T IS NOT NULL THEN PROCEDURE_CODE_8
WHEN LINE_25T IS NOT NULL THEN PROCEDURE_CODE_1
WHEN LINE_26T IS NOT NULL THEN PROCEDURE_CODE_2
WHEN LINE_27T IS NOT NULL THEN PROCEDURE_CODE_3
WHEN LINE_28T IS NOT NULL THEN PROCEDURE_CODE_4
WHEN LINE_29T IS NOT NULL THEN PROCEDURE_CODE_5
WHEN LINE_30T IS NOT NULL THEN PROCEDURE_CODE_6
WHEN LINE_31T IS NOT NULL THEN PROCEDURE_CODE_7
WHEN LINE_32T IS NOT NULL THEN PROCEDURE_CODE_8
ELSE NULL END AS PROC_CODE,
CASE
WHEN LINE_1T IS NOT NULL THEN UNITS_1
WHEN LINE_2T IS NOT NULL THEN UNITS_2
WHEN LINE_3T IS NOT NULL THEN UNITS_3
WHEN LINE_4T IS NOT NULL THEN UNITS_4
WHEN LINE_5T IS NOT NULL THEN UNITS_5
WHEN LINE_6T IS NOT NULL THEN UNITS_6
WHEN LINE_7T IS NOT NULL THEN UNITS_7
WHEN LINE_8T IS NOT NULL THEN UNITS_8
WHEN LINE_9T IS NOT NULL THEN UNITS_1
WHEN LINE_10T IS NOT NULL THEN UNITS_2
WHEN LINE_11T IS NOT NULL THEN UNITS_3
WHEN LINE_12T IS NOT NULL THEN UNITS_4
WHEN LINE_13T IS NOT NULL THEN UNITS_5
WHEN LINE_14T IS NOT NULL THEN UNITS_6
WHEN LINE_15T IS NOT NULL THEN UNITS_7
WHEN LINE_16T IS NOT NULL THEN UNITS_8
WHEN LINE_17T IS NOT NULL THEN UNITS_1
WHEN LINE_18T IS NOT NULL THEN UNITS_2
WHEN LINE_19T IS NOT NULL THEN UNITS_3
WHEN LINE_20T IS NOT NULL THEN UNITS_4
WHEN LINE_21T IS NOT NULL THEN UNITS_5
WHEN LINE_22T IS NOT NULL THEN UNITS_6
WHEN LINE_23T IS NOT NULL THEN UNITS_7
WHEN LINE_24T IS NOT NULL THEN UNITS_8
WHEN LINE_25T IS NOT NULL THEN UNITS_1
WHEN LINE_26T IS NOT NULL THEN UNITS_2
WHEN LINE_27T IS NOT NULL THEN UNITS_3
WHEN LINE_28T IS NOT NULL THEN UNITS_4
WHEN LINE_29T IS NOT NULL THEN UNITS_5
WHEN LINE_30T IS NOT NULL THEN UNITS_6
WHEN LINE_31T IS NOT NULL THEN UNITS_7
WHEN LINE_32T IS NOT NULL THEN UNITS_8
ELSE NULL END AS UNITS,
CASE
WHEN LINE_1T IS NOT NULL THEN MODIFIER_1
WHEN LINE_2T IS NOT NULL THEN MODIFIER_2
WHEN LINE_3T IS NOT NULL THEN MODIFIER_3
WHEN LINE_4T IS NOT NULL THEN MODIFIER_4
WHEN LINE_5T IS NOT NULL THEN MODIFIER_5
WHEN LINE_6T IS NOT NULL THEN MODIFIER_6
WHEN LINE_7T IS NOT NULL THEN MODIFIER_7
WHEN LINE_8T IS NOT NULL THEN MODIFIER_8
WHEN LINE_9T IS NOT NULL THEN MODIFIER_1
WHEN LINE_10T IS NOT NULL THEN MODIFIER_2
WHEN LINE_11T IS NOT NULL THEN MODIFIER_3
WHEN LINE_12T IS NOT NULL THEN MODIFIER_4
WHEN LINE_13T IS NOT NULL THEN MODIFIER_5
WHEN LINE_14T IS NOT NULL THEN MODIFIER_6
WHEN LINE_15T IS NOT NULL THEN MODIFIER_7
WHEN LINE_16T IS NOT NULL THEN MODIFIER_8
WHEN LINE_17T IS NOT NULL THEN MODIFIER_1
WHEN LINE_18T IS NOT NULL THEN MODIFIER_2
WHEN LINE_19T IS NOT NULL THEN MODIFIER_3
WHEN LINE_20T IS NOT NULL THEN MODIFIER_4
WHEN LINE_21T IS NOT NULL THEN MODIFIER_5
WHEN LINE_22T IS NOT NULL THEN MODIFIER_6
WHEN LINE_23T IS NOT NULL THEN MODIFIER_7
WHEN LINE_24T IS NOT NULL THEN MODIFIER_8
WHEN LINE_25T IS NOT NULL THEN MODIFIER_1
WHEN LINE_26T IS NOT NULL THEN MODIFIER_2
WHEN LINE_27T IS NOT NULL THEN MODIFIER_3
WHEN LINE_28T IS NOT NULL THEN MODIFIER_4
WHEN LINE_29T IS NOT NULL THEN MODIFIER_5
WHEN LINE_30T IS NOT NULL THEN MODIFIER_6
WHEN LINE_21T IS NOT NULL THEN MODIFIER_7
WHEN LINE_32T IS NOT NULL THEN MODIFIER_8
ELSE NULL END AS MOD_CD
FROM TEMP_TBL2
)
--GENERATE THE OUTPUT IN THE CORRECT FOMAT
SELECT
CLAIM_ID,
SEQ_NO,
DOS,
PROV_NUM,
REIMB,
AMT_PAID,
PROC_CODE,
UNITS,
MOD_CD
FROM TEMP_TBL3
WHERE
SEQ_NO IS NOT NULL
AND reimb is not null
ORDER BY
CLAIM_ID,
SEQ_NO
REQUIRED OUTPUT
CLAIM_ID SEQ_NO DOS PROV_NUM REIMB AMT_PAID PROC_CODE UNITS MOD_CD
10012009-01-01 00:00:00.000121000F6000908014NULL
100102009-01-01 00:00:00.000121000F5000741603NULL
100112009-01-01 00:00:00.000121000F3000721254NULL
100122009-01-01 00:00:00.000121000F70000992237NULL
100132009-01-01 00:00:00.000121000F6000810251NULL
100142009-01-01 00:00:00.000121000F8000692105NULL
100152009-01-01 00:00:00.000121000F1000J21851NULL
100162009-01-01 00:00:00.000121000F8000315757NULL
10022009-01-01 00:00:00.000121000F5000741603NULL
10032009-01-01 00:00:00.000121000F3000721254NULL
10042009-01-01 00:00:00.000121000F70000992237NULL
10052009-01-01 00:00:00.000121000F6000810251NULL
10062009-01-01 00:00:00.000121000F8000692105NULL
10072009-01-01 00:00:00.000121000F1000J21851NULL
10082009-01-01 00:00:00.000121000F8000315757NULL
10092009-01-01 00:00:00.000121000F6000908014NULL
10112009-11-21 00:00:00.000122000C0908011NULL
10122009-11-21 00:00:00.000122000F500000A70153NULL
10132009-11-21 00:00:00.000122000F3000933064NULL
10142009-11-21 00:00:00.000122000C0992231NULL
10312009-12-31 00:00:00.000123000F1000908014NULL
103102009-12-31 00:00:00.000123000F5000933061NULL
103112009-12-31 00:00:00.000123000F3000992441NULL
103122009-12-31 00:00:00.000123000F70000925071NULL
103132009-12-31 00:00:00.000123000F6000992121NULL
103142009-12-31 00:00:00.000123000F8000768565NULL
103152009-12-31 00:00:00.000123000F1000992431NULL
103162009-12-31 00:00:00.000123000F8000810032NULL
103172009-12-31 00:00:00.000123000F8000110402NULL
103182009-12-31 00:00:00.000123000F6000710202NULL
103192009-12-31 00:00:00.000123000F4000721252NULL
10322009-12-31 00:00:00.000123000F2000741603NULL
103202009-12-31 00:00:00.000123000F2000705532NULL
103212009-12-31 00:00:00.000123000C0768562NULL
103222009-12-31 00:00:00.000123000C0692104NULL
103232009-12-31 00:00:00.000123000C0J21856NULL
103242009-12-31 00:00:00.000123000C0315759NULL
103252009-12-31 00:00:00.000123000C6000908014NULL
103262009-12-31 00:00:00.000123000C5000741603NULL
103272009-12-31 00:00:00.000123000F3000721254NULL
103282009-12-31 00:00:00.000123000F70000992237NULL
103292009-12-31 00:00:00.000123000F6000810251NULL
10332009-12-31 00:00:00.000123000F3000721254NULL
103302009-12-31 00:00:00.000123000F8000692105NULL
103312009-12-31 00:00:00.000123000F1000J21851NULL
103322009-12-31 00:00:00.000123000F8000315757NULL
10342009-12-31 00:00:00.000123000F40000992237NULL
10352009-12-31 00:00:00.000123000F5000810251NULL
10362009-12-31 00:00:00.000123000F6000692105NULL
10372009-12-31 00:00:00.000123000F8000J21851NULL
10382009-12-31 00:00:00.000123000F10000315757NULL
10392009-12-31 00:00:00.000123000F6000992041NULL
Sorry for such a long post but I wanted to make sure I included everything necessary for someone to point me in the correct direction.
Thanks so much,
Sue B
July 12, 2010 at 11:05 am
Do a search on phrase "Dynamic Pivot". See if that helps you out. Then post back on this thread with further questions about it.
July 12, 2010 at 11:24 am
Thanks for the suggestion Brandie. However, doesn't pivot require you to do some sort of aggregation ? My problem doesn't require any aggregaton which is why I was trying to use a cross tab.
The biggest problem, for me anyway, is that the "line numbers" are variable but the fields in each row are not. I am having trouble making that work without writing case statements for each group of eight. I am just not seeing the light yet.
Thanks,
Sue B
July 12, 2010 at 11:32 am
Ah. Yes. Pivot does require aggregation. Sorry. I didn't realize that's what you're doing.
Hmm. Now I'm wondering if there's a way to trick Pivot into doing an aggregation that isn't used and doesn't mess up the data...
What I did on a 2000 Server once is to create a bunch of views with CASE statements and JOIN the views all together. It was a bit bulky, but it did work.
Create View dbo.MyView1 AS
Select Col1, Col2, Case col3 When 'A' Then 1 End as MyChoice1
from dbo.MyTable
GO
Create View dbo.MyView2 AS
Select Col1, Col2, Case col3 When 'B' Then 2 End as MyChoice2
from dbo.MyTable
GO
Create View dbo.MyView3 AS
Select Col1, Col2, Case col3 When 'C' Then 3 End as MyChoice3
from dbo.MyTable
GO
Select Col1, Col2, MyChoice1, MyChoice2, MyChoice3
from MyView1 v1
join MyView2 v2
on v1.Col1 = v2.Col1
join MyView3 v3
on v1.Col1 = v3.Col1;
Be aware my pseudo-code is not the best. I think I was actually "un-pivoting" a table instead of cross-tabbing/pivoting it and I'm working off memory from code I don't currently have access to. There may be a better way to do this, but this is what I recall doing.
July 12, 2010 at 1:41 pm
Would the following dynamic SQL help you?
DECLARE @sql VARCHAR(6000)
DECLARE @sql1 VARCHAR(500)
DECLARE @sql2 VARCHAR(5000)
DECLARE @sql3 VARCHAR(500)
SET @sql1='
;with cte_group_of8 as
(
SELECT
ROW_NUMBER() OVER(PARTITION BY claim_id ORDER BY cast(left(claim_sequence_numbers + '','',charindex('','',claim_sequence_numbers)-1) as int)) - 1 as grp,*
FROM ENC_HIST
)
'
SET @sql3=' ORDER BY Claim_ID, Seq'
SET @sql2=''
SELECT @sql2 =@sql2 +
'UNION ALL
SELECT
Claim_ID,
cte_group_of8.grp * 8 + '+CAST(n AS CHAR(1)) +'as Seq,
[FIRST_DATE_OF_SERVICE_'+CAST(n AS CHAR(1))+'],
[SERVICE_PROVIDER_NUM_'+CAST(n AS CHAR(1))+'] ,
[REIMBURSE_IND_'+CAST(n AS CHAR(1))+'] ,
[AMOUNT_PAID_'+CAST(n AS CHAR(1))+'] ,
[PROCEDURE_CODE_'+CAST(n AS CHAR(1))+'] ,
[UNITS_'+CAST(n AS CHAR(1))+'] ,
[MODIFIER_'+CAST(n AS CHAR(1))+']
FROM cte_group_of8
WHERE [FIRST_DATE_OF_SERVICE_'+CAST(n AS CHAR(1))+'] IS NOT NULL
'
FROM tally
WHERE n>0 AND n<9
SET @sql= @sql1 + STUFF(@sql2,1,11,'') + @sql3
PRINT @sql
--EXEC(@sql)
As a side note:
[SET sarcasm ON]
If you're currently reading a rather heavy book (about normalization, preferrably), make sure to "hand it over right on to the head" of the person that came up with such a "remarkable" (:sick:) table design. And make sure to use as much force as possible. If you miss, give it a second go. If not, well... even then. :sick:
[SET sarcasm OFF]
July 13, 2010 at 4:37 am
Lutz,
Thanks so much. I just couldn't figure out how to get the line numbers to go with the fields. I don't have any control over the table schema as it is determined by an unnamed government entity and I have been using foul language ever since I was assigned this project (which also includes actuaries - so you can imagine my frustration). I have several other tables with the same "design" so it is going to be a fun summer.
I can't thank you again for helping me out.
Sue B
July 13, 2010 at 10:49 pm
Brandie Tarvin (7/12/2010)
Do a search on phrase "Dynamic Pivot". See if that helps you out. Then post back on this thread with further questions about it.
Pivots are generally slower (and uglier, IMHO) than a nice simple "Cross Tab", Brandie. Take a look if you haven't already seen it)...
http://www.sqlservercentral.com/articles/T-SQL/63681/
http://www.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
Change is inevitable... Change for the better is not.
July 13, 2010 at 10:53 pm
LutzM (7/12/2010)
Would the following dynamic SQL help you?
DECLARE @sql VARCHAR(6000)DECLARE @sql1 VARCHAR(500)
DECLARE @sql2 VARCHAR(5000)
DECLARE @sql3 VARCHAR(500)
SET @sql1='
;with cte_group_of8 as
(
SELECT
ROW_NUMBER() OVER(PARTITION BY claim_id ORDER BY cast(left(claim_sequence_numbers + '','',charindex('','',claim_sequence_numbers)-1) as int)) - 1 as grp,*
FROM ENC_HIST
)
'
SET @sql3=' ORDER BY Claim_ID, Seq'
SET @sql2=''
SELECT @sql2 =@sql2 +
'UNION ALL
SELECT
Claim_ID,
cte_group_of8.grp * 8 + '+CAST(n AS CHAR(1)) +'as Seq,
[FIRST_DATE_OF_SERVICE_'+CAST(n AS CHAR(1))+'],
[SERVICE_PROVIDER_NUM_'+CAST(n AS CHAR(1))+'] ,
[REIMBURSE_IND_'+CAST(n AS CHAR(1))+'] ,
[AMOUNT_PAID_'+CAST(n AS CHAR(1))+'] ,
[PROCEDURE_CODE_'+CAST(n AS CHAR(1))+'] ,
[UNITS_'+CAST(n AS CHAR(1))+'] ,
[MODIFIER_'+CAST(n AS CHAR(1))+']
FROM cte_group_of8
WHERE [FIRST_DATE_OF_SERVICE_'+CAST(n AS CHAR(1))+'] IS NOT NULL
'
FROM tally
WHERE n>0 AND n<9
SET @sql= @sql1 + STUFF(@sql2,1,11,'') + @sql3
PRINT @sql
--EXEC(@sql)
As a side note:
[SET sarcasm ON]
If you're currently reading a rather heavy book (about normalization, preferrably), make sure to "hand it over right on to the head" of the person that came up with such a "remarkable" (:sick:) table design. And make sure to use as much force as possible. If you miss, give it a second go. If not, well... even then. :sick:
[SET sarcasm OFF]
Dang, Lutz... looks like I'm going to have to turn the "doesn't use RBAR, KISS it instead" crown over to you, Lutz. Very well done.
What's next... my mighty "Crosstab Decoder Ring"? 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
July 14, 2010 at 11:45 am
Jeff Moden (7/13/2010)
...Dang, Lutz... looks like I'm going to have to turn the "doesn't use RBAR, KISS it instead" crown over to you, Lutz. Very well done.
What's next... my mighty "Crosstab Decoder Ring"? 😛
Thanx for the flowers. :blush:
But, there's no reason to turn over one of your numerous crowns. Just add another one instead: "One of the best mentors". Or who do you think I learned all that stuff from originally? Based on your response it seems like I finally figured how that stuff works 😀
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply