?
Solved

CrossTab Pivot View!

Posted on 2006-05-24
16
Medium Priority
?
1,491 Views
Last Modified: 2008-03-27
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
Comment
Question by:Thandava Vallepalli
  • 4
  • 4
  • 4
  • +1
16 Comments
 
LVL 12

Expert Comment

by:ill
ID: 16749162
-- 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
 
LVL 12

Expert Comment

by:ill
ID: 16749165
-- 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
 
LVL 5

Expert Comment

by:ewilde
ID: 16749332
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 5

Expert Comment

by:ewilde
ID: 16749415
sorry, mistake: instead of tt1.recid i meant tt1.COMPANY...
0
 
LVL 14

Author Comment

by:Thandava Vallepalli
ID: 16749519
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
 
LVL 14

Author Comment

by:Thandava Vallepalli
ID: 16749524
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
 
LVL 12

Expert Comment

by:ill
ID: 16749540
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
 
LVL 14

Author Comment

by:Thandava Vallepalli
ID: 16749701
Hello ill,

can't we use dynamic sql in views?

itsvtk
0
 
LVL 14

Author Comment

by:Thandava Vallepalli
ID: 16749707
can't we use dynamic sql either in view or user defined function?
0
 
LVL 12

Expert Comment

by:ill
ID: 16749812
it is not possible to use dynamic sql in views or functions. possibility to pivot tables without dynamic sql starts by mssql 2005.
0
 
LVL 5

Expert Comment

by:ewilde
ID: 16749919
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
 
LVL 5

Expert Comment

by:ewilde
ID: 16749936
sorry, i don't test my examples (lake of time...)
just noticed :
do
... Company=@Company ...
instead of
... @Company=@Company ...

0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16755728
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
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 16755801
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16755816
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
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16755825
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question