Need help with Pivot table - 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

General SQL Server Forums

New to SQL Server Programming

Need help with Pivot table

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-02-07 : 09:53:57

Could someone help me pivot this table please

Here's the table info and some insert statements:


CREATE TABLE [dbo].[iclaimstest1](
	[sort] [varchar](1) NOT NULL,
	[reg] [varchar](3) NOT NULL,
	[region] [varchar](1) NOT NULL,
	[area] [varchar](2) NOT NULL,
	[dist] [varchar](3) NULL,
	[doc] [varchar](3) NOT NULL,
	[dowrdt] [smalldatetime] NULL,
	[iCnt] [int] NULL,
	[nCnt] [int] NULL,
	[tCnt] [int] NULL,
	[pCnt] [decimal](18, 1) NULL
) ON [PRIMARY]


insert into iclaimstest1
select '1', 'NAT', '0', '00', '000', '000','10/29/2010', '215262', '649104', '864366', '24.9' union all
select '1', 'NAT', '0', '00', '000', '000','10/29/2010', '94197', '207599', '301796', '31.2' union all
select '1', 'NAT', '0', '00', '000', '000','10/29/2010', '6018', '34059', '40077', '15.0' union all
select '1', 'NAT', '0', '00', '000', '000','11/26/2010', '477847', '1447292', '1925139', '24.8' union all
select '1', 'NAT', '0', '00', '000', '000','11/26/2010', '203210', '455484', '658694', '30.9' union all
select '1', 'NAT', '0', '00', '000', '000','11/26/2010', '13920', '75648', '89568', '15.5'

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-02-07 : 09:56:13

Sorry forgot to send this but I have a pivot stored procedure set up but I don't know where to add the pcnt column. Please assist.


DECLARE @pivot_cols NVARCHAR(MAX); 
SELECT @pivot_cols = 
STUFF((SELECT 
              
               ',SUM(CASE WHEN year_month = ''' + year_month + 
               ''' THEN icnt END) AS [' + year_month + ' icnt]' + 
               ',SUM(CASE WHEN year_month = ''' + year_month + 
               ''' THEN ncnt END) AS [' + year_month + ' ncnt]' 
+ 
               ',SUM(CASE WHEN year_month = ''' + year_month + 
               ''' THEN tcnt END) AS [' + year_month + ' tcnt]' 
     FROM (SELECT DISTINCT RIGHT(CONVERT(VARCHAR(9), 
                                 CAST(dowrdt AS DATETIME), 6), 6), 
                           CONVERT(CHAR(7), CAST(dowrdt AS DATETIME), 
126) 
           FROM iclaimstest1) AS T(year_month, sort) 
     ORDER BY sort 
     FOR XML PATH('') 
     ), 1, 1, ''); 

DECLARE @pivot_query NVARCHAR(MAX); 
SET @pivot_query = 
N'SELECT sort, reg,  ' + @pivot_cols + 
N' ,region, area, dist, doc ' + 
N' INTO    iClaimsYrPivottest ' + 
N'FROM (SELECT sort, reg, region, area, dist, doc, icnt, ncnt, tcnt, dowrdt, ' + 
                N'RIGHT(CONVERT(VARCHAR(9), CAST(dowrdt AS DATETIME), 
6), 
6) AS year_month ' + 
--                N'CAST((1.0 * icnt / NULLIF(tcnt, 0) * 100) AS decimal 
--(18, 
--1)) AS perc ' + 
         N'FROM iclaimstest1) AS F ' + 
N'GROUP BY sort, reg, area, dist, doc, region ' + 
N'ORDER BY sort;';

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-07 : 10:11:33

Some of that looks familiar...


DECLARE @pivot_cols NVARCHAR(MAX); 
SELECT @pivot_cols = 
STUFF((SELECT 
              
               ',SUM(CASE WHEN year_month = ''' + year_month + 
               ''' THEN icnt END) AS [' + year_month + ' icnt]' + 
               ',SUM(CASE WHEN year_month = ''' + year_month + 
               ''' THEN ncnt END) AS [' + year_month + ' ncnt]' + 
               ',SUM(CASE WHEN year_month = ''' + year_month + 
               ''' THEN tcnt END) AS [' + year_month + ' tcnt]'  +
               ',SUM(CASE WHEN year_month = ''' + year_month + 
               ''' THEN pcnt END) AS [' + year_month + ' pcnt]' 
     FROM (SELECT DISTINCT RIGHT(CONVERT(VARCHAR(9), 
                                 CAST(dowrdt AS DATETIME), 6), 6), 
                           CONVERT(CHAR(7), CAST(dowrdt AS DATETIME), 
126) 
           FROM iclaimstest1) AS T(year_month, sort) 
     ORDER BY sort 
     FOR XML PATH('') 
     ), 1, 1, ''); 

DECLARE @pivot_query NVARCHAR(MAX); 
SET @pivot_query = 
N'SELECT sort, reg,  ' + @pivot_cols + 
N' ,region, area, dist, doc ' + 
N' INTO    iClaimsYrPivottest ' + 
N'FROM (SELECT sort, reg, region, area, dist, doc, icnt, ncnt, tcnt,pcnt, dowrdt, ' + 
                N'RIGHT(CONVERT(VARCHAR(9), CAST(dowrdt AS DATETIME), 
6), 
6) AS year_month ' + 
--                N'CAST((1.0 * icnt / NULLIF(tcnt, 0) * 100) AS decimal 
--(18, 
--1)) AS perc ' + 
         N'FROM iclaimstest1) AS F ' + 
N'GROUP BY sort, reg, area, dist, doc, region ' + 
N'ORDER BY sort;';

Produced this query:


SELECT sort, reg,  SUM(CASE WHEN year_month = 'Oct 10' THEN icnt END) AS [Oct 10 icnt]
,SUM(CASE WHEN year_month = 'Oct 10' THEN ncnt 
END) AS [Oct 10 ncnt]
,SUM(CASE WHEN year_month = 'Oct 10' THEN tcnt END) AS [Oct 10 tcnt]
,SUM(CASE WHEN year_month = 'Oct 10' THEN pcnt END) AS [Oct 10 pcnt]
,SUM(CASE WHEN year_month = 'Nov 10' THEN icnt END) AS [Nov 10 icnt]
,SUM(CASE WHEN year_month = 'Nov 10' THEN ncnt END) AS [Nov 10 ncnt]
,SUM(CASE WHEN year_month = 'Nov 10' THEN tcnt END) AS [Nov 10 tcnt]
,SUM(CASE WHEN year_month = 'Nov 10' THEN pcnt END) AS [Nov 10 pcnt] 
,region, area, dist, doc  
INTO    iClaimsYrPivottest 
FROM (SELECT sort, reg, region, area, dist, doc, icnt, ncnt, tcnt ,pcnt 
, dowrdt, RIGHT(CONVERT(VARCHAR(9)
, CAST(dowrdt AS DATETIME),6), 6) AS year_month 
FROM iclaimstest1) AS F 
GROUP BY sort, reg, area, dist, doc, region ORDER BY sort;

Which produced apparently correct results

Poor planning on your part does not constitute an emergency on my part.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-02-07 : 10:35:40

Thanks the first one worked as new months will be added each month so I need to set it up dynamically.

when I did the calculation on the calculator to check the percentage it didn't add up right.

Oct10icnt/oct10tnct = 26% Is this right?


sort  reg    oct10icnt oct10ncnt oct10tnct  oct10pcnt  Nov10icnt  nov10ncnt  nov10tnct nov10pcnt
1     NAT    315477    890762	 1206239     71.1      694977         1978424  2673401 71.2

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-07 : 13:40:58

I didn't validate the calculation, I thought you needed the column. If you need the calculation..you may change it like this I supppose:



BEFORE:  ',SUM(CASE WHEN year_month = ''' + year_month + 
               ''' THEN pcnt END) AS [' + year_month + ' pcnt]' 

AFTER:  ',SUM(CASE WHEN year_month = ''' + year_month + 
               ''' THEN icnt/tcnt END) AS [' + year_month + ' pcnt]'

you may have to modify this line too


N'FROM (SELECT sort, reg, region, area, dist, doc, icnt, ncnt, tcnt,pcnt, dowrdt, ' + 
                N'RIGHT(CONVERT(VARCHAR(9), CAST(dowrdt AS DATETIME),

Poor planning on your part does not constitute an emergency on my part.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-02-07 : 15:49:58

Thanks Dataguru. I tried this but the pct fields came back with 0 for Oct 10 pct and Nov 10 pct fields. Ugh...


DECLARE @pivot_cols NVARCHAR(MAX); 
SELECT @pivot_cols = 
STUFF((SELECT 
              
               ',SUM(CASE WHEN year_month = ''' + year_month + 
               ''' THEN icnt END) AS [' + year_month + ' icnt]' + 
               ',SUM(CASE WHEN year_month = ''' + year_month + 
               ''' THEN ncnt END) AS [' + year_month + ' ncnt]' + 
               ',SUM(CASE WHEN year_month = ''' + year_month + 
               ''' THEN tcnt END) AS [' + year_month + ' tcnt]'  +
              ',SUM(CASE WHEN year_month = ''' + year_month + 
               ''' THEN icnt/tcnt END) AS [' + year_month + ' pcnt]' 
     FROM (SELECT DISTINCT RIGHT(CONVERT(VARCHAR(9), 
                                 CAST(dowrdt AS DATETIME), 6), 6), 
                           CONVERT(CHAR(7), CAST(dowrdt AS DATETIME), 
126) 
           FROM iclaimstest1) AS T(year_month, sort) 
     ORDER BY sort 
     FOR XML PATH('') 
     ), 1, 1, ''); 

DECLARE @pivot_query NVARCHAR(MAX); 
SET @pivot_query = 
N'SELECT sort, reg,  ' + @pivot_cols + 
N' ,region, area, dist, doc ' + 
N' INTO    iClaimsYrPivottest ' + 
N'FROM (SELECT sort, reg, region, area, dist, doc, icnt, ncnt, tcnt,pcnt, dowrdt, ' + 
                N'RIGHT(CONVERT(VARCHAR(9), CAST(dowrdt AS DATETIME), 
6), 
6) AS year_month ' + 
--                N'CAST((1.0 * icnt / NULLIF(tcnt, 0) * 100) AS decimal 
--(18, 
--1)) AS perc ' + 
         N'FROM iclaimstest1) AS F ' + 
N'GROUP BY sort, reg, area, dist, doc, region ' + 
N'ORDER BY sort;';

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-07 : 16:04:16

Are the source fields integers? If so then they would keep that precision of zero decimal plays in the resulting column. See if changing it to the below matters.

(icnt+0.00)/(tcnt+0.00)

Poor planning on your part does not constitute an emergency on my part.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-02-07 : 16:22:12

Yes both source fields are int

I got numbers but they are still wrong if you

315477/1206239 = 26% on the calculator SQL is giving me

0.7113227102905465

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-07 : 16:44:34

Ahh..probably summing the percentage for each applicable row in the source ..duh. My bad.

either this:
(SUM(icnt+0.00))/(SUM(tcnt+0.00))

Or

Something like this.

',SUM(CASE WHEN year_month = ''' + year_month +
''' THEN icnt+0.00 END)/ SUM(CASE WHEN year_month = ''' + year_month +
''' THEN tcnt+0.00 END) AS [' + year_month + ' pcnt]'

Poor planning on your part does not constitute an emergency on my part.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-02-07 : 16:53:16

Here's the table DDL and insert statement:



CREATE TABLE [dbo].[iclaimstest1](
	[sort] [varchar](1) NOT NULL,
	[reg] [varchar](3) NOT NULL,
	[region] [varchar](1) NOT NULL,
	[area] [varchar](2) NOT NULL,
	[dist] [varchar](3) NULL,
	[doc] [varchar](3) NOT NULL,
	[dowrdt] [smalldatetime] NULL,
	[iCnt] [int] NULL,
	[nCnt] [int] NULL,
	[tCnt] [int] NULL,
	[pCnt] [decimal](18, 1) NULL
) ON [PRIMARY]


insert into iclaimstest1
select '1', 'NAT', '0', '00', '000', '000','10/29/2010', '215262', '649104', '864366', '24.9' union all
select '1', 'NAT', '0', '00', '000', '000','10/29/2010', '94197', '207599', '301796', '31.2' union all
select '1', 'NAT', '0', '00', '000', '000','10/29/2010', '6018', '34059', '40077', '15.0' union all
select '1', 'NAT', '0', '00', '000', '000','11/26/2010', '477847', '1447292', '1925139', '24.8' union all
select '1', 'NAT', '0', '00', '000', '000','11/26/2010', '203210', '455484', '658694', '30.9' union all
select '1', 'NAT', '0', '00', '000', '000','11/26/2010', '13920', '75648', '89568', '15.5

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-02-07 : 17:08:47

THANKS! YIPPIE!!!! This one worked!!!



',SUM(CASE WHEN year_month = ''' + year_month + 
''' THEN icnt+0.00 END)/ SUM(CASE WHEN year_month = ''' + year_month + 
''' THEN tcnt+0.00 END) AS [' + year_month + ' pcnt]'

Only thing how do I change it to decimal it came out as this:

0.261537

I added the bold part but getting incorrect syntax near As:

DECLARE @pivot_cols NVARCHAR(MAX); 
SELECT @pivot_cols = 
STUFF((SELECT 
              
               ',SUM(CASE WHEN year_month = ''' + year_month + 
               ''' THEN icnt END) AS [' + year_month + ' icnt]' + 
               ',SUM(CASE WHEN year_month = ''' + year_month + 
               ''' THEN ncnt END) AS [' + year_month + ' ncnt]' + 
               ',SUM(CASE WHEN year_month = ''' + year_month + 
               ''' THEN tcnt END) AS [' + year_month + ' tcnt]'  +
             ',SUM(CASE WHEN year_month = ''' + year_month + 
''' THEN icnt+0.00 END)/ SUM(CASE WHEN year_month = ''' + year_month + 
''' THEN tcnt+0.00 END)  AS decimal  (18, 1)  AS [' + year_month + ' pcnt]'

     FROM (SELECT DISTINCT RIGHT(CONVERT(VARCHAR(9), 
                                 CAST(dowrdt AS DATETIME), 6), 6), 
                           CONVERT(CHAR(7), CAST(dowrdt AS DATETIME), 
126) 
           FROM iclaimstest1) AS T(year_month, sort) 
     ORDER BY sort 
     FOR XML PATH('') 
     ), 1, 1, ''); 

DECLARE @pivot_query NVARCHAR(MAX); 
SET @pivot_query = 
N'SELECT sort, reg,  ' + @pivot_cols + 
N' ,region, area, dist, doc ' + 
N' INTO    iClaimsYrPivottest7 ' + 
N'FROM (SELECT sort, reg, region, area, dist, doc, icnt, ncnt, tcnt,pcnt, dowrdt, ' + 
                N'RIGHT(CONVERT(VARCHAR(9), CAST(dowrdt AS DATETIME), 
6), 
6) AS year_month ' + 
--                N'CAST((1.0 * icnt / NULLIF(tcnt, 0) * 100) AS decimal 
--(18, 
--1)) AS perc ' + 
         N'FROM iclaimstest1) AS F ' + 
N'GROUP BY sort, reg, area, dist, doc, region ' + 
N'ORDER BY sort;'; 
 EXEC(@pivot_query);

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-07 : 17:24:31

Do you want it to be 26.1?

you need to cast({statement}*100 as {type-size})...see below.

...I think you need at most 3 digits left of the decimal point for your precision. (Unless you foresee over 999.9 as a result?)

',CAST((SUM(CASE WHEN year_month = ''' + year_month +
''' THEN icnt+0.00 END)/ SUM(CASE WHEN year_month = ''' + year_month +
''' THEN tcnt+0.00 END))*100 as decimal(4, 1)) AS [' + year_month + ' pcnt]'

Poor planning on your part does not constitute an emergency on my part.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-02-07 : 17:33:10

THANK YOU!!! That did it. I don't foresee it going over 999.9 One last thing...

can you explain this to me what's being done here as I want to understand what was added to the pivot table.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-07 : 17:38:57

The column you requested was added to it...that is, the computed percentage

if you change EXEC(@pivot_query); to PRINT @pivot_query you can see the resulting SQL statement that gets executed. It becomes clearer when you see the actual SQL that is being executed, and would also be easier to explain.

Poor planning on your part does not constitute an emergency on my part.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-02-07 : 18:20:09

okay thank you again for all of your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources