October 15, 2011 at 8:37 pm
i want to transpose (pivot?) this set so that the Rownum is the column and the descriptions of the amounts become rows.
i do not need the aggregate pivot demands, so i dont know how to turn it around.
IF OBJECT_ID('TempDB..#t','U') IS NOT NULL DROP TABLE #t
CREATE TABLE [dbo].[#t](
[Charqtr] [char](8) NULL,
[paid] [money] NULL,
[billed] [money] NULL,
[ineligible] [money] NULL,
[cob] [money] NULL,
[coinsurance] [money] NULL,
[discount] [money] NULL,
[deductible] [money] NULL,
[eligible_charges] [money] NULL,
[outofpocket] [money] NULL,
[claim_count] [int] NULL,
[rownum] [varchar](12) NULL,
[affil] [varchar](6) NOT NULL
) ON [PRIMARY]
insert into #t (Charqtr,paid,billed,ineligible,cob,coinsurance,discount,deductible,eligible_charges,outofpocket,claim_count,rownum,affil)
SELECT '201001 ',1516522.08,4564610.16,765087.56,120231.69,42493.58,2136369.48,39662.97,3636797.33,74534.51,7419,'Q 1','Par'union all
SELECT '201002 ',2792569.56,7568050.73,1193029.67,251090.60,69148.67,3445849.65,13070.04,6054781.79,234101.58,9944,'Q 2','Par'union all
SELECT '201003 ',2586667.23,6894117.70,1175073.31,277295.94,58375.67,2876011.79,10865.84,5383372.78,178582.84,10448,'Q 3','Par'union all
SELECT '201004 ',2626743.31,7396310.15,1185454.23,439782.92,13619.42,3438504.30,4873.04,5757453.58,163946.78,11322,'Q 4','Par'union all
SELECT '201101 ',658279.54,1806365.45,547315.31,238791.88,-95.12,461921.95,685.41,1020353.38,-5669.04,2074,'Q 5','Par'union all
SELECT '201102 ',7467.96,245207.24,194522.19,16468.11,-90.55,27901.59,-264.88,34307.49,6179.16,291,'Q 6','Par'union all
SELECT '201001 ',390384.54,750508.65,221467.62,40907.44,74895.60,20565.06,37856.89,413237.99,44841.81,1932,'Q 1','NonPar'union all
SELECT '201002 ',732563.18,1469692.46,536013.47,52785.70,89667.05,100585.39,19353.86,791226.24,28313.54,2834,'Q 2','NonPar'union all
SELECT '201003 ',964383.94,1940340.30,747460.17,76567.93,86294.38,87333.79,11433.52,1030017.82,35555.08,2698,'Q 3','NonPar'union all
SELECT '201004 ',629385.91,1136218.85,507789.98,67456.92,73894.24,45532.16,7281.19,487077.71,12086.19,2583,'Q 4','NonPar'union all
SELECT '201101 ',342374.14,629928.75,268751.41,39485.35,11477.21,7000.35,1859.80,310214.78,-19439.82,955,'Q 5','NonPar'union all
SELECT '201102 ',67115.12,208111.73,140606.57,4521.16,698.04,2189.66,-741.91,62285.96,145.30,186,'Q 6','NonPar'
i want to get the data with the rownums on columns and the descriptions on rows...if i have to stack par and non par or do them twice its no biggie, but i cannot feature how to 'turn' more than one value with pivot....in Excel i can just say 'Transpose' and it does it.
rownum Q 1 Q 2
affil Par Par
billed 4564610.16 7568050.73
Charqtr 201001201002
claim_cnt 74199944
cob 120231.69251090.6
coinsurance 42493.5869148.67
deductible 39662.9713070.04
discount 2136369.483445849.65
eligible_chgs 3636797.336054781.79
ineligible 765087.561193029.67
outofpocket 74534.51234101.58
paid 1516522.082792569.56
thanks a ton for your help
drew
October 16, 2011 at 4:03 pm
Drew,
I'm really confused as to what you want to do. Your textual description says you want to "unpivot" the rows but your example is matching up quarter information based on the very "row number" that you said you wanted as the identifier.
Would you take another look at the expected outcome you posted and make sure that's what you really want. If it is, then what do you want to do with "Q 3" through "Q 6"?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2011 at 4:44 pm
Ok... with the understanding that I may not actually understand what you need, the following will do what I think you're saying using the test data you posted...
WITH
cteUnPvt AS
( --=== Unpivot the data so we can work with it
SELECT t.RowNum,
t.affil,
t.Charqtr,
unpvt.AttributeName,
unpvt.AttributeValue
FROM #T t
CROSS APPLY
( --=== This is the "meat" of the unpivot.
-- Typically, this is a bit faster the an "UNPIVOT"
SELECT 'paid' ,paid UNION ALL
SELECT 'billed' ,billed UNION ALL
SELECT 'ineligible' ,ineligible UNION ALL
SELECT 'cob' ,cob UNION ALL
SELECT 'coinsurance' ,coinsurance UNION ALL
SELECT 'discount' ,discount UNION ALL
SELECT 'deductible' ,deductible UNION ALL
SELECT 'eligible_charges' ,eligible_charges UNION ALL
SELECT 'outofpocket' ,outofpocket UNION ALL
SELECT 'claim_count' ,claim_count
) unpvt (AttributeName, AttributeValue)
) --=== Repivot the data the way we want it
SELECT AttributeName,
affil,
Q1 = SUM(CASE WHEN RowNum = 'Q 1' THEN AttributeValue ELSE 0 END),
Q2 = SUM(CASE WHEN RowNum = 'Q 2' THEN AttributeValue ELSE 0 END),
Q3 = SUM(CASE WHEN RowNum = 'Q 3' THEN AttributeValue ELSE 0 END),
Q4 = SUM(CASE WHEN RowNum = 'Q 4' THEN AttributeValue ELSE 0 END),
Q5 = SUM(CASE WHEN RowNum = 'Q 5' THEN AttributeValue ELSE 0 END),
Q6 = SUM(CASE WHEN RowNum = 'Q 6' THEN AttributeValue ELSE 0 END),
Total = SUM(AttributeValue)
FROM cteUnPvt
GROUP BY AttributeName, affil
ORDER BY AttributeName, affil
;
Tip'o'da'hat to Paul White for the "different" method of unpivoting using Cross Apply.
Here's what the output looks like...
AttributeName affil Q1 Q2 Q3 Q4 Q5 Q6 Total
---------------- ------ ---------- ---------- ---------- ---------- ----------- --------- -----------
billed NonPar 750508.65 1469692.46 1940340.30 1136218.85 629928.75 208111.73 6134800.74
billed Par 4564610.16 7568050.73 6894117.70 7396310.15 1806365.45 245207.24 28474661.43
claim_count NonPar 1932.00 2834.00 2698.00 2583.00 955.00 186.00 11188.00
claim_count Par 7419.00 9944.00 10448.00 11322.00 2074.00 291.00 41498.00
cob NonPar 40907.44 52785.70 76567.93 67456.92 39485.35 4521.16 281724.50
cob Par 120231.69 251090.60 277295.94 439782.92 238791.88 16468.11 1343661.14
coinsurance NonPar 74895.60 89667.05 86294.38 73894.24 11477.21 698.04 336926.52
coinsurance Par 42493.58 69148.67 58375.67 13619.42 -95.12 -90.55 183451.67
deductible NonPar 37856.89 19353.86 11433.52 7281.19 1859.80 -741.91 77043.35
deductible Par 39662.97 13070.04 10865.84 4873.04 685.41 -264.88 68892.42
discount NonPar 20565.06 100585.39 87333.79 45532.16 7000.35 2189.66 263206.41
discount Par 2136369.48 3445849.65 2876011.79 3438504.30 461921.95 27901.59 12386558.76
eligible_charges NonPar 413237.99 791226.24 1030017.82 487077.71 310214.78 62285.96 3094060.50
eligible_charges Par 3636797.33 6054781.79 5383372.78 5757453.58 1020353.38 34307.49 21887066.35
ineligible NonPar 221467.62 536013.47 747460.17 507789.98 268751.41 140606.57 2422089.22
ineligible Par 765087.56 1193029.67 1175073.31 1185454.23 547315.31 194522.19 5060482.27
outofpocket NonPar 44841.81 28313.54 35555.08 12086.19 -19439.82 145.30 101502.10
outofpocket Par 74534.51 234101.58 178582.84 163946.78 -5669.04 6179.16 651675.83
paid NonPar 390384.54 732563.18 964383.94 629385.91 342374.14 67115.12 3126206.83
paid Par 1516522.08 2792569.56 2586667.23 2626743.31 658279.54 7467.96 10188249.68
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2011 at 4:46 pm
Thank you so much for your reply.
I want to lay the succeeding quarters next to one and two...i want it to change from this
Charqtrpaidbilledineligiblecobcoinsurancediscountdeductibleeligible_chargesoutofpocketclaim_countrownumaffil
2009011003871.042983490.4356735.9993944.7645703.621456333.5932924.552487106.0367973.315690Q 1Par
2009021631516.065166318.12993929.28278631.4642428.182215296.0814562.643851329.281901.528580Q 2Par
2009031600802.044689056.471030679.68276915.7939780.461926753.037177.823341680.54-71740.238392Q 3Par
2009042601162.586422177.56915319.55273709.8672087.092603894.249246.615161061.0695570.429632Q 4Par
201001578005.611642349.86548470.19117469.19-23730.63581632.34421.971000141.11-24816.692152Q 5Par
20100237576.43211868.1158238.095980.92-8606.4845541.95-462.9556255.57-7735.72354Q 6Par
2010034419.76149506.2966227.8310155.2-1166.7486105.25-107.9574290-19392.3179Q 7Par
201004-7626.098141478552.197395.47-11.283600.3920-4522.38-11737.2173Q 8Par
200901330710.55659003.52203480.6928987.3846952.7537471.1327535.92379582.727621.191393Q 1NonPar
200902593655.241188648.84441058.6459114.4173548.0335692.9320926.48614927.761247.72881Q 2NonPar
200903544715.391179383.5463060.5274625.167605.8251424.3910813.33574092.06791.282372Q 3NonPar
200904592182.931177371.24426186.7788495.3764820.1219875.238331.81597868.9830472.342801Q 4NonPar
201001471059.121324076.8698779.5750260.2736035.2250249.323401.31539001.74-121185.231271Q 5NonPar
20100261440.8247283.82153204.966811.066435.5428303.2290080832.26-8304.39199Q 6NonPar
20100366634.0348014.2414881.512226.191471.59-28104.13-19029434.956246.74101Q 7NonPar
20100416546.9237566.8829168.59245.921733.39035.336418.98-165.2553Q 8NonPar
to this
Charqtr200901200902200903200904201001201002201003201004200901200902200903200904201001201002201003201004
paid1003871.041631516.061600802.042601162.58578005.6137576.434419.76-7626.09330710.55593655.24544715.39592182.93471059.1261440.866634.0316546.92
billed2983490.45166318.124689056.476422177.561642349.86211868.1149506.2981414659003.521188648.841179383.51177371.241324076.8247283.8248014.2437566.88
ineligible356735.99993929.281030679.68915319.55548470.19158238.0966227.8378552.19203480.69441058.64463060.52426186.77698779.57153204.9614881.5129168.59
cob93944.76278631.46276915.79273709.86117469.195980.9210155.27395.4728987.3859114.4174625.188495.3750260.276811.062226.19245.92
coinsurance45703.6242428.1839780.4672087.09-23730.63-8606.48-1166.74-11.2846952.7573548.0367605.8264820.1236035.226435.541471.591733.39
discount1456333.592215296.081926753.032603894.24581632.3445541.9586105.253600.3937471.1335692.9351424.3919875.2350249.3228303.22-28104.130
deductible32924.5514562.647177.829246.61421.97-462.95-107.952027535.9220926.4810813.338331.813401.31900-19035.33
eligible_charges2487106.033851329.23341680.545161061.061000141.1156255.5774290-4522.38379582.7614927.76574092.06597868.98539001.7480832.2629434.956418.98
outofpocket67973.3181901.52-71740.2395570.42-24816.69-7735.72-19392.3-11737.2127621.191247.7791.2830472.34-121185.23-8304.396246.74-165.25
claim_count56908580839296322152354179731393288123722801127119910153
rownumQ 1Q 2Q 3Q 4Q 5Q 6Q 7Q 8Q 1Q 2Q 3Q 4Q 5Q 6Q 7Q 8
affilParParParParParParParParNonParNonParNonParNonParNonParNonParNonParNonPar
When i said i could run it twice for par and non par, i meant that we can eliminate the repeating yearmo and Qs by stacking the Affils (doing what ever operation rearranges my result this way 2 x, once for Par and once for NonPar)
thanks again
drew
October 16, 2011 at 4:58 pm
In that case, how's this look?
WITH
cteUnPvt AS
( --=== Unpivot the data so we can work with it
SELECT t.affil,
t.Charqtr,
unpvt.AttributeName,
unpvt.AttributeValue
FROM #T t
CROSS APPLY
( --=== This is the "meat" of the unpivot.
-- Typically, this is a bit faster the an "UNPIVOT"
SELECT 'paid' ,CAST(paid AS VARCHAR(15)) UNION ALL
SELECT 'billed' ,CAST(billed AS VARCHAR(15)) UNION ALL
SELECT 'ineligible' ,CAST(ineligible AS VARCHAR(15)) UNION ALL
SELECT 'cob' ,CAST(cob AS VARCHAR(15)) UNION ALL
SELECT 'coinsurance' ,CAST(coinsurance AS VARCHAR(15)) UNION ALL
SELECT 'discount' ,CAST(discount AS VARCHAR(15)) UNION ALL
SELECT 'deductible' ,CAST(deductible AS VARCHAR(15)) UNION ALL
SELECT 'eligible_charges' ,CAST(eligible_charges AS VARCHAR(15)) UNION ALL
SELECT 'outofpocket' ,CAST(outofpocket AS VARCHAR(15)) UNION ALL
SELECT 'claim_count' ,CAST(claim_count AS VARCHAR(15)) UNION ALL
SELECT 'rownum' ,CAST(rownum AS VARCHAR(15))
) unpvt (AttributeName, AttributeValue)
) --=== Repivot the data the way we want it
SELECT AttributeName,
affil,
[201001] = MAX(CASE WHEN Charqtr = '201001' THEN AttributeValue ELSE '' END),
[201002] = MAX(CASE WHEN Charqtr = '201002' THEN AttributeValue ELSE '' END),
[201003] = MAX(CASE WHEN Charqtr = '201003' THEN AttributeValue ELSE '' END),
[201004] = MAX(CASE WHEN Charqtr = '201004' THEN AttributeValue ELSE '' END),
[201101] = MAX(CASE WHEN Charqtr = '201101' THEN AttributeValue ELSE '' END),
[201102] = MAX(CASE WHEN Charqtr = '201102' THEN AttributeValue ELSE '' END)
FROM cteUnPvt
GROUP BY AttributeName, affil
ORDER BY AttributeName, affil
;
Results look like the following...
AttributeName affil 201001 201002 201003 201004 201101 201102
---------------- ------ --------------- --------------- --------------- --------------- --------------- ---------------
billed NonPar 750508.65 1469692.46 1940340.30 1136218.85 629928.75 208111.73
billed Par 4564610.16 7568050.73 6894117.70 7396310.15 1806365.45 245207.24
claim_count NonPar 1932 2834 2698 2583 955 186
claim_count Par 7419 9944 10448 11322 2074 291
cob NonPar 40907.44 52785.70 76567.93 67456.92 39485.35 4521.16
cob Par 120231.69 251090.60 277295.94 439782.92 238791.88 16468.11
coinsurance NonPar 74895.60 89667.05 86294.38 73894.24 11477.21 698.04
coinsurance Par 42493.58 69148.67 58375.67 13619.42 -95.12 -90.55
deductible NonPar 37856.89 19353.86 11433.52 7281.19 1859.80 -741.91
deductible Par 39662.97 13070.04 10865.84 4873.04 685.41 -264.88
discount NonPar 20565.06 100585.39 87333.79 45532.16 7000.35 2189.66
discount Par 2136369.48 3445849.65 2876011.79 3438504.30 461921.95 27901.59
eligible_charges NonPar 413237.99 791226.24 1030017.82 487077.71 310214.78 62285.96
eligible_charges Par 3636797.33 6054781.79 5383372.78 5757453.58 1020353.38 34307.49
ineligible NonPar 221467.62 536013.47 747460.17 507789.98 268751.41 140606.57
ineligible Par 765087.56 1193029.67 1175073.31 1185454.23 547315.31 194522.19
outofpocket NonPar 44841.81 28313.54 35555.08 12086.19 -19439.82 145.30
outofpocket Par 74534.51 234101.58 178582.84 163946.78 -5669.04 6179.16
paid NonPar 390384.54 732563.18 964383.94 629385.91 342374.14 67115.12
paid Par 1516522.08 2792569.56 2586667.23 2626743.31 658279.54 7467.96
rownum NonPar Q 1 Q 2 Q 3 Q 4 Q 5 Q 6
rownum Par Q 1 Q 2 Q 3 Q 4 Q 5 Q 6
--Jeff Moden
Change is inevitable... Change for the better is not.
October 16, 2011 at 4:59 pm
Exactly.
Sorry for being inarticulate, and extremely grateful for your effort.
drew
October 16, 2011 at 5:01 pm
Nah... not to worry. Sometimes it's hard to put questions/requirements together. Now we know what a BA goes through. 😀 Glad I could help.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2011 at 7:28 pm
i have a dopey question about the transformation.
in the original version there were 13 columns and 16 rows...208 data points, but the transformed result has 11 columns and 20 rows, or 220 data points.
my intuition says the Attribute Name column is neutral in the transformation because it was already there, as must be Affil, although Yearmo got left behind and the Totals column is added, so it should be a wash...i dont see any other redundancy in the result, but there looks like there is a difference of 12 data points between them but i cannot articulate why.
please help me pull my head out of ....the cloud <g>.
thanks very much
drew
October 18, 2011 at 7:43 pm
I'm not sure what you mean... In the final code result I posted, the are 6 "datapoint" columns and 22 rows for a total of 132 datapoints.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 18, 2011 at 9:02 pm
yes, i think that's because in the 'repivot the way we want it' you only went to Q6 instead of Q8.
if you take a second look at the posted data, it runs from the first quarter of 2009 to the fourth of 2010, so that's eight quarters, but you transformed six of them, so if you add the additional two quarters, i think we're home.
the first untransformed set had sixteen rows...one for each quarter/affil (e.g. Par Q1, Par Q2....Non Par Q4).
the rownum columns are redundant because they became the column headings.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply