Pivot Table

[nc297]

Could someone help me make this data
Sort rpt apr pcnt apr icnt may pcnt may icnt
1 2 22.6 182866 23.4 152619
A 2 27.2 182866 23.4 65336
B 2 20.5 76509 27.4 69989
C 2 13.5 38917 13.5 34850
D 2 25.6 93802 25.5 74682
E 2 39.9 15270 42.6 34770

Want data to Pivot like this:
Sort rpt Dowrdt pcnt icnt
1 2 4/30/2010 25.6 93802
A 2 5/28/10 39.6 45698


I want the dates april 10, may 10 (in the first table) to look like this in the second table under Dowrdt

Here's the table info to help you test it out.


here's the table info for the first table:

GO
CREATE TABLE [dbo].[RibInfoN2](
[sort] [varchar](1) NOT NULL,
[rpt] [varchar](1) NOT NULL,
[apr 10 pcnt] [decimal](4, 1) NULL,
[apr 10 icnt] [int] NULL,
[may 10 pcnt] [decimal](4, 1) NULL,
[may 10 icnt] [int] NULL,
) ON [PRIMARY]

GO
SET ANSI_PADDING OFFInsert into ribinfoN2

Select '1', '2', '22.6', '182866','23.4','152619' union all

Select 'A', '2', '27.2', '182866','23.4','65336' union all
Select 'B', '2', '20.5', '76509','27.4','69989' union all
Select 'C', '2', '13.5', '38917','13.5','34850' union all
Select 'D', '2', '25.6', '93802','25.5','74682' union all
Select 'E', '2', '39.9', '15270','42.6','34770'

 

[simian336]

how do you get

pcnt icnt
25.6 93802

Simi

 

[markros]

Something like

;with cte as (select sort, rpt, 'April' as [Mnth], [apr 10 pcnt] as [pcnt], [apr 10 icnt] as icnt
from dbo.RibInfoN2
UNION ALL
select sort, rpt, 'May' as [Mnth], [may 10 pcnt] as [pcnt], [may 10 icnt] as icnt
from dbo.RibInfoN2)

select sort, rpt,Mnth, avg(pcnt) as pcnt, avg(icnt) as icnt
from cte group by sort, rpt,Mnth

PluralSight Learning Library

 

[nc297]

Thanks Markros!!!!

How do i get it to come out as 22.6, 22.4 for the pcnt column (means percent) column.

I added the dates that I needed:

;with cte as (select sort, rpt, '04/29/2010' as [Mnth], [apr 10 pcnt] as [pcnt], [apr 10 icnt] as icnt
from dbo.RibInfoN2
UNION ALL
select sort, rpt, '05/30/2010' as [Mnth], [may 10 pcnt] as [pcnt], [may 10 icnt] as icnt
from dbo.RibInfoN2)

select sort, rpt,Mnth, avg(pcnt) as pcnt, avg(icnt) as icnt
from cte group by sort, rpt,Mnth

 

[markros]

Use cast(avg(pcnt) as decimal(10,1)) as pcnt

PluralSight Learning Library

 

[nc297]

Thanks Again!

Could you explain what you wrote? CTE that's a temp table right?

 

[markros]

Yes, in this particular case CTE (common table expression) can be also used as derived table.

So, I first 'UNPIVOT' the data using UNION ALL (since we needed to unpivot based on 2 columns) and then GROUP BY the rest of the fields.

You may want to review this blog post explaining CTE
CTE and hierarchical queries

PluralSight Learning Library

 

[nc297]

Thanks again and I will review the blog.

I added more to the query. I wanted to add two columns together this is what I've done:

;with cte as (select sort, reg, region, area, dist, doc, '04/30/2010' as [Dowrdt], [apr 10 pcnt] as [pcnt], [apr 10 icnt] as icnt, [apr 10 ncnt] as ncnt, (sum(isnull([apr 10 icnt],0)) + sum(isnull([apr 10 ncnt],0))) as tcnt,rpt
from dbo.RibInfo1
group by sort, reg, region, area, dist, doc, [apr 10 pcnt], [apr 10 icnt], [apr 10 ncnt] , rpt
UNION ALL
select sort, reg, region, area, dist, doc, '05/28/2010' as [Dowrdt], [may 10 pcnt] as [pcnt], [may 10 icnt] as icnt, [may 10 ncnt] as ncnt , (sum(isnull([may 10 icnt],0)) + sum(isnull([may 10 ncnt],0))) as tcnt,rpt
from dbo.RibInfo1)
group by sort, reg, region, area, dist, doc, [may 10 pcnt], [may 10 icnt], [may 10 ncnt] , rpt

select sort, reg, region, area, dist, doc, Dowrdt, icnt, ncnt, tcnt, rpt
from cte
group by sort, reg, region, area, dist, doc, rpt, Dowrdt, icnt,ncnt, tcnt, rpt

Now I'm getting this error message:

Column 'dbo.RibInfo1.sort' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

What am I missing?

 

[markros]

You put ) in the wrong place. It should be right after ,rpt in your first query that defines CTE.

PluralSight Learning Library

 

[nc297]

Thanks again!!!

 

[nc297]

Now I'm trying to truncate the table and do an insert back into the table but getting incorrect syntax near ;

I don't want to create a new table each time.


Truncate table info
insert into info
;with cte as (select sort, rpt, '4/30/2010' as [Dowrdt], [apr 10 pcnt] as [pcnt], [apr 10 icnt] as icnt
from dbo.RibInfoN2
UNION ALL
select sort, rpt, '5/30/2010' as [Dowrdt], [may 10 pcnt] as [pcnt], [may 10 icnt] as icnt
from dbo.RibInfoN2)

select sort, rpt,Dowrdt, cast(avg(pcnt) as decimal(10,1)) as pcnt , icnt
from cte group by sort, rpt,Dowrdt, icnt

 

[markros]

Put INSERT INTO right before
select sort ...

and AFTER the cte declaration.

PluralSight Learning Library

 

[nc297]

Thanks again Markros!!!!