Transposing rows into columns - 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)

Transposing rows into columns

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-03-02 : 08:52:09

vijay writes "Hello,

I have a data table contains cust info and want to crate columns out of rows.

eg:

acct# trndt duedt
1234 20061027 20061001
1234 20061112 20061101
1234 20061223 20061201
1235 20061005 20061001
1235 20061130 20061101
1235 20061229 20061201

now i need to work out something like

acct# trndt3 trandt2 trandt1 duedt3 duedt2 duedt1
1234 20061027 20061112 20061223 20061001 20061101 20061201"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-02 : 09:47:56

[code]
declare @table table
(
acct# int,
trndt datetime,
duedt datetime
)
insert into @table
select 1234, '20061027', '20061001' union all
select 1234, '20061112', '20061101' union all
select 1234, '20061223', '20061201' union all
select 1235, '20061005', '20061001' union all
select 1235, '20061130', '20061101' union all
select 1235, '20061229', '20061201'

select t3.acct#,
trndt3 = t3.trndt, trndt2 = min(t2.trndt), trndt1 = min(t1.trndt),
duedt3 = t3.duedt, duedt2 = min(t2.duedt), duedt1 = min(t1.duedt)
from @table t3
inner join @table t2 on t3.acct# = t2.acct# and t3.trndt < t2.trndt
inner join @table t1 on t3.acct# = t1.acct# and t3.trndt < t1.trndt and t2.trndt < t1.trndt
group by t3.acct#, t3.trndt, t3.duedt
/*
acct# trndt3 trndt2 trndt1 duedt3 duedt2 duedt1
----------- ----------- ----------- ----------- ---------- ----------- -----------
1234 2006-10-27 2006-11-12 2006-12-23 2006-10-01 2006-11-01 2006-12-01
1235 2006-10-05 2006-11-30 2006-12-29 2006-10-01 2006-11-01 2006-12-01
*/
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-02 : 10:26:36

Or with ROW_NUMBER() function available in SQL Server 2005

SELECT		Acct,
		MAX(CASE WHEN RecID = 3 THEN TrnDt END) AS TrnDT3,
		MAX(CASE WHEN RecID = 2 THEN TrnDt END) AS TrnDT2,
		MAX(CASE WHEN RecID = 1 THEN TrnDt END) AS TrnDT1,
		MAX(CASE WHEN RecID = 3 THEN DueDt END) AS DueDT3,
		MAX(CASE WHEN RecID = 2 THEN DueDt END) AS DueDT2,
		MAX(CASE WHEN RecID = 1 THEN DueDt END) AS DueDT1
FROM		(
			SELECT		Acct,
					TrnDT,
					DueDT,
					ROW_NUMBER() OVER(PARTITION BY Acct ORDER BY TrnDT DESC) AS RecID
			FROM		@Table
		) AS x
GROUP BY	Acct
ORDER BY	Acct

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-02 : 10:29:29

Just one table scan instead of three!

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-02 : 10:41:56

The new PIVOT operator in SQL Server 2005 did not do much better job.

SELECT		Acct#,
		[3] AS TrnDT3,
		[2] AS TrnDT2,
		[1] AS TrnDT1,
		[-3] AS DueDT3,
		[-2] AS DueDT2,
		[-1] AS DueDT1
FROM		(
			SELECT		Acct#,
					TrnDT AS theDate,
					ROW_NUMBER() OVER (PARTITION BY Acct# ORDER BY TrnDT DESC) AS RecID
			FROM		@Table
			UNION ALL
			SELECT		Acct#,
					DueDT,
					-ROW_NUMBER() OVER (PARTITION BY Acct# ORDER BY DueDT DESC)
			FROM		@Table
		) AS s
PIVOT		(
			MAX	(theDate)
			FOR	RecID IN ([3], [2], [1], [-3], [-2], [-1])
		) AS p
ORDER BY	Acct#

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-02 : 10:42:26

In fact, it had the worse execution plan of the three suggestions above!

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources