Help with dynamic cross-tab

Question

Help with dynamic cross-tab

sbelche-760308

SSC-Addicted

Points: 467
More actions
July 12, 2010 at 8:58 am

#223758

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

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Brandie Tarvin SSC Guru Points: 173104 More actions · Answer 1

Do a search on phrase "Dynamic Pivot". See if that helps you out. Then post back on this thread with further questions about it.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

sbelche-760308 SSC-Addicted Points: 467 More actions · Answer 2

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

Brandie Tarvin SSC Guru Points: 173104 More actions · Answer 3

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.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

LutzM SSC Guru Points: 107049 More actions · Answer 4

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]

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

sbelche-760308 SSC-Addicted Points: 467 More actions · Answer 5

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

Jeff Moden SSC Guru Points: 1004582 More actions · Answer 6

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

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004582 More actions · Answer 7

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

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 8

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

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Help with dynamic cross-tab

Cookies on SQLServerCentral