Select help - 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 2008 Forums

Transact-SQL (2008)

Select help

Author

Topic

Togaspoon
Starting Member

42 Posts

Posted - 2009-09-11 : 11:15:04

DECLARE @results table (EntryId int,EntryName varchar(50),NflWeek int, Pick varchar(1),Team varchar(50))

INSERT INTO @results values(1,'WS 1',1,'A','Baltimore')
INSERT INTO @results values(1,'WS 1',2,'A','Atlanta')
INSERT INTO @results values(1,'WS 1',2,'B','Carolina')
INSERT INTO @results values(2,'TP 1',1,'A','Minnesota')
INSERT INTO @results values(2,'TP 1',1,'A','Seattle')

SELECT *
FROM @results

What's the best way to get this:


Id     Name     Week 1A    Week 2A    Week 2B
1      WS       Baltimore  Atlanta    Carolina
2      TP       Minnesota  Seattle    No Pick

Andreas
Starting Member

11 Posts

Posted - 2009-09-20 : 12:20:18

In you example you state that Seattle should be in Week 2A, but I assume you meant 1A?

In any way, you can accomplish it using the pivot operator:

with Results as
(
   select
      EntryId Id,
      substring(EntryName, 1, 2) Name,
      'Week ' + cast(NflWeek as varchar(2)) + Pick [Week],
      Team
   from @results
)
select
   p.Id,
   p.Name,
   isnull(p.[Week 1A], 'No Pick') [Week 1A],
   isnull(p.[Week 2A], 'No Pick') [Week 2A],
   isnull(p.[Week 2B], 'No Pick') [Week 2B]
from
   (
      select
         row_number() over(partition by [Week] order by Id) RowId,
         Id,
         Name,
         [Week],
         Team
      from Results
   ) t
   pivot
   (
      min(Team)
      for [Week] in ([Week 1A], [Week 2A], [Week 2B])
   ) p

The downside of pivot is that you have to know the values of the columns NflWeek and Pick beforehand, however you can overcome that using dynamic SQL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources