A JOIN and a transpose? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

A JOIN and a transpose?

Author

Topic

Wilco
Starting Member

9 Posts

Posted - 2007-10-09 : 17:25:32

So let me see if I can give a simple example. Say I have 2 tables that look like this:

"branches" table:


branch_id   | name
-----------------------------------
1           | Downtown Library
2           | North End Library
3           | Eastern Library

"stats" table


stat_id   | branch_id   | year  | count
-----------------------------------------
1         | 1           | 2001  | 1200
2         | 1           | 2002  | 2100
3         | 1           | 2003  | 1348
4         | 2           | 2001  | 1800
5         | 2           | 2002  | 1700
6         | 2           | 2003  | 900
7         | 3           | 2001  | 600
8         | 3           | 2002  | 800
9         | 3           | 2003  | 1100

How would I construct a query that would return a result set that looked like this:


branch_id   | name              | 2001  | 2002  | 2003
--------------------------------------------------------
1           | Downtown Library  | 1200  | 2100  | 1348
2           | North End Library | 1800  | 1700  | 900
3           | Eastern Library   | 600   | 800   | 1100

I want the result set to be somewhat dynamic (so depending on the years within a given table, the correct number of columns would be used) - so I can't simply have a static number of "year" columns.

I'm thinking I should somehow use a subquery to transpose the data from the "stats" table and then do a join on the "branches" table. Does anyone have any advice on how to construct such a query?

Wilco
Starting Member

9 Posts

Posted - 2007-10-09 : 17:56:56

After doing some research, it seems the correct term for what I want to accomplish is a "pivot." So I basically need to perform a pivot on the "stats" table and then join that result with the "branches" table.

cognos79
Posting Yak Master

241 Posts

Posted - 2007-10-09 : 20:53:09

try this wilco...you have to change the following query as per your naming conventions

declare @branches table
(
branch_id int
,nam varchar(25)
)

declare @stats table
(
stat_id int
,branch_id int
,yea int
,coun int
)

create table #stat_branches
(
branch_id int
,nam varchar(25)
,yea int
,coun int
)

declare @sortedyears table
(
yea int
)

insert into @branches
select 1, 'downtown' union all
select 2, 'north end' union all
select 3, 'eastern'

insert into @stats
select 1,1,2001,1200 union all
select 2,1,2002,1300 union all
select 3,2,2001,1200 union all
select 4,2,2002,1300 union all
select 5,3,2003,1500

declare @years varchar(300)

set @years = ''

insert into @sortedyears
select distinct s.yea
from @stats s
order by s.yea

-- concatenates the years e.g [2001],[20002],[20003]'
Select @years = @years + '[' + convert(varchar(20),a.yea )+ '],' from (Select a.yea from @sortedyears a) a
SET @years = LEFT(@years, LEN(@years) - 1)

insert into #stat_branches
select b.branch_id,b.nam, s.yea,s.coun
from @stats s
join @branches b
on s.branch_id = b.branch_id

exec('SELECT pvt.*
FROM #stat_branches
PIVOT(
sum(coun)
for yea in (' + @years + ')
)AS pvt')

drop table #stat_branches

Wilco
Starting Member

9 Posts

Posted - 2007-10-10 : 12:21:20

Thanks for the help - I managed to set up a stored proc that would accomplish this. For future reference, I'll post it below:


BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @years varchar(2000)
    SET @years = ''
    SELECT @years = @years + '[' + CONVERT(varchar(12), Year, 101) + '], '
	FROM(SELECT DISTINCT year FROM database_stats) stats
        ORDER BY year
	SET @years = LEFT(@years, LEN(@years) - 1) 
    EXEC ('SELECT y.branch, y.notes, z.* FROM database_statbranches y LEFT JOIN (SELECT statbranch_id, '
               + @years + ' FROM (SELECT statbranch_id, year, count FROM database_stats) AS SourceTable PIVOT(max(count) FOR year IN ('
               + @years + ')) AS X) z ON z.statbranch_id = y.statbranch_id')
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources