Pivot Table - 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)

Pivot Table

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2012-10-29 : 06:04:33

Hi,

I have the following table:

AccountNumber, YYYYMM, Value, RowNumber
123567, 201105, 0, 1
123567, 201106, 1, 2

How would I go about pivoting this table?

So I have :

AccountNumber, RowNumber1, RowNumber2
123567, 0, 1

So I basicall want the Account number going down, and Row number going across and the value being in the body?

Thanks

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-29 : 06:33:30

declare @tab TABLE(AccountNumber int, YYYYMM varchar(10), Value int , RowNumber int)
INSERT INTO @tab
SELECT 123567, '201105', 0, 1 union all
SELECT 123567, 201106, 1, 2

select AccountNumber, [1] rownumber1 , [2] rownumber1
from (select accountnumber, value, RowNumber from @tab) as p
PIVOT
(max(value) FOR RowNumber IN ([1],[2])) as pvt

--
Chandu

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2012-10-29 : 06:38:06

hi,

thanks for this, how would I go about making this dynamic? so the row number can be X?

quote:
Originally posted by bandi

declare @tab TABLE(AccountNumber int, YYYYMM varchar(10), Value int , RowNumber int)
INSERT INTO @tab
SELECT 123567, '201105', 0, 1 union all
SELECT 123567, 201106, 1, 2

select AccountNumber, [1] rownumber1 , [2] rownumber1
from (select accountnumber, value, RowNumber from @tab) as p
PIVOT
(max(value) FOR RowNumber IN ([1],[2])) as pvt

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-29 : 07:48:27

check this link for dynamic pivoting
http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

--
Chandu

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-10-29 : 08:31:33

Here tab is assumed as Table Name


CREATE TABLE tab(AccountNumber int, YYYYMM varchar(10), Value int , RowNumber int)
INSERT INTO tab 
SELECT 123567, '201105', 0, 1 union all
SELECT 123567, 201106, 1, 2

DECLARE @cols varchar(1000) ='', @query varchar(max) = ''
SELECT  @cols = STUFF(( SELECT DISTINCT '],[' + cast([rownumber] as varchar(5)) FROM tab
						 ORDER BY '],[' + cast([rownumber] as varchar(5)) FOR XML PATH('')), 1, 2, '') + ']';
--PRINT @cols
SET @query = N'SELECT AccountNumber,'+ @cols +	' FROM 	(SELECT accountnumber, value, RowNumber FROM tab) p
				PIVOT
				(max(value) FOR [rownumber] IN 	( '+@cols +' )) AS pvt;';
--PRINT @query
EXEC (@query)

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources