Change rows into column and vice versa without using pivot method.

Question

Post reply

Change rows into column and vice versa without using pivot method.

anandvish2006

Valued Member

Points: 56
More actions
February 25, 2011 at 6:15 am

#235737

Change rows into column and vice versa without using pivot method.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 1

anandvish2006 (2/25/2011)
Change rows into column and vice versa without using pivot method.

Which version of SQL?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 2

Please see the following article as a starter... and, yes, we still need to know the version of SQL you are using...

http://www.sqlservercentral.com/articles/T-SQL/63681/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vedran Kesegic SSCrazy Eights Points: 9302 More actions · Answer 3

I'll show you simple example.

First, prepare test data:

SELECT name = 'age', value = 20

into #fact

union all

SELECT 'age', 30

union all

SELECT 'age', 40

union all

SELECT 'height', 180

union all

SELECT 'height', 190

select * from #fact

WITH PIVOT:

select p.age, p.height

from #fact f

PIVOT

( avg(f.value)

for f.name in ( age, height )

) p

ageheight

30185

WITHOUT PIVOT:

select age = avg( case when f.name = 'age' then f.value end ),

height = avg( case when f.name = 'height' then f.value end )

from #fact f

ageheight

30185

_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths

Paul White SSC Guru Points: 150468 More actions · Answer 4

anandvish2006 (2/25/2011)
Change rows into column and vice versa without using pivot method.

If the names of the columns need to be dynamic, you can use code like this:

SET NOCOUNT ON;

SET STATISTICS XML OFF

;

CREATE TABLE

#Sample

(

company VARCHAR(10) NOT NULL,

period INTEGER NOT NULL,

sales_value MONEY NOT NULL

)

;

INSERT #Sample (company, period, sales_value)

VALUES ('A', 200901, $25000),

('A', 200902, $12000),

('A', 200902, $22000),

('A', 200903, $18000),

('A', 200904, $19000),

('A', 200904, $11000),

('A', 200904, $12000),

('A', 200905, $23000),

('A', 200906, $32000),

('B', 200901, $11000),

('B', 200902, $15000),

('B', 200903, $19000),

('B', 200903, $11000),

('B', 200904, $12000),

('B', 200905, $21000),

('B', 200905, $17000),

('B', 200905, $13000),

('B', 200906, $14000)

;

DECLARE Periods

CURSOR LOCAL

FORWARD_ONLY

FAST_FORWARD

READ_ONLY

FOR SELECT DISTINCT S.period

FROM #Sample AS S

;

DECLARE @period INTEGER,

@pstring CHAR(6),

@sql VARCHAR(MAX)

;

OPEN periods

;

FETCH NEXT

FROM Periods

INTO @period

;

SET @sql = SPACE(0);

WHILE @@FETCH_STATUS = 0

BEGIN

SET @pstring = CONVERT(CHAR(6), @period);

SET @sql +=

', SUM(CASE WHEN period = ' + @pstring +

' THEN sales_value END) AS ' + QUOTENAME(@period)

;

FETCH NEXT

FROM Periods

INTO @period

;

END

;

CLOSE Periods; DEALLOCATE Periods

;

SET @sql =

'SELECT company ' + @sql +

'FROM #Sample ' +

'GROUP BY company'

;

EXECUTE (@sql)

;

DROP TABLE

#Sample

;

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi