• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1494
  • Last Modified:

CrossTab Pivot View!

Hello Experts,

Please find below the table which has 3 columns (i.e. company, year, amout ).

COMPANY      |      YEAR      |      AMOUNT
-------            ----            -----
C1      |      2000      |      10
C1      |      2000      |      20
C1      |      2001      |      35
C1      |      2002      |      24
C1      |      2002      |      10
C1      |      2003      |      45

I need to create a view that should display the data in below format.


COMPAY       |      2000      |      2001      |      2002      |       2003
--------                     ------                         ------                        -------                        --------
C1            30            35            34            45


How can i create a view for above requirement.

I know this is possible with procedure and dynamic SQL.  But my client is asking this requiremnt in view.  Is there any posibility to achive this funcationality using views?


Waiting for your valulable reply...

itsvtk
0
Thandava Vallepalli
Asked:
Thandava Vallepalli
  • 4
  • 4
  • 4
  • +1
1 Solution
 
illCommented:
-- only harcoded like this
select
"company"=  company,
"2000"= sum( case [year] when 2000 then [year] else 0 end),
"2001"= sum( case [year] when 2001 then [year] else 0 end),
"2002"= sum( case [year] when 2002 then [year] else 0 end),
...
from mytable
0
 
illCommented:
-- sorry year->amount
create view testView as
select
"company"=  company,
"2000"= sum( case [year] when 2000 then [amount] else 0 end),
"2001"= sum( case [year] when 2001 then [amount] else 0 end),
"2002"= sum( case [year] when 2002 then [amount] else 0 end)
from mytable
0
 
ewildeCommented:
having function like this:

create function dbo.AmountForYear(@Company varchar(20), @Year_ varchar(20)) returns varchar(1000) as
begin
  declare @Res varchar(1000)
  set @Res=''
  select @Res=Amount from T1 where @Company=@Company and  Year= @Year_
  return @Res
end

you can query like this:

select   tt1.COMPANY, dbo.AmountForYear(tt1.recid,'2001') as 'year 2001' , dbo.AmountForYear(tt1.recid,'2002') as 'year 2002', dbo.AmountForYear(tt1.recid,'2003') as 'year 2003',    from (select distinct COMPANY from T1) as tt1
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
ewildeCommented:
sorry, mistake: instead of tt1.recid i meant tt1.COMPANY...
0
 
Thandava VallepalliAuthor Commented:
Hello ill ,

in your solution, you have hard coded the years.  But we should not hard code the years in query.

All distinct years should come dynamically from original table.


itsvtk
0
 
Thandava VallepalliAuthor Commented:
Hello ewilde,

In your solution also, you have hard coded the years......   The problem is i don't know how many distinct years will come in data.

itsvtk

0
 
illCommented:
sorry, you cannot have variable number of columns in mssql 2000 withou using dynamic sql.
rem:ewilde's approch is just wrong ( returning bad result in bad datatype)
0
 
Thandava VallepalliAuthor Commented:
Hello ill,

can't we use dynamic sql in views?

itsvtk
0
 
Thandava VallepalliAuthor Commented:
can't we use dynamic sql either in view or user defined function?
0
 
illCommented:
it is not possible to use dynamic sql in views or functions. possibility to pivot tables without dynamic sql starts by mssql 2005.
0
 
ewildeCommented:
but than the number of columns for each company is dynamic...?
so you might need just one column to hold the amounts of all the years :

having function like this:

create function dbo.AmountForYear(@Company varchar(20)) returns varchar(1000) as
begin
  declare @Res varchar(1000)
  set @Res=''
  select @Res=@Res+'YEAR:'+Year+'Amount:'+Amount+',' from T1 where @Company=@Company
  return @Res
end

you can query like this:

select   tt1.COMPANY, dbo.AmountForYear(tt1.COMPANY)   from (select distinct COMPANY from T1) as tt1
0
 
ewildeCommented:
sorry, i don't test my examples (lake of time...)
just noticed :
do
... Company=@Company ...
instead of
... @Company=@Company ...

0
 
Scott PletcherSenior DBACommented:
You could use dynamic SQL to create a static view.  The number of columns in the view would depend on how many different years were present in the input data.

0
 
Scott PletcherSenior DBACommented:
For example:

--create table, load sample data
if object_id('tablename') is not null
    drop table tablename
create table tablename (
      company varchar(10),
      year smallint,
      amount int
      )
insert into tablename values('C1'     ,     2000     ,     10)
insert into tablename values('C1'     ,     2000     ,     20)
insert into tablename values('C1'     ,     2001     ,     35)
insert into tablename values('C1'     ,     2002     ,     24)
insert into tablename values('C1'     ,     2002     ,     10)
insert into tablename values('C1'     ,     2003     ,     45)


DECLARE @MinYear SMALLINT
DECLARE @MaxYear SMALLINT
DECLARE @Year SMALLINT
DECLARE @sql VARCHAR(8000)

SELECT @MinYear = MIN(Year), @MaxYear = MAX(Year)
FROM tableName

SET @sql = 'CREATE VIEW viewName AS SELECT Company'

SET @Year = @MinYear

WHILE @Year <= @MaxYear
BEGIN
    SET @sql = @sql + ', SUM(CASE WHEN Year = ' + CAST(@Year AS CHAR(4)) +
        ' THEN Amount ELSE 0 END) AS [' + CAST(@Year AS CHAR(4)) + ']'
    SET @Year = @Year + 1
END --WHILE

SET @sql = @sql + 'FROM tableName GROUP BY Company'

PRINT @sql  --for testing only
EXEC(@sql)  --create the view
SELECT * FROM viewName  --for testing only
0
 
Scott PletcherSenior DBACommented:
Oops, left out dropping view if it already exists.  Add this line just before the [other] EXEC():

EXEC('IF OBJECT_ID(''viewname'') IS NOT NULL DROP VIEW viewname')
0
 
Scott PletcherSenior DBACommented:
Then run this line:

insert into tablename values('C1'     ,     2004     ,     55)

and run the code above again ... you should see another column in the output for the added year of "2004" :-) .
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 4
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now